Cell Reference with Range Name
 

I would like to be able to use a cell reference, in a formula, that points to
a cell that contains a Range Name. I hope I've stated that clearly. Just
in case, I'll give my example --

I have a table in which I've created many lookups, all of which look to a
range named Term1. I want to be able to copy my table, but I need to be
able to change each copy to look at Term2, Term3, Term4, etc.... So, I
thought I would just use a cell reference in my lookup formulas, and point to
a cell that contains whichever range I want to use. This way, I don't have
to change the formulas in my table. I know I can do a search and replace,
but I didn't want to have to do that.

So.. Is there a way to use a cell reference in a formula, where that cell
reference contains a range name?

Thanks, in advance.

Cell Reference with Range Name
 

Use INDIRECT():
Let's say we put 1,2,3,4,5,6,7,8,9 in cells A1 thru C3.
Let's say we assign the block A1:C3 the Name topleft.

Enter topleft in F1 and in F2 enter:

=SUM(INDIRECT(F1)) to display 45


--
Gary's Student


"SCSC" wrote:

I would like to be able to use a cell reference, in a formula, that points to
a cell that contains a Range Name. I hope I've stated that clearly. Just
in case, I'll give my example --

I have a table in which I've created many lookups, all of which look to a
range named Term1. I want to be able to copy my table, but I need to be
able to change each copy to look at Term2, Term3, Term4, etc.... So, I
thought I would just use a cell reference in my lookup formulas, and point to
a cell that contains whichever range I want to use. This way, I don't have
to change the formulas in my table. I know I can do a search and replace,
but I didn't want to have to do that.

So.. Is there a way to use a cell reference in a formula, where that cell
reference contains a range name?

Thanks, in advance.

Cell Reference with Range Name
 

THANKS! That works perfectly!


"Gary''s Student" wrote:

Use INDIRECT():
Let's say we put 1,2,3,4,5,6,7,8,9 in cells A1 thru C3.
Let's say we assign the block A1:C3 the Name topleft.

Enter topleft in F1 and in F2 enter:

=SUM(INDIRECT(F1)) to display 45


--
Gary's Student


"SCSC" wrote:

I would like to be able to use a cell reference, in a formula, that points to
a cell that contains a Range Name. I hope I've stated that clearly. Just
in case, I'll give my example --

I have a table in which I've created many lookups, all of which look to a
range named Term1. I want to be able to copy my table, but I need to be
able to change each copy to look at Term2, Term3, Term4, etc.... So, I
thought I would just use a cell reference in my lookup formulas, and point to
a cell that contains whichever range I want to use. This way, I don't have
to change the formulas in my table. I know I can do a search and replace,
but I didn't want to have to do that.

So.. Is there a way to use a cell reference in a formula, where that cell
reference contains a range name?

Thanks, in advance.