Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Indirect worksheet function
All,
I am using the =SUM('Start:<<End'!B7) formula to sum data from 50 sheets. I want to make this formula little dynamic. I want the '7" in the formula to be a cell link e.g. A1 so that it sums up the field in the cells whose row is defined by the number input in A!. I tried using =SUM(INDIRECT("('Start:<<End'!B"&A1) but this gives errors. Any solutions or alternatives to this method. Thanks much, RK |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Indirect worksheet function
Make a list of all your sheets
and name the list FL. =SUMPRODUCT(SUMIF(A1,A1,INDIRECT(FL&"!B"&A1))) If the list can be generated by some algorithm, further automation is possible. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Indirect worksheet function
Thanks Herbert.
I am new to lists, could you please explain little bit more on how to create the list. I looked at excel help and couldn't find a way to create a list across diff. sheets. thanks much, RK "Herbert Seidenberg" wrote: Make a list of all your sheets and name the list FL. =SUMPRODUCT(SUMIF(A1,A1,INDIRECT(FL&"!B"&A1))) If the list can be generated by some algorithm, further automation is possible. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Indirect worksheet function
The most straight forward way is to type
the sheet names into 50 adjacent cells and name those 50 cells FL. The automated way is to use a macro: Sub listsheets() For i = 1 To Worksheets.Count Cells(i, "A") = Sheets(i).Name 'List starts at A1. 'If you want to start the list at B22 use 'Cells(i + 21, "B") = Sheets(i).Name Next i End Sub If you sheet names have some order to them, or you are willing to change the names, then I can give you an easy shortcut. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect worksheet function | Excel Worksheet Functions | |||
SUM and INDIRECT to reference worksheet | Excel Discussion (Misc queries) | |||
INDIRECT Function impact on Copy Worksheet | Excel Worksheet Functions | |||
indirect worksheet function in excel | Excel Worksheet Functions | |||
INDIRECT worksheet with a space in the name | Excel Worksheet Functions |