VLookUp Between Multiple Workbooks. Is it Possible?
Hello.
Is it possible to VLookUp Data between multiple workbooks (12 different workbooks to be exact) in one master sheet?
Master sheet.xlsx
-Contains item number
-Purchase Date
-Sell Date
-Purchase Price
-Sell Price
-Profit Price
The 12 workbooks are the 12 months out of the year, which are invoices.
- Contain the data needed in the master sheet
Is this possible? Has it been done?
Thank you.
are the books open or closed - if closed you will need an add-in
what version of excel do you have
have a read here
http://howtovlookupinexcel.com/vlook...-two-workbooks
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
My limited experimentation indicates that it should work if the workbooks are open and you are not trying to access the same data from multiple sources at the same time in the same cell. To get the data from different workbooks, separate formulae would have to be used.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
VLOOKUP works on closed files.Originally Posted by newdoverman
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I have the workbooks closed. Would you like me upload mock-up files of the workbooks?
How would I accomplish this formula? What it be easier to explain if I upload the workbooks here to download? (just mock-up sample workbooks)
yep,mock ups would help
the link I posted showed step by step how to setup the vlookup
Below are the mockup files. I made them as simple as I could and the desired format I would like.
Arrange the files in the following format below in a folder:
Excel Workbooks (folder)
- Master sheet
- invoices (folder)
- Jan, Feb, Mar
I did not mock up the rest of the months (April, May, June, etc) because I assume you can add the monthly invoice workbooks into the formula, the same way you add the Jan, Feb, Mar workbooks for VLook Up. Message me if I should mock up the rest of the months. Highly appreciated for your help.
Last edited by matt323; 12-25-2013 at 02:05 PM.
I just uploaded the mock up files.
I read the link you posted. But, the How To only explains VLookUp Between 2 workbooks. I still cant figure out a way to VLookUp between 1 master workbook and 12 workbooks (12 different Monthly Invoices (Example: Jan, Feb, March etc))
can you explain how they would work as posted by
newdoverman
you are not trying to access the same data from multiple sources at the same time in the same cell. To get the data from different workbooks, separate formulae would have to be used.
etaf- have you figured out a solution? I am still researching through google. No luck yet. Seems, like I have to enter all the information manually, which would be a headache.
not sure what you are actually trying to do -
can you explain in some detail - how you expect the vlookup to work and bring in the data from 12 workbooks into one master sheet ?
however, i will probably be offline now for a few days due to holidays and UK timezone
I am trying to fill in the empty data in the master sheet from the 12 workbooks.
The main goal I would like VLook Up is to find the item number in the 12 workbooks and input the data related to the item number (sell date, sold price, profit)
so the item will only appear on 1 workbook and not on all 12 ?
if so
you could use an IF statement to work through all 12 books
=if( not(iserror( vlookup(book1))), vlookup( book1) , if( not(iserror( vlookup(book2))), vlookup(book2), if(not(iserror( vlookup(book3))), vlookup(book3) , etc
quite messy that way,
i'm sure there is a better way
BUT if the item can appear on more than one workbook - then its not going to work - and how would you bring that into 1 cell ?
sorry, but i will be off line now
every item with have a different id number, example: abc1, abc2, abc3, abc4 etc.
Happy Holidays. I will try to figure this out.
thank you. Hopefully this gets resolved before you get back.
thanks again for your time
All those invoices must have been created from a master list of sales or something like that. It would be best to access the source data for the invoices if at all possible.
Accessing 12 invoices to compile a master list is a real messy way of going about things. The more steps involved, the more chance of something failing in the process. The simplest solution is usually the best.
What do you mean by access the source data?
is item1 the same as abc1
item1 has data in the row
abc1 is just a blank row with abc1 in it
so do we match on the item1 part and pull back the information
regarding source information - how are the 12 sheets generated - somewhere the data must exist and NOT in 12 separate sheets ?
The source data is the data that produced the invoices in the first place.
yes, so the point that i think was being made was instead of the source data producing 12 separate sheets can it produce one master sheet
also , if not
is item1 the same as abc1
item1 has data in the row
abc1 is just a blank row with abc1 in it
so do we match on the item1 part and pull back the information
The function of the master sheet I am trying to accomplish is:
-the sell date, sold, and profit column will gather its information from the 12 different invoices.
-how will the master sheet obtain this data? Well, by looking up the item number (item number is placed below the item name). With that being said, the information is above the item number needed by the master sheet.
I am simply trying to create a basic system where the item number in the master inventory workbook executes VLook up in the 12 monthly invoice workbooks to obtain the information needed to fill in the blanks in the master inventory workbook (sell date, sold and price column).
The 12 monthly invoices (jan,feb,march,april, etc), I have to input the data in the rows individually. Why? Because I have to calculate the fees, sell date, and profit made from the item. This is information that I dont have control of, meaning I dont know when item will sell (so I cant input a date ahead of time), I dont know how much fees per item and profit made (I have to input this information manually).
I hope I explained well. I also re-uploaded the monthly mock up sheets because I forgot to add zeros in the item number. For example,
wrong - abc1 / correct - abc001
The only reason I am using 12 different workbooks instead of putting everything in 1 file and using 12 sheets, is because I dont want to overload that 1 file. Wouldnt the file be too huge. What if I have 1000 items in each monthly invoice. I dont want to overload 1 excel file.
Having the data in one sheet that feeds 12 would be smaller and a lot more efficient.
12000 rows should not be to much of an issue and would make the lookup so much easier
but i'm not sure how to do the lookup now as the code ABC is below the actual item number - if item number would be very easy in one sheet
=vlookup ( d4, range with the the monthly values , 1, false)
try this in the master sheet
with all the files in the same directory
in G4 put
=VLOOKUP(D4,[januaryinvoice.xlsx]Sheet1!$C$4:$G$7,3,FALSE)
in H4 put
=VLOOKUP(D4,[januaryinvoice.xlsx]Sheet1!$C$4:$G$7,5,FALSE)
that shows 1 example
so I can expand to cover 12 different sheets with quite a complex IF statement as I showed
see if that works for january - But if you need the ABC code then not sure how to do that
and again are these item ids all unique ?
Last edited by etaf; 12-25-2013 at 04:10 PM.
Hey. Check this out. What is your opinion on using index and match vs vlookup for my situation?
This is a revised workbook mock up.
I just can't figure out how to search through all sheets using this method.
Attachment 286051
Hey. Check this out. What is your opinion on using index and match vs vlookup for my situation?
This is a revised workbook mock up.
I just can't figure out how to search through all sheets using this method.
Attachment 286053
create a complicated IF statement, using your index match
Note I have only updated the formula for 2 sheets - so we would need to extend to all 12 sheets - but check this is what you want first and that it actually works on the real data first
=IF(NOT(ISERROR(INDEX(Sheet2!$A:$O,MATCH(Sheet1!$E4,Sheet2!$C:$C,0)-1,MATCH(Sheet1!G$3,Sheet2!$A$3:$O$3,0)))),INDEX(Sheet2!$A:$O,MATCH(Sheet1!$E4,Sheet2!$C:$C,0)-1,MATCH(Sheet1!G$3,Sheet2!$A$3:$O$3,0)),IF(NOT(ISERROR(INDEX(Sheet3!$A:$O,MATCH(Sheet1!$E4,Sheet3!$C:$C,0)-1,MATCH(Sheet1!G$3,Sheet3!$A$3:$O$3,0)))),INDEX(Sheet3!$A:$O,MATCH(Sheet1!$E4,Sheet3!$C:$C,0)-1,MATCH(Sheet1!G$3,Sheet3!$A$3:$O$3,0)),"error"))
What is your opinion using a complicated IF statement vs Index Match for my purpose?
Did you add index match to the IF?
I am still trying to study the code.
Thanks
How would I add more sheets do the code?
thank you
yes, i have added your index match
and so in the if statement
it checks to see if the index match for sheet2 produces an error - if not then we know the value should be in sheet 2 - so we then for true use the index match for sheet 2
if it is an error - then we do the next nested IF statement
it checks to see if the index match for sheet3 produces an error - if not then we know the value should be in sheet 3 - so we then for true use the index match for sheet 3
and so that would need to be setup for all 12 sheets
How can I keep adding sheets to the formula? Could you break that down for me so I could better understand? thanks
so the way the IF is setup
IF( Test , TRUE, FALSE )
so the test is
NOT(ISERROR(INDEX(Sheet2!$A:$O,MATCH(Sheet1!$E4,Sheet2!$C:$C,0)-1,MATCH(Sheet1!G$3,Sheet2!$A$3:$O$3,0))))
then the TRUE
is
INDEX(Sheet2!$A:$O,MATCH(Sheet1!$E4,Sheet2!$C:$C,0)-1,MATCH(Sheet1!G$3,Sheet2!$A$3:$O$3,0)),
now the false is a nested IF
so
IF(TEST, TRUE , IF( TEST, TRUE , FALSE))
and that expands
IF(TEST, TRUE, IF( TEST, TRUE, IF( TEST, TRUE , FALSE )))
and so you repeat the test
NOT(ISERROR(INDEX(Sheet2!$A:$O,MATCH(Sheet1!$E4,Sheet2!$C:$C,0)-1,MATCH(Sheet1!G$3,Sheet2!$A$3:$O$3,0))))
but change the sheet to sheet3
and then you do the TRUE
INDEX(Sheet2!$A:$O,MATCH(Sheet1!$E4,Sheet2!$C:$C,0)-1,MATCH(Sheet1!G$3,Sheet2!$A$3:$O$3,0))
and change the sheet to sheet 3
then repeat those two statements for
sheet 4,5,6,7,8,9,10,11,12
and then you will need a last FALSE - which is where I currently have
"error"
or you could change to
" Value not found in Sheets 2-12"
make sure the , are in place and the correct brackets
does that help - if i had more time , i could work it all out and post -
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks