Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
INDEX("a"&COLUMNS(INDIRECT(A1):INDIRECT(A2)):A30,4) | Excel Discussion (Misc queries) | |||
INDIRECT.EXT | Excel Worksheet Functions | |||
INDIRECT.EXT | Excel Worksheet Functions | |||
indirect? | Excel Worksheet Functions |