Worksheet name as referenced variable...

[davidlturner]

Example:

=SUM('[MySales.xls]Sales2003'!B1:B500)

Can anyone enlighten me on how I can enter the required worksheet nam
into a cell in the current sheet - in this case Sales2003 - the
somehow reference that cell in a function?

I thought I could utilise the INDIRECT() function, but...

P.S. if i wanted to do the same with the file name, would that b
INDIRECT()?

Any help greatly appreciated

 

[Frank Kabel]

Hi
if the other workbook is OPEN then INDIRECT is the correct
function. e.g. if A1 stores the sheet name use:
=SUM(INDIRECT("'[MySales.xls]" & A1 & "'!B1:B500"))

 

[davidlturner]

Thanks - I suspected it was INDIRECT() but couldn't get the synta
right