variable in lookup (Excel 2003)

[carango]

Is there a way to insert a variable into a VLookup string? For instance, I have the following formula in a cell:
=VLOOKUP(A6,'E:COOKROOMDailyInv[020105.xls]Inventory'!$A$3:$G$85,7,0)
I would like to be able to substitute the 020105.xls with a cell reference. I would like the formula to look at cell b6, for instance, to get the document name. Is that possible?

What I am trying to do is to reference existing daily inventory worksheets and be able to put the beginning inventory date in one column and the ending inventory date in the next column to get the difference in inventory between the 2 days. The daily worksheets are named consistently with the date as their name. Any better ideas to accomplish this task?

Thanks in advance for your assistance! Linda

[rory]

Hans,
One small typo - you missed out INDIRECT! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
=VLOOKUP(A6,INDIRECT("["&B6&"]Inventory!$A$3:$G$85"),7,0)

[HansV]

Thanks to Rory for pointing out stupid mistake - I omitted the essential part from the formula, the INDIRECT function. Now corrected.

You can use the INDIRECT function:
<code>
=VLOOKUP(A6,INDIRECT("["&B6&"]Inventory!$A$3:$G$85"),7,0)
</code>
Note: INDIRECT doesn't work if the workbook referred to is closed, so there is no need to include the path. The formula will return #REF if the other workbook is closed, but it will update itself the moment the other workbook is opened. Laurent Longre's free add-in MoreFunc (recommended!) contains a function INDIRECT.EXT that does work if the workbook referred to is closed.

[HansV]

<img src=/S/blush.gif border=0 alt=blush width=15 height=15> Small? <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

Thanks for that! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

[carango]

Worked like a charm. Thanks so much!