Named Range Reference

Tropez

New Member
Joined
Apr 19, 2004
Messages
35
Greetings all, first off thank you to all the GREAT advice everyone adds to this Message Board as it has definately helped me out!

Now for my question, is there a way to reference a named range from another cell?

I have one sheet that has survey results and the corresponding question number as a named range (Q_1, Q_2, Q_3, and so on).

I have a second sheet that summarizes the results of from that table so they are easy to read, and I am referencing those named ranges in this formula:

Code:
=SUMPRODUCT((Q_2)*(Sales_ID=G1))

Cell A1 of the summary sheet has the number of a question that could also be used as the named range (Q_2). Is it possible to do something similar to this?

Code:
=SUMPRODUCT((CONCATENATE("Q_",A1))*(Sales_ID=G1))

Doing this allows the viewer to change the question reference to be any of the questions on the fly.

Thanks in advance!
Ken[/b]
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Tropez said:
Greetings all, first off thank you to all the GREAT advice everyone adds to this Message Board as it has definately helped me out!

Now for my question, is there a way to reference a named range from another cell?

I have one sheet that has survey results and the corresponding question number as a named range (Q_1, Q_2, Q_3, and so on).

I have a second sheet that summarizes the results of from that table so they are easy to read, and I am referencing those named ranges in this formula:

Code:
=SUMPRODUCT((Q_2)*(Sales_ID=G1))

Cell A1 of the summary sheet has the number of a question that could also be used as the named range (Q_2). Is it possible to do something similar to this?

Code:
=SUMPRODUCT((CONCATENATE("Q_",A1))*(Sales_ID=G1))

Doing this allows the viewer to change the question reference to be any of the questions on the fly.

Thanks in advance!
Ken[/b]

Replace

=SUMPRODUCT((Q_2)*(Sales_ID=G1))

with:

=SUMIF(Sales_ID,G1,Q_2)

And try:

=SUMIF(Sales_ID,G1,INDIRECT("Q_"&A1))
 

Tropez

New Member
Joined
Apr 19, 2004
Messages
35
That would work fine, except I just realized that I have a second formula that looks for those items >=4.

Code:
=SUMPRODUCT((Q_2>=4)*(Sales_ID=G5))

Thanks!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Tropez said:
That would work fine, except I just realized that I have a second formula that looks for those items >=4.

Code:
=SUMPRODUCT((Q_2>=4)*(Sales_ID=G5))

Thanks!

The previous formulas should still be done with SumIf though.
 

Tropez

New Member
Joined
Apr 19, 2004
Messages
35
I added the indirect statement to the sumproduct formula and it works perfectly!

Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,057
Messages
5,569,954
Members
412,299
Latest member
agentless
Top