Referring To A Cell In A Workbook Name Based On A Cell Value

[DINGO0Z]

I am writing a formula in cell "C1" in "Workbook #1" that will referenc
cell "D76" in a workbook that has not been created yet. The way I wil
tell the formula what the name of the workbook will be is b
referencing cell "A1" and adding the extension .xls to it.

Example:
"Workbook #1"
_A___|B_|C
1_Tax_|__|=c:workbooks\A1.xls, spreadsheet1, cell # D76
(c:workbooks\Tax.xls, spreadsheet1 cell # D76)
2_____|__|__

Can anyone tell me the formula syntax to accomplish this, or is it eve
possible?

Thank you for any replies,
-Da

 

[RagDyer]

This can be done.

However, you'll have to keep this linking formula as a *text* "non-formula"
until the actual WB is created and saved at least once, otherwise it'll
return a #Ref! error.

If you accurately posted your correct path, try this in say D2 of Workbook1:

="='c:\workbooks\["&$A$1&".xls]spreadsheet1'!D76"

Don't be concerned that what you see in the cell, and what you see in the
formula bar, is not identical.

NOW, right click in D2 and choose "Copy",
Then, right click in A2, and choose "Paste Special"
Click on "Values", then <OK>.

You now have a text, non-working formula in A2, waiting for the actual WB
whose name is entered in A1, to be created and saved, which *does* contain a
WS named "spreadsheet1".
Once you've completed creating the WB, it's time to "activate" the formula.
You do this by simply opening and closing TTC.
Click in A2, and from the Menu Bar,
<Data> <Text To Columns> <Finish>

If you have data in D76, of worksheet "spreadsheet1", in workbook (name in
A1), on drive C:, it'll now be displayed in A2.