Variable in an external lookup formula 1

[marcr62]

I have an exteranl lookup formula that brings data in a summay spreadsheet from various other spreadsheets. An example formula is: ='[daily Bookings 2003.xls]Wk 8'!$N$19.

My question is,"Is it possible to have the 8 in this formula be a variable that is set on the summary document each week? What I mean is, that this summary sheet is run each week and the next run will be looking for Wk 9, not Wk 8 as above. It does not work, but what I would like to do is: ='[daily Bookings 2003.xls](variable)'!$N$19 where variable is actually =D9 on the summary sheet. Each week I will make D9 equal that weeks number.

 

[Dreamboat]

You need to put an indirect in there...


&INDIRECT("E1&quot Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[marcr62]

Thanks for responding,

Where in the ='[daily Bookings 2003.xls]Wk 8'!$N$19 does the &indriect("E1&quot go.

I replaced the WK 8 with &indirect("E1&quot and a dialog box comes up asking me to pick a sheet name from the daily bookings 2003.xls.

 

[Dreamboat]

And in E1, did you put something like Wk 8?

I would try getting rid of spaces in the sheetnames, for one.

and I THINK (help somebody!), it would be:

='[daily Bookings 2003.xls]indirect("E1&quot'!$N$19

where the contents of E1 contain your worksheet name Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[Gavona]

It's more like:
Indirect("'[daily Bookings 2003.xls]"&E1&"'!$N$19&quot

I would suggest naming cell E1 and then the formula would become:
Indirect("'[daily Bookings 2003.xls]"&myname&"'!$N$19&quot

 

[Dreamboat]

Thanks GAVONA!!! Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[marcr62]

Thanks Everyone,

That worked great.