Using named ranges in a formula

S

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: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?
 
P

Peo Sjoblom

Try

=SUMIF(INDIRECT(B1),A1)


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


--


Regards,


Peo Sjoblom
 
P

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
 
S

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top