Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referring to Sheet locations in a formula
In VBA, sheets can be referred to by either name or location.
Example: Sheets("Sheet2").Activate or Sheets(2).Activate Is there a way to refer to sheet locations in a formula? For example, I'd like to sum cell C1 on sheets 2 through 10 (the last sheet will be determined in a cell on sheet1). I'm thinking it will be an array, but I'm not sure about referring to sheet locations in a formula. Thanks In Advance. Paul |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referring to Sheet locations in a formula
Try this:
A1 = the last sheet number = 10 =SUMPRODUCT(SUMIF(INDIRECT("sheet"&ROW(INDIRECT("2 :"&A1))&"!C1"),"<")) This will sum Sheet2:Sheet10!C1 Biff "PCLIVE" <pclive(remove wrote in message ... In VBA, sheets can be referred to by either name or location. Example: Sheets("Sheet2").Activate or Sheets(2).Activate Is there a way to refer to sheet locations in a formula? For example, I'd like to sum cell C1 on sheets 2 through 10 (the last sheet will be determined in a cell on sheet1). I'm thinking it will be an array, but I'm not sure about referring to sheet locations in a formula. Thanks In Advance. Paul |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referring to Sheet locations in a formula
Thanks Biff,
The problem is that my sheets are not named "Sheet1", "Sheet2", etc. They have unique names without a number following. That is why I'm looking for away to have the formula to somehow reference the sheet location order rather than the name. I'm thinking I'll have to use VBA to get the result I'm looking for. But I figured I'd throw it out there. "T. Valko" wrote in message ... Try this: A1 = the last sheet number = 10 =SUMPRODUCT(SUMIF(INDIRECT("sheet"&ROW(INDIRECT("2 :"&A1))&"!C1"),"<")) This will sum Sheet2:Sheet10!C1 Biff "PCLIVE" <pclive(remove wrote in message ... In VBA, sheets can be referred to by either name or location. Example: Sheets("Sheet2").Activate or Sheets(2).Activate Is there a way to refer to sheet locations in a formula? For example, I'd like to sum cell C1 on sheets 2 through 10 (the last sheet will be determined in a cell on sheet1). I'm thinking it will be an array, but I'm not sure about referring to sheet locations in a formula. Thanks In Advance. Paul |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referring to Sheet locations in a formula
List your sheet names in a range of cells, say, A2:A6
In cells B2:B6 enter a 1 for those sheets that you want to include in the sum Then: =SUMPRODUCT(SUMIF(OFFSET(B2,ROW(B2:B6)-MIN(ROW(B2:B6)),),1,INDIRECT("'"&A2:A6&"'!C1"))) Eh, you might be better off using code! Biff "PCLIVE" <pclive(remove wrote in message ... Thanks Biff, The problem is that my sheets are not named "Sheet1", "Sheet2", etc. They have unique names without a number following. That is why I'm looking for away to have the formula to somehow reference the sheet location order rather than the name. I'm thinking I'll have to use VBA to get the result I'm looking for. But I figured I'd throw it out there. "T. Valko" wrote in message ... Try this: A1 = the last sheet number = 10 =SUMPRODUCT(SUMIF(INDIRECT("sheet"&ROW(INDIRECT("2 :"&A1))&"!C1"),"<")) This will sum Sheet2:Sheet10!C1 Biff "PCLIVE" <pclive(remove wrote in message ... In VBA, sheets can be referred to by either name or location. Example: Sheets("Sheet2").Activate or Sheets(2).Activate Is there a way to refer to sheet locations in a formula? For example, I'd like to sum cell C1 on sheets 2 through 10 (the last sheet will be determined in a cell on sheet1). I'm thinking it will be an array, but I'm not sure about referring to sheet locations in a formula. Thanks In Advance. Paul |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referring to Sheet locations in a formula
I understand that I can do that, but my sheet names are not all created yet,
so I was hoping that I could have something that would increment as I added sheets. I think I'll use VBA to get the second and last sheet names. Then I can reference it properly. "T. Valko" wrote in message ... List your sheet names in a range of cells, say, A2:A6 In cells B2:B6 enter a 1 for those sheets that you want to include in the sum Then: =SUMPRODUCT(SUMIF(OFFSET(B2,ROW(B2:B6)-MIN(ROW(B2:B6)),),1,INDIRECT("'"&A2:A6&"'!C1"))) Eh, you might be better off using code! Biff "PCLIVE" <pclive(remove wrote in message ... Thanks Biff, The problem is that my sheets are not named "Sheet1", "Sheet2", etc. They have unique names without a number following. That is why I'm looking for away to have the formula to somehow reference the sheet location order rather than the name. I'm thinking I'll have to use VBA to get the result I'm looking for. But I figured I'd throw it out there. "T. Valko" wrote in message ... Try this: A1 = the last sheet number = 10 =SUMPRODUCT(SUMIF(INDIRECT("sheet"&ROW(INDIRECT("2 :"&A1))&"!C1"),"<")) This will sum Sheet2:Sheet10!C1 Biff "PCLIVE" <pclive(remove wrote in message ... In VBA, sheets can be referred to by either name or location. Example: Sheets("Sheet2").Activate or Sheets(2).Activate Is there a way to refer to sheet locations in a formula? For example, I'd like to sum cell C1 on sheets 2 through 10 (the last sheet will be determined in a cell on sheet1). I'm thinking it will be an array, but I'm not sure about referring to sheet locations in a formula. Thanks In Advance. Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula referring to a different sheet | Excel Discussion (Misc queries) | |||
Referring to a list in one sheet to another column on a different | Excel Discussion (Misc queries) | |||
Referring to the previous selected sheet in a macro | Excel Discussion (Misc queries) | |||
Changing Formula Locations | Excel Discussion (Misc queries) | |||
referring to previous sheet | Excel Discussion (Misc queries) |