Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT function to reference values in another worksheet
I have multiple worksheets each representing a year. In a summary
sheet I want to return values from each year's worksheet. I'm using =INDIRECT("'"&A$1&"'!$B5") in which A$1 returns the year at the top of the summary worksheet which corresponds to the name of a single year worksheet (e.g. A1 contains "1975", which is also the name of a worksheet). Although this works and returns the contents of cell B5 in worksheet 1975, my problem is that when I copy the formula down a column the "$B5" component doesn't update. So as I move down a column I want the formula to read: =INDIRECT("'"&A$1&"'!$B5") =INDIRECT("'"&A$1&"'!$B6") =INDIRECT("'"&A$1&"'!$B7") etc, but it remains =INDIRECT("'"&A$1&"'! $B5"). (The A$1 component updates fine as it moves across rows, e.g. B$1, C $1, D$1 etc.) Any ideas? Many thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT function to reference values in another worksheet
It doesn't change because it is inside quotes, and so gets treated as
if it is text rather than a cell reference. Try something like this: =INDIRECT("'"&A$1&"'!$B"&ROW(A5)) ROW(A5) will return 5 the first time you use it, but will increment to ROW(A6), ROW(A7) etc as it is copied down, thus returning 6, 7 etc. Hope this helps. Pete On Feb 1, 12:26*pm, Rich wrote: I have multiple worksheets each representing a year. In a summary sheet I want to return values from each year's worksheet. I'm using =INDIRECT("'"&A$1&"'!$B5") in which A$1 returns the year at the top of the summary worksheet which corresponds to the name of a single year worksheet (e.g. A1 contains "1975", which is also the name of a worksheet). Although this works and returns the contents of cell B5 in worksheet 1975, my problem is that when I copy the formula down a column the "$B5" component doesn't update. So as I move down a column I want the formula to read: =INDIRECT("'"&A$1&"'!$B5") =INDIRECT("'"&A$1&"'!$B6") =INDIRECT("'"&A$1&"'!$B7") etc, but it remains =INDIRECT("'"&A$1&"'! $B5"). (The A$1 component updates fine as it moves across rows, e.g. B$1, C $1, D$1 etc.) Any ideas? Many thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell reference OR INDIRECT function | Excel Worksheet Functions | |||
Indirect reference to another worksheet in file | Excel Discussion (Misc queries) | |||
SUM and INDIRECT to reference worksheet | Excel Discussion (Misc queries) | |||
Use INDIRECT function to reference a value in closed file | Excel Worksheet Functions | |||
Indirect reference from one worksheet to another | Excel Worksheet Functions |