Indirect referencing Workbook and Worksheet and Cell - not working

[MixedUpExcel]

Hi,

This should be straight forward but I can't see what I'm missing.

On Sheet 1 in Cell O4 I have the name of a Workbook (not the one I'm in) but it is open.
On Sheet 2 I have the name of the current Sheetname in Cell P1 (which will be identical sheet on the Workbook I have the name of in Cell O4 on Sheet1)

I am currently in a cell on Sheet2 and want to reference the Workbook name from Sheet1 and the Sheetname from Sheet2

I am trying to use Indirect as both the Workbook name and Sheetname can change in the future.

This is the formula I have but I can't see what I'm missing:

=INDIRECT("'["&Sheet1!O4&"]"&P1&"'!"&C9)

I know that it should look like this when it pulls through the data:

='[Workbookname.xlsx]currentsheetname '!$C$9

Please can you someone advise.

Thanks in advance.

Simon

 

[Snakehips]

@MixedUpExcel

Try..... =INDIRECT("'[" & Sheet1!O4 & "]" & P1 &"'!C9")

 

[MixedUpExcel]

@MixedUpExcel

Try..... =INDIRECT("'[" & Sheet1!O4 & "]" & P1 &"'!C9")

Hi Snakehips,

Thanks for your help with this.

It seems to be working now

Simon

 

[MixedUpExcel]

Hi,

I've found this as a possible solution:

[COLOR=#333333][I]=INDIRECT("'[" & Sheet1!O4 & "]" & P1 &"!"&cell("address",C9))[/I][/COLOR]

however, this now references just the cell on Sheet2 (the sheet I'm on)

Any ideas?

Thanks.

 

[tyija1995]

To make C9 relative in that sense instead of &"!C9" replace with &"!"&CELL("address",C9)


I.e. INDIRECT("'[" & Sheet1!O4 & "]" & P1 &"!"&CELL("address",C9))

 

[MixedUpExcel]

Thank you.

It appears the formula I had was correct, however, if I click into the formula it's selecting the cell on the sheet I'm currently in.

This clearly isn't a true reflection of the formula as it's actually pulling the data from that cell reference on my other workbook.

Anyhow, it works.

So thanks again.

Simon