Question description:
This user has given permission to use the problem statement for this
blog.
HOW TO USE VLOOKUP FOR ENTIRE SHEETWORK AND MULTIPLE TABS?
Solved by T. Y. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
31/05/2018 - 01:09
Hi welcome!
Excelchat Expert
31/05/2018 - 01:10
You have a question on Vlookup use on multiple sheets?
User
31/05/2018 - 01:10
Hi
User
31/05/2018 - 01:10
yes
User
31/05/2018 - 01:10
My project is for reporting
Excelchat Expert
31/05/2018 - 01:11
There are no differences in the syntax of the VLOOKUP formula for other sheets.
Excelchat Expert
31/05/2018 - 01:11
We just need to make sure the limitations of VLOOKUP are not getting in the way.
Excelchat Expert
31/05/2018 - 01:11
Do you have a sheet you can upload?
User
31/05/2018 - 01:13
no, because it confidential
Excelchat Expert
31/05/2018 - 01:13
Or an example with dummy data?
User
31/05/2018 - 01:14
i have multiple sheets like this with different data
User
31/05/2018 - 01:17
can you see what i am pasting in excel now
Excelchat Expert
31/05/2018 - 01:17
ok, I can try to add some dummy data
Excelchat Expert
31/05/2018 - 01:17
No, I was pasting
Excelchat Expert
31/05/2018 - 01:17
What sheet?
User
31/05/2018 - 01:17
1
Excelchat Expert
31/05/2018 - 01:17
ok
Excelchat Expert
31/05/2018 - 01:18
and sheet 2
User
31/05/2018 - 01:18
yes
Excelchat Expert
31/05/2018 - 01:18
and 3
User
31/05/2018 - 01:18
yes
Excelchat Expert
31/05/2018 - 01:19
So where is the formula -sheet 4?
Excelchat Expert
31/05/2018 - 01:20
Ok, what information do you need from each sheet?
Excelchat Expert
31/05/2018 - 01:21
For VLOOKUP to work , regardless of sheet count, the KEY field must be on the far left.
Excelchat Expert
31/05/2018 - 01:21
VLOOKUP only goes right--->
Excelchat Expert
31/05/2018 - 01:22
right accross columns
Excelchat Expert
31/05/2018 - 01:22
HLOOKUP goes down rows.
User
31/05/2018 - 01:22
oky
Excelchat Expert
31/05/2018 - 01:23
IN this setup easy to VLOOKUP using ITEM
Excelchat Expert
31/05/2018 - 01:24
Sheet does not matter. A least in EXCEL, not always 100% sure on this view screen.
User
31/05/2018 - 01:25
i want every count that i do for every month transfer to another worksheet relating to the excat item
Excelchat Expert
31/05/2018 - 01:25
every count means?
Excelchat Expert
31/05/2018 - 01:26
Item is column c?
User
31/05/2018 - 01:26
no b
Excelchat Expert
31/05/2018 - 01:27
B is all same value
Excelchat Expert
31/05/2018 - 01:27
you mean count how many = APOLLO?
User
31/05/2018 - 01:27
yes
User
31/05/2018 - 01:28
go to sheet 2
Excelchat Expert
31/05/2018 - 01:28
Yes, see 3 in column B = APOLLO
User
31/05/2018 - 01:28
we have the Apollo in different colour
Excelchat Expert
31/05/2018 - 01:29
Column C
Excelchat Expert
31/05/2018 - 01:29
So just count column b or ??
User
31/05/2018 - 01:30
i want total for all the 3 colorus to be tansfered to another work sheet that tells my boss how many we have sold in a month
User
31/05/2018 - 01:31
which is lighlighted in red
Excelchat Expert
31/05/2018 - 01:31
I think you just need a PIVOT table
Excelchat Expert
31/05/2018 - 01:32
Count or ADD values in a table base on attributes Like APOLLO and COLOR.
User
31/05/2018 - 01:33
how ?
Excelchat Expert
31/05/2018 - 01:34
You mean how with many sheets or just a single PIVOT?
User
31/05/2018 - 01:35
Many sheets please
Excelchat Expert
31/05/2018 - 01:36
Ok, that is way beyond the simple VLOOKUP question posted.
Excelchat Expert
31/05/2018 - 01:37
and can't be done in this viewer-advanced PIVOT functions required.
Excelchat Expert
31/05/2018 - 01:37
I can take these 3 sheets and work in EXCEL off-line and have site support send to you .
Excelchat Expert
31/05/2018 - 01:38
But given this is just sample data , it may be hard for you to replicate.
Excelchat Expert
31/05/2018 - 01:38
Are you familar with PIVOTS?
User
31/05/2018 - 01:38
yes
Excelchat Expert
31/05/2018 - 01:39
Ok, then you get the issue. You want a cross tab report accross mutliple sheets and then PIVOT.
Excelchat Expert
31/05/2018 - 01:39
Other option , I think may work here. is if all sheets are structured the same , consolidate into 1 summary sheet and then PIVOT.
User
31/05/2018 - 01:40
Thank for your help can i speak to you later
Excelchat Expert
31/05/2018 - 01:40
Either way some work to be done. and I would have to do that off-line.
User
31/05/2018 - 01:40
Are you in Australia
Excelchat Expert
31/05/2018 - 01:40
Yes, try the consolidate then PIVOT
Excelchat Expert
31/05/2018 - 01:41
No i am in the US. I think I am allowed to say that.
User
31/05/2018 - 01:41
oh i was about to ask you if youy do coruses for excel
User
31/05/2018 - 01:42
courses ***
Excelchat Expert
31/05/2018 - 01:42
I just support this chat line which is student 1 question formula stuff. You can go to various FREELANCE sites for more advanced and lengthy support.
User
31/05/2018 - 01:44
look i will be back
Excelchat Expert
31/05/2018 - 01:44
You can re-post your question and I am confident anyone on this line can assist you.
Excelchat Expert
31/05/2018 - 01:44
Ok, have a great day there!
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.
