Using named ranges in a formula

[Steve_H]

I have created a number of named ranges as follows:
Worksheet2 Column B:B named January
Worksheet2 Column C:C named February
Worksheet2 Column D named March
etc etc

I have a formula in a seperate worksheet as follows:
=sumif(worksheet2!A:A,A1,B1)

Therefore I would like to type in "March" in "B1" and the formula above
recognises the named range "March". If I then type in "January" in B1 the
formula will recalculate based on the data in named range "January". Can
anyone advise if this is possible and if so how it can be done?

 

[Peo Sjoblom]

Try

=SUMIF(INDIRECT(B1),A1)


Note that the workbook with the named ranges need to be open


--


Regards,


Peo Sjoblom

 

[Pete_UK]

Yes it can be done using INDIRECT. But I'm not sure what you are
trying to do. You will be adding the range B1 if A1 matches the named
range, but how can you add the word "March"?

Pete

 

[Steve_H]

Hi Peo

Thanks for your help. I had to do a slight deviation from your suggestion
but the function INDIRECT, this is what worked.

=SUMIF(worksheet2!A:A,A1,INDIRECT($B$1))

Thanks again.