vlookup 50+ woorkbooks

[Walter3]

Originally posted by Walter3
Hi Alan

I am using a standard worksheet format. One spreedsheet is my materia
use on a job ( item, description, Quantity ) the other is a list of al
items ( item, description, items used ) I can use vlookup to lookup i
one spreedsheet, that works well. I need it to be able to open close
spreedsheets to give me a total item used for a YTD report so i kno
how much items used.

ex
VLOOKUP(A2,'C:\[john.XLS]ESTSHEET'!$A:$D,4,true)+VLOOKUP(A2,'C:\[tony.XLS]ESTSHEET'!$A:$D,4,true)
Etc......

Can [tony.xls] be substituted by a *.xls to eliminate all the + sign
or is there a beter way and how do i do it. I am a beginner.

How can this be done to lookup the data in closed workbooks.
I don't want to open all of them to get a total ( I need to find ou
YYTD report of all items

 

[Tom Ogilvy]

No. You need to refer to each separate workbook. Your formula doesn't open
the workbooks - just retrieves the data from them.

formulas are limited to 1024 characters in length, so you probably need to
do a vlookup on each workbook in a separate cell, then sum the cells.

 

[Tom Ogilvy]

If the data is in the same place in each workbook you might want to look at
the consolidate option in the Data menu.

 

[Walter3]

hi Tom Ogilvy


Thanks for the info and in leading me in the correct path but I am
not sure as how to do that, though i have tried a consolidation in a
macro once, and it seems to work great. I 'll try it and let you Know
how it works. In the meantime if you have any other ways please let me
know. Thank you so much.