Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum values from the listed sheets
Hi everybody,
I'm having a little problem with my excel table. I have many sheets for entering data, they look exactly the same, just the sheet name is different. Then I also have the summary sheets, that are also the same as the one for data entry, just they contain formulas to sum data from data entry sheets. So what I'm trying to make a list of data entry sheets in the summary sheet and then the formula that would sum all the data entered in the listed sheets in the same cell address where the formula is located. The list of sheets to sum could contain let's say up to 30 places. And another quick question. Is it possible to somehow link make some kind of "master formula" and then use it in multiple cells, so when you change the master formula all the cells that use it change too. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum values from the listed sheets
On Oct 11, 8:08 am, wrote:
Hi everybody, I'm having a little problem with my excel table. I have many sheets for entering data, they look exactly the same, just the sheet name is different. Then I also have the summary sheets, that are also the same as the one for data entry, just they contain formulas to sum data from data entry sheets. So what I'm trying to make a list of data entry sheets in the summary sheet and then the formula that would sum all the data entered in the listed sheets in the same cell address where the formula is located. The list of sheets to sum could contain let's say up to 30 places. And another quick question. Is it possible to somehow link make some kind of "master formula" and then use it in multiple cells, so when you change the master formula all the cells that use it change too. Thanks. By now I came up with this formula, but it's for a list of 4 sheets only. =IF($AE$2="";0;INDIRECT("'"&$AE$2&"'"&"!"&ADDRESS( ROW();COLUMN()))) +IF($AE$3="";0;INDIRECT("'"&$AE$3&"'"&"!"&ADDRESS( ROW();COLUMN()))) +IF($AE$4="";0;INDIRECT("'"&$AE$4&"'"&"!"&ADDRESS( ROW();COLUMN()))) +IF($AE$5="";0;INDIRECT("'"&$AE$5&"'"&"!"&ADDRESS( ROW();COLUMN()))) The sheet names are in AE column. This certainly won't work for 30 sheets, as the formula would get too big to enter. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum values from the listed sheets
Hi
You can use something like =SUM(Sheet1:Sheet30!A1) OR, what I prefer, (and it isn't dependent upon having sheets named 1 to 30), you can insert 2 dummy sheets called First and Last. Drag these so that they encompass your sheets to be summed, in a sort of "sandwich", with your summary sheet outside of this range. Then =SUM(First:Last!A1) -- Regards Roger Govier wrote in message oups.com... Hi everybody, I'm having a little problem with my excel table. I have many sheets for entering data, they look exactly the same, just the sheet name is different. Then I also have the summary sheets, that are also the same as the one for data entry, just they contain formulas to sum data from data entry sheets. So what I'm trying to make a list of data entry sheets in the summary sheet and then the formula that would sum all the data entered in the listed sheets in the same cell address where the formula is located. The list of sheets to sum could contain let's say up to 30 places. And another quick question. Is it possible to somehow link make some kind of "master formula" and then use it in multiple cells, so when you change the master formula all the cells that use it change too. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum values from the listed sheets
Thanks for your answer Roger. I knew about this formula, but still I
would need the formula to sum according to provided list of data entry sheets. The formula I wrote in my second post works perfectly, the only problem is that it's going to be too long to enter if I make it for 30 sheets. On Oct 11, 12:20 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi You can use something like =SUM(Sheet1:Sheet30!A1) OR, what I prefer, (and it isn't dependent upon having sheets named 1 to 30), you can insert 2 dummy sheets called First and Last. Drag these so that they encompass your sheets to be summed, in a sort of "sandwich", with your summary sheet outside of this range. Then =SUM(First:Last!A1) -- Regards Roger Govier wrote in message oups.com... Hi everybody, I'm having a little problem with my excel table. I have many sheets for entering data, they look exactly the same, just the sheet name is different. Then I also have the summary sheets, that are also the same as the one for data entry, just they contain formulas to sum data from data entry sheets. So what I'm trying to make a list of data entry sheets in the summary sheet and then the formula that would sum all the data entered in the listed sheets in the same cell address where the formula is located. The list of sheets to sum could contain let's say up to 30 places. And another quick question. Is it possible to somehow link make some kind of "master formula" and then use it in multiple cells, so when you change the master formula all the cells that use it change too. Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum values from the listed sheets
Hi
Then try =SUMPRODUCT(N(INDIRECT("'"&AE2:AE30&"'!B1"))) You need to ensure that the range AE2:AE30 matches the range that contains Sheet names. If there are any blank cells in that range you will get a #REF error. -- Regards Roger Govier wrote in message oups.com... Thanks for your answer Roger. I knew about this formula, but still I would need the formula to sum according to provided list of data entry sheets. The formula I wrote in my second post works perfectly, the only problem is that it's going to be too long to enter if I make it for 30 sheets. On Oct 11, 12:20 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi You can use something like =SUM(Sheet1:Sheet30!A1) OR, what I prefer, (and it isn't dependent upon having sheets named 1 to 30), you can insert 2 dummy sheets called First and Last. Drag these so that they encompass your sheets to be summed, in a sort of "sandwich", with your summary sheet outside of this range. Then =SUM(First:Last!A1) -- Regards Roger Govier wrote in message oups.com... Hi everybody, I'm having a little problem with my excel table. I have many sheets for entering data, they look exactly the same, just the sheet name is different. Then I also have the summary sheets, that are also the same as the one for data entry, just they contain formulas to sum data from data entry sheets. So what I'm trying to make a list of data entry sheets in the summary sheet and then the formula that would sum all the data entered in the listed sheets in the same cell address where the formula is located. The list of sheets to sum could contain let's say up to 30 places. And another quick question. Is it possible to somehow link make some kind of "master formula" and then use it in multiple cells, so when you change the master formula all the cells that use it change too. Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum values from the listed sheets
Great, this is just what I needed. I just changed the formula so I
could use it in any cell. So now it is: =SUMPRODUCT(N(INDIRECT("'"&AE2:AE30&"'!"&ADDRESS(R OW();COLUMN())))) Thanks a lot man, this will be a huge time save for me! There's just another small problem. As I will have many summary sheets where many cells will be using this formula, it would be great if I could somehow make one "master cell" in a sheet where I could make the changes to the formula(to define the range for sheet names and avoid the #REF error), which would then be applied to all cells using the formula. On Oct 11, 4:36 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi Then try =SUMPRODUCT(N(INDIRECT("'"&AE2:AE30&"'!B1"))) You need to ensure that the range AE2:AE30 matches the range that contains Sheet names. If there are any blank cells in that range you will get a #REF error. -- Regards Roger Govier wrote in message oups.com... Thanks for your answer Roger. I knew about this formula, but still I would need the formula to sum according to provided list of data entry sheets. The formula I wrote in my second post works perfectly, the only problem is that it's going to be too long to enter if I make it for 30 sheets. On Oct 11, 12:20 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi You can use something like =SUM(Sheet1:Sheet30!A1) OR, what I prefer, (and it isn't dependent upon having sheets named 1 to 30), you can insert 2 dummy sheets called First and Last. Drag these so that they encompass your sheets to be summed, in a sort of "sandwich", with your summary sheet outside of this range. Then =SUM(First:Last!A1) -- Regards Roger Govier wrote in message groups.com... Hi everybody, I'm having a little problem with my excel table. I have many sheets for entering data, they look exactly the same, just the sheet name is different. Then I also have the summary sheets, that are also the same as the one for data entry, just they contain formulas to sum data from data entry sheets. So what I'm trying to make a list of data entry sheets in the summary sheet and then the formula that would sum all the data entered in the listed sheets in the same cell address where the formula is located. The list of sheets to sum could contain let's say up to 30 places. And another quick question. Is it possible to somehow link make some kind of "master formula" and then use it in multiple cells, so when you change the master formula all the cells that use it change too. Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum values from the listed sheets
Hi
Define a named range InsertNameDefine. Name MyRng Refers to = OFFSET($AE$2,0,0,COUNTA($AE:$AE)) =SUMPRODUCT(N(INDIRECT("'"&MyRng&"'!"&ADDRESS(ROW( );COLUMN())))) -- Regards Roger Govier wrote in message ups.com... Great, this is just what I needed. I just changed the formula so I could use it in any cell. So now it is: =SUMPRODUCT(N(INDIRECT("'"&AE2:AE30&"'!"&ADDRESS(R OW();COLUMN())))) Thanks a lot man, this will be a huge time save for me! There's just another small problem. As I will have many summary sheets where many cells will be using this formula, it would be great if I could somehow make one "master cell" in a sheet where I could make the changes to the formula(to define the range for sheet names and avoid the #REF error), which would then be applied to all cells using the formula. On Oct 11, 4:36 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi Then try =SUMPRODUCT(N(INDIRECT("'"&AE2:AE30&"'!B1"))) You need to ensure that the range AE2:AE30 matches the range that contains Sheet names. If there are any blank cells in that range you will get a #REF error. -- Regards Roger Govier wrote in message oups.com... Thanks for your answer Roger. I knew about this formula, but still I would need the formula to sum according to provided list of data entry sheets. The formula I wrote in my second post works perfectly, the only problem is that it's going to be too long to enter if I make it for 30 sheets. On Oct 11, 12:20 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi You can use something like =SUM(Sheet1:Sheet30!A1) OR, what I prefer, (and it isn't dependent upon having sheets named 1 to 30), you can insert 2 dummy sheets called First and Last. Drag these so that they encompass your sheets to be summed, in a sort of "sandwich", with your summary sheet outside of this range. Then =SUM(First:Last!A1) -- Regards Roger Govier wrote in message groups.com... Hi everybody, I'm having a little problem with my excel table. I have many sheets for entering data, they look exactly the same, just the sheet name is different. Then I also have the summary sheets, that are also the same as the one for data entry, just they contain formulas to sum data from data entry sheets. So what I'm trying to make a list of data entry sheets in the summary sheet and then the formula that would sum all the data entered in the listed sheets in the same cell address where the formula is located. The list of sheets to sum could contain let's say up to 30 places. And another quick question. Is it possible to somehow link make some kind of "master formula" and then use it in multiple cells, so when you change the master formula all the cells that use it change too. Thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum values from the listed sheets
Hi,
I tried this, but it seems it doesn't work. It gives me the #REF error, and I've been able to define the range just with the the sheet names, for example: =OFFSET(Sheet1!$AE$3;0;0;COUNTA(Sheet1!$AE:$AE)) It won't let me do it without "Sheet1!" What could be the problem? On Oct 12, 2:43 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi Define a named range InsertNameDefine. Name MyRng Refers to = OFFSET($AE$2,0,0,COUNTA($AE:$AE)) =SUMPRODUCT(N(INDIRECT("'"&MyRng&"'!"&ADDRESS(ROW( );COLUMN())))) -- Regards Roger Govier wrote in message ups.com... Great, this is just what I needed. I just changed the formula so I could use it in any cell. So now it is: =SUMPRODUCT(N(INDIRECT("'"&AE2:AE30&"'!"&ADDRESS(R OW();COLUMN())))) Thanks a lot man, this will be a huge time save for me! There's just another small problem. As I will have many summary sheets where many cells will be using this formula, it would be great if I could somehow make one "master cell" in a sheet where I could make the changes to the formula(to define the range for sheet names and avoid the #REF error), which would then be applied to all cells using the formula. On Oct 11, 4:36 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi Then try =SUMPRODUCT(N(INDIRECT("'"&AE2:AE30&"'!B1"))) You need to ensure that the range AE2:AE30 matches the range that contains Sheet names. If there are any blank cells in that range you will get a #REF error. -- Regards Roger Govier wrote in message groups.com... Thanks for your answer Roger. I knew about this formula, but still I would need the formula to sum according to provided list of data entry sheets. The formula I wrote in my second post works perfectly, the only problem is that it's going to be too long to enter if I make it for 30 sheets. On Oct 11, 12:20 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi You can use something like =SUM(Sheet1:Sheet30!A1) OR, what I prefer, (and it isn't dependent upon having sheets named 1 to 30), you can insert 2 dummy sheets called First and Last. Drag these so that they encompass your sheets to be summed, in a sort of "sandwich", with your summary sheet outside of this range. Then =SUM(First:Last!A1) -- Regards Roger Govier wrote in message groups.com... Hi everybody, I'm having a little problem with my excel table. I have many sheets for entering data, they look exactly the same, just the sheet name is different. Then I also have the summary sheets, that are also the same as the one for data entry, just they contain formulas to sum data from data entry sheets. So what I'm trying to make a list of data entry sheets in the summary sheet and then the formula that would sum all the data entered in the listed sheets in the same cell address where the formula is located. The list of sheets to sum could contain let's say up to 30 places. And another quick question. Is it possible to somehow link make some kind of "master formula" and then use it in multiple cells, so when you change the master formula all the cells that use it change too. Thanks. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum values from the listed sheets
Hi
Yes it will include the sheet references. Just ensure that you are on the correct sheet when you define the named range. Worked fine for me. -- Regards Roger Govier wrote in message oups.com... Hi, I tried this, but it seems it doesn't work. It gives me the #REF error, and I've been able to define the range just with the the sheet names, for example: =OFFSET(Sheet1!$AE$3;0;0;COUNTA(Sheet1!$AE:$AE)) It won't let me do it without "Sheet1!" What could be the problem? On Oct 12, 2:43 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi Define a named range InsertNameDefine. Name MyRng Refers to = OFFSET($AE$2,0,0,COUNTA($AE:$AE)) =SUMPRODUCT(N(INDIRECT("'"&MyRng&"'!"&ADDRESS(ROW( );COLUMN())))) -- Regards Roger Govier wrote in message ups.com... Great, this is just what I needed. I just changed the formula so I could use it in any cell. So now it is: =SUMPRODUCT(N(INDIRECT("'"&AE2:AE30&"'!"&ADDRESS(R OW();COLUMN())))) Thanks a lot man, this will be a huge time save for me! There's just another small problem. As I will have many summary sheets where many cells will be using this formula, it would be great if I could somehow make one "master cell" in a sheet where I could make the changes to the formula(to define the range for sheet names and avoid the #REF error), which would then be applied to all cells using the formula. On Oct 11, 4:36 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi Then try =SUMPRODUCT(N(INDIRECT("'"&AE2:AE30&"'!B1"))) You need to ensure that the range AE2:AE30 matches the range that contains Sheet names. If there are any blank cells in that range you will get a #REF error. -- Regards Roger Govier wrote in message groups.com... Thanks for your answer Roger. I knew about this formula, but still I would need the formula to sum according to provided list of data entry sheets. The formula I wrote in my second post works perfectly, the only problem is that it's going to be too long to enter if I make it for 30 sheets. On Oct 11, 12:20 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi You can use something like =SUM(Sheet1:Sheet30!A1) OR, what I prefer, (and it isn't dependent upon having sheets named 1 to 30), you can insert 2 dummy sheets called First and Last. Drag these so that they encompass your sheets to be summed, in a sort of "sandwich", with your summary sheet outside of this range. Then =SUM(First:Last!A1) -- Regards Roger Govier wrote in message groups.com... Hi everybody, I'm having a little problem with my excel table. I have many sheets for entering data, they look exactly the same, just the sheet name is different. Then I also have the summary sheets, that are also the same as the one for data entry, just they contain formulas to sum data from data entry sheets. So what I'm trying to make a list of data entry sheets in the summary sheet and then the formula that would sum all the data entered in the listed sheets in the same cell address where the formula is located. The list of sheets to sum could contain let's say up to 30 places. And another quick question. Is it possible to somehow link make some kind of "master formula" and then use it in multiple cells, so when you change the master formula all the cells that use it change too. Thanks. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum values from the listed sheets
OK, I got what I want now:
=SUMPRODUCT(N(INDIRECT("'"&OFFSET($AE$2;0;0;COUNTA ($AE: $AE)-1;1)&"'!"&ADDRESS(ROW();COLUMN())))) This way I don't have to define name for the range and I can use the formula anywhere on any sheet. Thank you very much Roger, you really saved me a lot of time. I hope someone else can benefit from this too. Best regards. On Oct 14, 10:36 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi Yes it will include the sheet references. Just ensure that you are on the correct sheet when you define the named range. Worked fine for me. -- Regards Roger Govier wrote in message oups.com... Hi, I tried this, but it seems it doesn't work. It gives me the #REF error, and I've been able to define the range just with the the sheet names, for example: =OFFSET(Sheet1!$AE$3;0;0;COUNTA(Sheet1!$AE:$AE)) It won't let me do it without "Sheet1!" What could be the problem? On Oct 12, 2:43 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi Define a named range InsertNameDefine. Name MyRng Refers to = OFFSET($AE$2,0,0,COUNTA($AE:$AE)) =SUMPRODUCT(N(INDIRECT("'"&MyRng&"'!"&ADDRESS(ROW( );COLUMN())))) -- Regards Roger Govier wrote in message roups.com... Great, this is just what I needed. I just changed the formula so I could use it in any cell. So now it is: =SUMPRODUCT(N(INDIRECT("'"&AE2:AE30&"'!"&ADDRESS(R OW();COLUMN())))) Thanks a lot man, this will be a huge time save for me! There's just another small problem. As I will have many summary sheets where many cells will be using this formula, it would be great if I could somehow make one "master cell" in a sheet where I could make the changes to the formula(to define the range for sheet names and avoid the #REF error), which would then be applied to all cells using the formula. On Oct 11, 4:36 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi Then try =SUMPRODUCT(N(INDIRECT("'"&AE2:AE30&"'!B1"))) You need to ensure that the range AE2:AE30 matches the range that contains Sheet names. If there are any blank cells in that range you will get a #REF error. -- Regards Roger Govier wrote in message groups.com... Thanks for your answer Roger. I knew about this formula, but still I would need the formula to sum according to provided list of data entry sheets. The formula I wrote in my second post works perfectly, the only problem is that it's going to be too long to enter if I make it for 30 sheets. On Oct 11, 12:20 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi You can use something like =SUM(Sheet1:Sheet30!A1) OR, what I prefer, (and it isn't dependent upon having sheets named 1 to 30), you can insert 2 dummy sheets called First and Last. Drag these so that they encompass your sheets to be summed, in a sort of "sandwich", with your summary sheet outside of this range. Then =SUM(First:Last!A1) -- Regards Roger Govier wrote in message groups.com... Hi everybody, I'm having a little problem with my excel table. I have many sheets for entering data, they look exactly the same, just the sheet name is different. Then I also have the summary sheets, that are also the same as the one for data entry, just they contain formulas to sum data from data entry sheets. So what I'm trying to make a list of data entry sheets in the summary sheet and then the formula that would sum all the data entered in the listed sheets in the same cell address where the formula is located. The list of sheets to sum could contain let's say up to 30 places. And another quick question. Is it possible to somehow link make some kind of "master formula" and then use it in multiple cells, so when you change the master formula all the cells that use it change too. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do use listed pathname to link values from another workbook? | Excel Discussion (Misc queries) | |||
Find values not listed in two sheets | Excel Discussion (Misc queries) | |||
hiding zero values on all sheets & by default on new sheets | Excel Worksheet Functions | |||
I need to run queries against values in other sheets | Excel Discussion (Misc queries) | |||
Min and Max values from over 200 sheets | Excel Discussion (Misc queries) |