Indirect

[BNT1 via OfficeKB.com]

Hi

Currently using this formula in cell =INDIRECT($A5&"!"&B$4) A5 contains
the sheet number, and B4 contains a value. All works well

What I want to do now is amend to show the value in L9, instead of B4, but
keep getting REF# error. Incidentally, L9 does contain a sum formula

All help appricaiated

regards

 

[Bob Phillips]

Doesn't B4 need to contain a cell reference, whereas a sum formula returns a
number?

 

[Eduardo]

Hi,
I think the problem is that L9 is empty or 0 try

=if(or(L9="",L9=),0,INDIRECT($A5&"!"&B$4))

 

[David Biddulph]

Are you saying that =INDIRECT($A5&"!"&B$4) works but =INDIRECT($A5&"!"&L$9)
doesn't?
What value does L9 show? What does =ISNUMBER(L9) show?

 

[Bernard Liengme]

Suppose A5 contains the text: Sheet3
And B4 the text: A1
The formula =INDIRECT($A5&"!"&B$4) will return the value from Sheet3 cell
A1; the result will be the same as =Sheet3!A1

If you want the formula to return the value from L9 on the other sheet, then
cell B4 should hold the text: L9

Or have I misread your question?

 

[BNT1 via OfficeKB.com]

Hi Bob

Well, this did get the grey matter working and now ive seen the error of my
ways. I have hiden the rows on the sheet when compiled some years ago, and
mistakenly, looked at the wrong B4 in another sheet

Thanks for the help

regards

Doesn't B4 need to contain a cell reference, whereas a sum formula returns a
number?

Hi

[quoted text clipped - 8 lines]

regards

 

[David Biddulph]

Looking at this again, I realise that if L9 returns a number, you haven't
included the column letter in your formula.
I assume that B4 included a letter and number?
--
David Biddulph

Are you saying that =INDIRECT($A5&"!"&B$4) works but
=INDIRECT($A5&"!"&L$9) doesn't?
What value does L9 show? What does =ISNUMBER(L9) show?