Can't make this formula work: sum(Indirect.... Please help!

[pedie]

Hi, this could be pretty simple but cant get it to work...
B1 value = Sheet3!

Formulas i tried...

=SUM(Jan:B1&"!"&A2)
=SUM(Jan:INDIRECT($B$1&A3))
=SUM(Jan:INDIRECT($B$1)&A4)

It works when i try it this way...
=SUM(Jan:Sheet3!A4)
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=18 width=64></TD></TR></TBODY></TABLE>
Thanks for helping in advance...

 

[pedie]

#NAME? Error

When I evaluate the formula it show(#Name?:indirect(B1)&"A3"))
3rd step shows.
Sum(#Name?:#Ref!&"A3")
4rd step shows.
sum(#Name?:&"A3")
5rd step shows.
sum(#Name?)

I rechecked the worksheet names and it has Jan and Sheet3 too...


What could be wrong?
Is there a specific way Indirect ways...and not when cancatenated?


Thanks again.

 

[energman58]

SUM(Jan:INDIRECT($B$1&"A3"))

Should work - as you had it it was adding the Value in A3 not the reference to "A3" to your string


Indirect works on a string so your formula must result in a string

 

[pedie]

SUM(Jan:INDIRECT($B$1&"A3"))

Should work - as you had it it was adding the Value in A3 not the reference to "A3" to your string


Indirect works on a string so your formula must result in a string

I tried this way; =SUM(Jan:INDIRECT($B$1&"A3"))..and it still errors #Name?


Thanks again for helping!

 

[pedie]

When i try this way it works fine.'=SUM(Jan:Sheet3!A2)
Evaluate shows =Sum(#Name?:INDIRECT("Sheet3!'&"A3"))
i think the indirect way suggeted works cool but the this part

Jan:

i think is the issue here....


Thanks agian

 

[energman58]

Must be something wrong with the named range "Jan" - check with the name manager to make sure it has not been accidentally removed. You should also check the scope and definition of Jan in the name manager - make sure it is set to Workbook and not Sheet3 or whatever - and that it refers to a cell not a range you may have to delete it and redefine it to make it work.

If Jan is in another workbook you have to fully reference it as an external link.

I just tried it and it works fine for me