Indirect reference to another workbook?

[erniepoe]

Can somebody help me out with the formula phrasing with an indirect reference to a different workbook?

The workbook is called "Power," and the worksheet is "template," and I'm trying to link to cellB58 in that particular workbook, but not quite sure on the syntax.

 

[careywalker]

When you say link, do you want to get the value in cell B58 or do you want a hyperlink that when you click on the link it takes you to cell cell B58 in the other file?

 

[erniepoe]

Sorry for the ambiguity. I meant that I'm trying to use the indirect formula to get the value of cell B58 from the "Power" workbook and the "template" worksheet in the current workbook I'm using. Not looking to hyperlink or anything.

Thanks.

 

[danzon]

in cell A1: type "power.xlsx
in cell A2: type "template"


=INDIRECT("'["&A1&"]"&A2&"'!"&"$B$58")

should work as long as power.xlsx workbook is currently open... otherwise you will need to put the full file location reference in A1

 

[erniepoe]

Thanks Danzon,

Rather than having to type the name of the workbook and worksheet in dummy cells, is it possible to incorporate them both into the folder? I tried replacing the A1 and A2 from your formula with them, but it didn't work. Is there a way to do this?

 

[danzon]

I may be misunderstanding what you are after but...

if you just want to code the location directly in the formula then you don't need to use indirect at all

just use this formula in the cell..adjusting for the correct file location reference

='C:\Documents and Settings\user\Desktop\[power.xlsx]template'!$B$58