Named Range Reference

[Tropez]

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:

=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?

=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]

 

[Aladin Akyurek]

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:

=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?

=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]

That would work fine, except I just realized that I have a second formula that looks for those items >=4.

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

Thanks!

 

[Aladin Akyurek]

That would work fine, except I just realized that I have a second formula that looks for those items >=4.

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

Thanks!

The previous formulas should still be done with SumIf though.

 

[Tropez]

I added the indirect statement to the sumproduct formula and it works perfectly!

Thank you!