Help with building a formula based on cell values

[jfrick100]

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

 

[Tom Ogilvy]

In B2: =Indirect("'[Source Data.xls]" & A2 & "'!$J$" & row()+4)

Source Data.xls must be open for this to work. Indirect does not support
going to a closed workbook in xl2003 and earlier.

 

[Gary''s Student]

Use INDIRECT. Something like:

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

Of course both books must be open

 

[jfrick100]

Use INDIRECT. Something like:

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

Of course both books must be open

Thanks. I tried this and I am almost there. The reason is I forgot
to mention that the value of the worksheet is caluclated off of a data
and not in a cell.

So I have no vlue in B1, but I do calculate the vlue of the worksheet
from a cell.

Is it possible to use a formula instead of a cell?

Thanks for all posts so far.

James