Indirect
 

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

--
Message posted via http://www.officekb.com

Indirect
 

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

--
__________________________________
HTH

Bob

"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:9a1768a3caeab@uwe...
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

--
Message posted via http://www.officekb.com

Indirect
 

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

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

"BNT1 via OfficeKB.com" wrote:

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

--
Message posted via http://www.officekb.com

Indirect
 

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?
--
David Biddulph

"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:9a1768a3caeab@uwe...
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

--
Message posted via http://www.officekb.com

Indirect
 

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?

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:9a1768a3caeab@uwe...
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

--
Message posted via http://www.officekb.com

Indirect
 

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

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

Hi

[quoted text clipped - 8 lines]

regards

--
Message posted via http://www.officekb.com

Indirect
 

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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?
--
David Biddulph

"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:9a1768a3caeab@uwe...
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

--
Message posted via http://www.officekb.com