Question description:
This user has given permission to use the problem statement for this
blog.
hi there, im making a spread sheet to track progress in a game and would like to create a drop log sheet, i was hoping to use the if function to detect weather a item is logged on a sheet then send the sheet number to a list if so
Solved by K. F. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
25/09/2018 - 06:40
Hi, welcome to Got it Pro-Excel!
User
25/09/2018 - 06:40
hi
Excelchat Expert
25/09/2018 - 06:40
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert
25/09/2018 - 06:41
First, do you have a sample data or will you be able to send me your file?
User
25/09/2018 - 06:41
yes
Excelchat Expert
25/09/2018 - 06:41
Can you send it by using the attachment button in this chatbox?
User
25/09/2018 - 06:41
[Uploaded an Excel file]
Excelchat Expert
25/09/2018 - 06:41
Give me a moment to review your file.
User
25/09/2018 - 06:42
no worries
User
25/09/2018 - 06:42
im quite new to this so it may seem like a messy document to you!
Excelchat Expert
25/09/2018 - 06:43
No worries.
Excelchat Expert
25/09/2018 - 06:43
While I'm reviewing it, can you tell me your high-level goal?
User
25/09/2018 - 06:43
just personal interest if i am honest, working on the document for me and a few friends just to get some data on a game we play
User
25/09/2018 - 06:44
its basically to track in game currency
Excelchat Expert
25/09/2018 - 06:44
This is actually a very decent looking sheet you have here.
User
25/09/2018 - 06:44
thank you! learning as i go along!
Excelchat Expert
25/09/2018 - 06:45
Awesome!
Excelchat Expert
25/09/2018 - 06:45
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
User
25/09/2018 - 06:45
sure!
User
25/09/2018 - 06:45
only having one issue at the moment!
Excelchat Expert
25/09/2018 - 06:45
Great! Can you tell me more about this issue that we need to fix?
User
25/09/2018 - 06:46
if you see the hiden sheets named kc (2:101)
User
25/09/2018 - 06:47
id like to ad a box in them with a number and if a unique item (items on the right highlited in orrange) foes above 0 on amount, have it added to a new sheet in a list to track what sheet the item was listed on
Excelchat Expert
25/09/2018 - 06:47
Wait, let me look at it, there's a lot of hidden sheets in here.
User
25/09/2018 - 06:47
esentially creating a drop log of the item and the kc sheet it is allocated too
Excelchat Expert
25/09/2018 - 06:48
Can you give me the exact sheet name?
User
25/09/2018 - 06:48
so im only working on kc and main spreadsheet at the moment, the rest are copys of kc
User
25/09/2018 - 06:48
allowing for the formulas on the main spreadsheet to work
User
25/09/2018 - 06:50
the main spreadsheet is essentially a total of all the sheets when filled in and kc is the sheet you would fill in per game played
Excelchat Expert
25/09/2018 - 06:50
Can you tell me more about the box that you want to add in KC sheets?
User
25/09/2018 - 06:50
if you look on sheet kc
User
25/09/2018 - 06:50
under overall time there is a box with kill count written in it
User
25/09/2018 - 06:51
i would like the number in that box to be pulled to another sheet IF the amount of the uniques above it go to 0<
Excelchat Expert
25/09/2018 - 06:51
In KC Sheets there's only 'Raid Time' in it.
User
25/09/2018 - 06:52
ahh i believe you might have an old copy
Excelchat Expert
25/09/2018 - 06:52
Oh ok, will you be able to send me the current version?
User
25/09/2018 - 06:52
my appologies
[Uploaded an Excel file]
Excelchat Expert
25/09/2018 - 06:52
No worries.
User
25/09/2018 - 06:54
so below that raid time you saw it should have kill count
Excelchat Expert
25/09/2018 - 06:54
Yes, I can see it.
User
25/09/2018 - 06:54
that would be filled in with say "100"
User
25/09/2018 - 06:55
id like that "Kill Count: 100" to be pulled from that sheet to a drop log IF the 'Amount' in !P4:P10 is >0
Excelchat Expert
25/09/2018 - 06:56
And you want this to be applied for all the KC sheets?
User
25/09/2018 - 06:56
but i need it to be something like 'Kc:Kc(101)'!P4:P10>0
Excelchat Expert
25/09/2018 - 06:56
Kindly extend our session when it hits below 3-minute mark, don't worry it's free.
User
25/09/2018 - 06:57
all done!
Excelchat Expert
25/09/2018 - 06:57
Thanks!
Excelchat Expert
25/09/2018 - 06:58
How do you want the data to be transferred to your Drop Log sheet?
User
25/09/2018 - 06:59
something along the lines of for instance IF(!P4>0 show O4 + SHOW P14
User
25/09/2018 - 06:59
if that makes sense
User
25/09/2018 - 07:00
so it would list the item name and the killcount number in a list
Excelchat Expert
25/09/2018 - 07:00
And you want this in separate rows for each KC Sheet?
User
25/09/2018 - 07:01
im aware the images will be alot harder to move over
User
25/09/2018 - 07:01
and yes for instance i would like it to auto fill in a list but only if the sheet listed a unique item
User
25/09/2018 - 07:01
can you see the excel document onm the web page if i type in it?
Excelchat Expert
25/09/2018 - 07:02
Do you mean this worksheet on our platform?
User
25/09/2018 - 07:02
yes
Excelchat Expert
25/09/2018 - 07:02
Yes, that's real-time.
User
25/09/2018 - 07:03
so id like the data to be pulled over and fill in a list like that if possible
Excelchat Expert
25/09/2018 - 07:04
Ok, thanks for the visual. Another question for you - How do you use these KC Sheets? Do you create a copy first then use the new copied sheet?
User
25/09/2018 - 07:05
so you would fill in Kc(2) then hide it and unhide Kc(3) ready to fill in and so on..
User
25/09/2018 - 07:05
thats the only way i could make the formula work for me in the total mainsheet
Excelchat Expert
25/09/2018 - 07:06
Oh ok, Yes that's logical. What if you get two unique items?
User
25/09/2018 - 07:06
you cant per raid
User
25/09/2018 - 07:06
so there should only ever be 1 on any copy of the sheet
Excelchat Expert
25/09/2018 - 07:06
1 row per sheet, is that correct?
Excelchat Expert
25/09/2018 - 07:07
And if you didn't acquire any unique item in that raid for that KC, it should be blank?
User
25/09/2018 - 07:07
yes so id rather it be hiden if possible
Excelchat Expert
25/09/2018 - 07:07
Yes, we can do that.
Excelchat Expert
25/09/2018 - 07:08
Based on what you’ve shared, you need the Drop Log filled like you showed in our shared sheet for every KC sheet if a unique item is acquired, do you believe that will address your problem?
User
25/09/2018 - 07:09
yes i belive so!
User
25/09/2018 - 07:09
i was having issues getting my head around vlookup and the if function
Excelchat Expert
25/09/2018 - 07:09
I’m going to create a formula for you. This should take me a couple of minutes to finish.
Excelchat Expert
25/09/2018 - 07:10
No worries, I'll explain the solution provided after you confirmed that it's doing exactly what it needs, is that fine with you?
User
25/09/2018 - 07:10
yes sure!
Excelchat Expert
25/09/2018 - 07:10
Great, thanks. Give me a moment to solve this for you.
User
25/09/2018 - 07:10
thanks alot i appreciate it!
Excelchat Expert
25/09/2018 - 07:10
My pleasure.
Excelchat Expert
25/09/2018 - 07:13
You can extend the session for another 20 minutes once our timer hits below 3-minute mark.
Excelchat Expert
25/09/2018 - 07:13
Again, it will be free but that will be our last extension.
User
25/09/2018 - 07:13
im on it!
Excelchat Expert
25/09/2018 - 07:14
Thanks.
Excelchat Expert
25/09/2018 - 07:17
There we go.
Excelchat Expert
25/09/2018 - 07:18
I'm starting with Kc (2) since Kc (1) is already populated and it's showing that you acquired multiple unique items.
User
25/09/2018 - 07:19
yes thats fine, that was just from me messing around testing the formulas
Excelchat Expert
25/09/2018 - 07:19
Ok, and I also noticed that other Kc sheets doesn't have Raid Time and Kill count in it. I'll try my best to do it for all sheets within our time limit.
Excelchat Expert
25/09/2018 - 07:20
I was able to do the formula but I need to make sure that all your Kc sheets are aligned.
User
25/09/2018 - 07:20
they are all copies so that shouldnt be an issue i just havent added the raid times or kill counts yet
Excelchat Expert
25/09/2018 - 07:21
Oh ok, I'll modify the formula first and send it to you for testing.
User
25/09/2018 - 07:21
thanks!
Excelchat Expert
25/09/2018 - 07:29
Almost done.
User
25/09/2018 - 07:29
sweet!
Excelchat Expert
25/09/2018 - 07:32
Here's your file.
[Uploaded an Excel file]
Excelchat Expert
25/09/2018 - 07:32
If you'll see Kc (2) and Kc (6) are visible and I used it as test sheets.
User
25/09/2018 - 07:33
That looks perfect!
Excelchat Expert
25/09/2018 - 07:33
On your Drop Log sheet - Columns A to E will be your unique result.
Excelchat Expert
25/09/2018 - 07:33
Excellent!
User
25/09/2018 - 07:33
is there a way to have the left hand side on a seperate sheet?
Excelchat Expert
25/09/2018 - 07:33
Column H to M will be your actual process.
Excelchat Expert
25/09/2018 - 07:34
Sure, but please keep in mind that we only have 6 minutes left. I might not be able to finish that in time.
Excelchat Expert
25/09/2018 - 07:34
But let's try.
Excelchat Expert
25/09/2018 - 07:34
Can you test it first and work on a a different Kc sheet to verify if it works?
User
25/09/2018 - 07:34
if not can you give me any guidence where i would find the process myself?
Excelchat Expert
25/09/2018 - 07:34
Ok let me explain the process I did.
Excelchat Expert
25/09/2018 - 07:35
In columns H to M of Drop Log Sheet.
Excelchat Expert
25/09/2018 - 07:35
This is the actual process that looks up from each Kc Sheet.
Excelchat Expert
25/09/2018 - 07:35
Basically if you have a new Kc sheet. Just copy the formulas in Column H to M and extend it to the rows below.
User
25/09/2018 - 07:35
i can confirm its working
Excelchat Expert
25/09/2018 - 07:36
So right now it's only up to row 101
Excelchat Expert
25/09/2018 - 07:36
and is serving Kc (102)
Excelchat Expert
25/09/2018 - 07:36
If you have a new Kc sheet such as Kc (103)
User
25/09/2018 - 07:36
yes if it goes further extend the formula with the extra sheets
Excelchat Expert
25/09/2018 - 07:36
Just copy the formula from H101:L101 below.
Excelchat Expert
25/09/2018 - 07:37
The most important part here is the Column L, which is the unique item name.
Excelchat Expert
25/09/2018 - 07:37
If that is blank, meaning there is no "1" in your column P of that specific Kc sheet. it will not show up in columns A to E.
Excelchat Expert
25/09/2018 - 07:38
To see what I meant, try replacing 1 with 0 on your Kc (6) cell P4 or your amount of Avernic defender hilt.
Excelchat Expert
25/09/2018 - 07:38
And you'll see that it removes that row in Columns A to E of Drop Log sheet.
Excelchat Expert
25/09/2018 - 07:39
Even though it had Kill count as per your requirement.
User
25/09/2018 - 07:39
yes that works perfectly!
Excelchat Expert
25/09/2018 - 07:39
Awesome!
Excelchat Expert
25/09/2018 - 07:39
I'm very happy to help you!
Excelchat Expert
25/09/2018 - 07:39
Would there be anything else I can assist you with regards to the original question and the solution provided? We have 20 seconds left.
User
25/09/2018 - 07:40
excellent job!
Excelchat Expert
25/09/2018 - 07:40
If that's all, I'd like to wish you have a very nice day ahead of you and we'd love to hear from you again. Feel free to leave a rating and comment at the end of this session. Thank you for using Got it Pro-Excel! Have fun hunting!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
