Use INDIRECT. Something like:

=INDIRECT("[Book2]"& B1 & "!$B$2",TRUE)
where B1 contains:
Sheet1

Of course both books must be open
--
Gary''s Student - gsnu2007


" wrote:

I need help building a formula.

I have a cell whose formula is

='[Source Data.xls]200709'!$J$6

This, of course, means go to the "Source Data.xls" file, "200709"
worksheet, and copy cell "$J$6". This works well.

I have a need to find a way to to substitute the value in a cell for
the "200709".

for example, suppose me spread sheet looked like this:

A B
1 Date sales
2 200708 x
3 200709 y
4 200710 z

Then the value for the sales cells would be

='[Source Data.xls]200708'!$J$6
='[Source Data.xls]200709'!$J$7
='[Source Data.xls]200710'!$J$8

However, I have literally hundreds of these cells to do.

So I would like to do something like this

='[Source Data.xls]"Reference A2"'!$J$6
='[Source Data.xls]"Reference A3"'!$J$7
='[Source Data.xls]"Reference A4"'!$J$8

where the "Reference A2" would cause the command to be evalusted with
the value in cell A2.

Is this possible?

Thanks,

James