Named Ranges
 

I have a named range called "Range_1" which is set as:

=OFFSET(Total!$E$3,0,('Graph Data'!$J$2-1)*5+'Graph
Data'!$I$2-1,COUNTA(Total!$B:$B)-1,1)

It works fine, and essentially keeps the named range to the length of the
list of items I have on the "Total" page. Problem is, I want this same named
range to be able to pick the page it is supposed to be looking at based on a
text value entered into a cell.

If I use the formula "=ADDRESS(3,5,1,1,"Total")" then I get the answer
"Total!$E$3". I can manipluate this formula to give me the sheet I am
looking for to use in the offset (there are 13 in total).

If I combine the OFFSET formula with the ADDRESS formula, it won't work. I
thoght I could use a named range of :

=OFFSET(ADDRESS(3,5,1,1,"Total"),0,('Graph Data'!$J$2-1)*5+'Graph
Data'!$I$2-1,COUNTA(Total!$B:$B)-1,1)

to give me the same named range. But it does not work.
Does anyone know how I can achieve what I want?
Does anyone even know what I am talking about?!!!

Rich

Named Ranges
 

Try it like this:

A1 = Total

=OFFSET(INDIRECT("'"&A1&"'!E3").........

--
Biff
Microsoft Excel MVP


"Rich" wrote in message
...
I have a named range called "Range_1" which is set as:

=OFFSET(Total!$E$3,0,('Graph Data'!$J$2-1)*5+'Graph
Data'!$I$2-1,COUNTA(Total!$B:$B)-1,1)

It works fine, and essentially keeps the named range to the length of the
list of items I have on the "Total" page. Problem is, I want this same
named
range to be able to pick the page it is supposed to be looking at based on
a
text value entered into a cell.

If I use the formula "=ADDRESS(3,5,1,1,"Total")" then I get the answer
"Total!$E$3". I can manipluate this formula to give me the sheet I am
looking for to use in the offset (there are 13 in total).

If I combine the OFFSET formula with the ADDRESS formula, it won't work.
I
thoght I could use a named range of :

=OFFSET(ADDRESS(3,5,1,1,"Total"),0,('Graph Data'!$J$2-1)*5+'Graph
Data'!$I$2-1,COUNTA(Total!$B:$B)-1,1)

to give me the same named range. But it does not work.
Does anyone know how I can achieve what I want?
Does anyone even know what I am talking about?!!!

Rich

Named Ranges
 

The ADDRESS function is returning a literal of the address. To use it as an
address you have to put it inside the INDIRECT function as in:

=OFFSET(INDIRECT(ADDRESS(3,5,1,1,"Total")),0,('Gra ph Data'!$J$2-1)*5+'Graph
Data'!$I$2-1,COUNTA(Total!$B:$B)-1,1)

Tyro

"Rich" wrote in message
...
I have a named range called "Range_1" which is set as:

=OFFSET(Total!$E$3,0,('Graph Data'!$J$2-1)*5+'Graph
Data'!$I$2-1,COUNTA(Total!$B:$B)-1,1)

It works fine, and essentially keeps the named range to the length of the
list of items I have on the "Total" page. Problem is, I want this same
named
range to be able to pick the page it is supposed to be looking at based on
a
text value entered into a cell.

If I use the formula "=ADDRESS(3,5,1,1,"Total")" then I get the answer
"Total!$E$3". I can manipluate this formula to give me the sheet I am
looking for to use in the offset (there are 13 in total).

If I combine the OFFSET formula with the ADDRESS formula, it won't work.
I
thoght I could use a named range of :

=OFFSET(ADDRESS(3,5,1,1,"Total"),0,('Graph Data'!$J$2-1)*5+'Graph
Data'!$I$2-1,COUNTA(Total!$B:$B)-1,1)

to give me the same named range. But it does not work.
Does anyone know how I can achieve what I want?
Does anyone even know what I am talking about?!!!

Rich