Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
refer to sequential sheets in a formula
I have 100 sheets in a workbook, with each sheet named "1" to "100"
sequentially. In a summary sheet, I want to include a formula that refers to a range of cells in each sheet separately (i.e. each sheet sequentially rather than all sheets together). I have created a countif formula in cell A1 on my summary sheet that counts the number of entries in a defined range on sheet 1. I then want to pull down this formula such that Cell A2 counts from the same defined range in sheet 2 and so on. That is, I want the range of cells to stay the same, but the sheet name to change (to the next sheet in the workbook) when I drag down the formula. Any pointers would be helpful. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
refer to sequential sheets in a formula
Suppose you had this in A1:
=COUNTIF('1'!A1:A5,"=5") Replace the above with this instead, in A1: =COUNTIF(INDIRECT("'"&ROW(A1)&"'!A1:A5"),"=5") Then just copy A1 down 100 rows to A100 to extract the required results from each of the 100 sheets named as: 1,2,3, ... 100 Note that the same formula above [ie with ROW(A1)] should be used in any starting cell, if it's other than A1. ROW(A1) is the incrementer part within the formula. Adapt to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "seanoniallain" wrote: I have 100 sheets in a workbook, with each sheet named "1" to "100" sequentially. In a summary sheet, I want to include a formula that refers to a range of cells in each sheet separately (i.e. each sheet sequentially rather than all sheets together). I have created a countif formula in cell A1 on my summary sheet that counts the number of entries in a defined range on sheet 1. I then want to pull down this formula such that Cell A2 counts from the same defined range in sheet 2 and so on. That is, I want the range of cells to stay the same, but the sheet name to change (to the next sheet in the workbook) when I drag down the formula. Any pointers would be helpful. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
refer to sequential sheets in a formula
Thanks Max! This works.
"Max" wrote: Suppose you had this in A1: =COUNTIF('1'!A1:A5,"=5") Replace the above with this instead, in A1: =COUNTIF(INDIRECT("'"&ROW(A1)&"'!A1:A5"),"=5") Then just copy A1 down 100 rows to A100 to extract the required results from each of the 100 sheets named as: 1,2,3, ... 100 Note that the same formula above [ie with ROW(A1)] should be used in any starting cell, if it's other than A1. ROW(A1) is the incrementer part within the formula. Adapt to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "seanoniallain" wrote: I have 100 sheets in a workbook, with each sheet named "1" to "100" sequentially. In a summary sheet, I want to include a formula that refers to a range of cells in each sheet separately (i.e. each sheet sequentially rather than all sheets together). I have created a countif formula in cell A1 on my summary sheet that counts the number of entries in a defined range on sheet 1. I then want to pull down this formula such that Cell A2 counts from the same defined range in sheet 2 and so on. That is, I want the range of cells to stay the same, but the sheet name to change (to the next sheet in the workbook) when I drag down the formula. Any pointers would be helpful. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
refer to sequential sheets in a formula
Good to hear that. Thanks for feeding back.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "seanoniallain" wrote in message ... Thanks Max! This works. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible? | Excel Worksheet Functions | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Match then lookup | Excel Worksheet Functions | |||
How do I make a formula refer to given data even if I do a sort f. | Excel Discussion (Misc queries) |