Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Ranges | Excel Discussion (Misc queries) | |||
Named ranges | Excel Discussion (Misc queries) | |||
named ranges | Excel Worksheet Functions | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
Named Ranges | Excel Worksheet Functions |