Indirect() won't work with a formula entered as Text?

[prabha_friend]

In J14:
='[Alan Beta Strat Bond and GBO 25th February.xlsx]MULTISECTOR Front Sheet'!$B$2 (Formatted as Text)
In A2:
=INDIRECT(FilePath)
Note:
Range("J14") Named as Filepath

Why am I getting #REF!

This is not the right way?

 

[pgc01]

Hi

2 remarks

1 - When you say formatted as Text you mean it displays the string "='[Alan..."? in that case, Indirect() cannot interpret the "="

2 - remember that Indirect() only works with open workbooks.

 

[prabha_friend]

Hi pg,
I removed the "=" from the formula. Now my formula is just the following:
'[Alan Beta Strat Bond and GBO 25th February.xlsx]MULTISECTOR Front Sheet'!$B$2

But still I am getting an #REF!

One more thing the file is opened but still. How to make it work?

 

[Jonmo1]

A Leading ' is a special character that signifies the following characters should be treated as text.
And it is usually ignored by formulas
So the Indirect formula is actually seeing the cell's contents as
[Alan Beta Strat Bond and GBO 25th February.xlsx]MULTISECTOR Front Sheet'!$B$2
Without that leading '

Try doubling that leading ' so the cell's contents looks like
''[Alan Beta Strat Bond and GBO 25th February.xlsx]MULTISECTOR Front Sheet'!$B$2

That's 2 apostraphe's, not a regular quote mark.

 

[BGY23]

Hi,
Open the alan beta etc workbook.
In the cell where you want to reference the alan beta workbook (j14) type =Cell("address", now click on the cell you want in the Alan beta workbook) and press enter.
Now A2 =indirect(j14 or Filepath,1) whichever you prefer. Don't forget the ,1 at the end of the indirect formula.

 

[BGY23]

You should note it still doen't work without the Alan beta workbook being open.

 

[prabha_friend]

Thank you very much Jonmo. It's working great. Don't take me annoying. I have one more question over that:

Can we use an hybrid reference? (Some parts of the formula Indirect: (say referring an opened file name) and some other parts Direct: (say sheetname and range))

How to do like this?

A1: (as Text) with two apostraphes's

''[Alan Beta Strat Bond and GBO 25th February.xlsx]"

A2: (As formula)
=Indirect(A1&"MULTISECTOR Front Sheet'!$B$2")

Otherwise I have to duplicate many Indirect cells for every variation of Sheetname and Range.

Thanks in advance

 

[Jonmo1]

Yes, but A1 should only contain the 'leading' apostrophes, there should be none at the end, because that comes after the sheet name which you've put into the formula.
So A1 should be
''[Alan Beta Strat Bond and GBO 25th February.xlsx]

 

[prabha_friend]

@Jonmo: You rock! Thank you

 

[Jonmo1]

You're welcome.