Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically updating summary worksheet
Hi,
I have several tabs (worksheets) representing purchases for a specific year. There is one tab (worksheet) that is a Summary of all purchases for all years. How do I get Excel to automatically update the Summary worksheet anytime one of the other worksheets are modified? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically updating summary worksheet
Hi srpettew,
Assuming you summary sheet has formulae linking to the others, the data should update automatically anytime the worksheet re-calculates. Cheers "srpettew" wrote in message ... Hi, I have several tabs (worksheets) representing purchases for a specific year. There is one tab (worksheet) that is a Summary of all purchases for all years. How do I get Excel to automatically update the Summary worksheet anytime one of the other worksheets are modified? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically updating summary worksheet
Hi macropod,
I'm trying to figure out how to do this (formula linking to others). Thanks. "macropod" wrote: Hi srpettew, Assuming you summary sheet has formulae linking to the others, the data should update automatically anytime the worksheet re-calculates. Cheers "srpettew" wrote in message ... Hi, I have several tabs (worksheets) representing purchases for a specific year. There is one tab (worksheet) that is a Summary of all purchases for all years. How do I get Excel to automatically update the Summary worksheet anytime one of the other worksheets are modified? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically updating summary worksheet
Well, at it's simplest:
..Select the target cell (i.e. the one on your Summary sheet you want to have updated automatically), .. press the '=' sign .. select the source cell .. press <Enter Now the two are linked and the value on the Summary sheet should update whenever the source cell is updated. In essence, the procedure is the same as you might use with the mouse to reference another cell on the same worksheet - the only significant difference is that you're selecting another worksheet's tab along the way. Cheers "srpettew" wrote in message ... Hi macropod, I'm trying to figure out how to do this (formula linking to others). Thanks. "macropod" wrote: Hi srpettew, Assuming you summary sheet has formulae linking to the others, the data should update automatically anytime the worksheet re-calculates. Cheers "srpettew" wrote in message ... Hi, I have several tabs (worksheets) representing purchases for a specific year. There is one tab (worksheet) that is a Summary of all purchases for all years. How do I get Excel to automatically update the Summary worksheet anytime one of the other worksheets are modified? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically updating summary worksheet
I have a running tally on a summary sheet. I don't want to lose those
numbers. If I make the specific cell equal to another cell in another workbook, it will change my summary. All I really want to do is add the summary workbook cell with any updates made in the current year workbook. For example, if the total number of licenses for a product is 7 (summary), and I purchase 2 more licenses this year, I want the summary to now show 9. Does this make sense? Thanks, SP "macropod" wrote: Well, at it's simplest: ..Select the target cell (i.e. the one on your Summary sheet you want to have updated automatically), .. press the '=' sign .. select the source cell .. press <Enter Now the two are linked and the value on the Summary sheet should update whenever the source cell is updated. In essence, the procedure is the same as you might use with the mouse to reference another cell on the same worksheet - the only significant difference is that you're selecting another worksheet's tab along the way. Cheers "srpettew" wrote in message ... Hi macropod, I'm trying to figure out how to do this (formula linking to others). Thanks. "macropod" wrote: Hi srpettew, Assuming you summary sheet has formulae linking to the others, the data should update automatically anytime the worksheet re-calculates. Cheers "srpettew" wrote in message ... Hi, I have several tabs (worksheets) representing purchases for a specific year. There is one tab (worksheet) that is a Summary of all purchases for all years. How do I get Excel to automatically update the Summary worksheet anytime one of the other worksheets are modified? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically updating summary worksheet
Hi srpettew,
OK, so you have a 'current year *workbook*' which, if I understand you correctly, has 'a running tally on a summary sheet'. I assume that's not the same *worksheet* that has the licence data, since your original post said you 'have several tabs (worksheets) representing purchases for a specific year'. In that case, a solution along the lines I proposed will work - I didn't mention anything about linking to another *workbook* - though you could do that too if there was a case for doing so. So, what exactly do you want to appear in your summary: - the sum of all cells in a specified location; or - the sum of all cells in a specified column, on all worksheets? Something else? Cheers "srpettew" wrote in message ... I have a running tally on a summary sheet. I don't want to lose those numbers. If I make the specific cell equal to another cell in another workbook, it will change my summary. All I really want to do is add the summary workbook cell with any updates made in the current year workbook. For example, if the total number of licenses for a product is 7 (summary), and I purchase 2 more licenses this year, I want the summary to now show 9. Does this make sense? Thanks, SP "macropod" wrote: Well, at it's simplest: ..Select the target cell (i.e. the one on your Summary sheet you want to have updated automatically), .. press the '=' sign .. select the source cell .. press <Enter Now the two are linked and the value on the Summary sheet should update whenever the source cell is updated. In essence, the procedure is the same as you might use with the mouse to reference another cell on the same worksheet - the only significant difference is that you're selecting another worksheet's tab along the way. Cheers "srpettew" wrote in message ... Hi macropod, I'm trying to figure out how to do this (formula linking to others). Thanks. "macropod" wrote: Hi srpettew, Assuming you summary sheet has formulae linking to the others, the data should update automatically anytime the worksheet re-calculates. Cheers "srpettew" wrote in message ... Hi, I have several tabs (worksheets) representing purchases for a specific year. There is one tab (worksheet) that is a Summary of all purchases for all years. How do I get Excel to automatically update the Summary worksheet anytime one of the other worksheets are modified? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically updating summary worksheet
Hi Macropod,
I think I've figured out what I need to do, and there is no easy way to go about it. Yes, I have multiple worksheets with a list of purchased licenses for a specific year. Problem is, the rows are all different because different licenses were purchased each year, so the cells won't match up. I think I just need to go in the summary sheet and reference the specific cell on each sheet. A couple of them have over 400, so this should be fun! Thank you for your responses. They are appreciated. SP "macropod" wrote: Hi srpettew, OK, so you have a 'current year *workbook*' which, if I understand you correctly, has 'a running tally on a summary sheet'. I assume that's not the same *worksheet* that has the licence data, since your original post said you 'have several tabs (worksheets) representing purchases for a specific year'. In that case, a solution along the lines I proposed will work - I didn't mention anything about linking to another *workbook* - though you could do that too if there was a case for doing so. So, what exactly do you want to appear in your summary: - the sum of all cells in a specified location; or - the sum of all cells in a specified column, on all worksheets? Something else? Cheers "srpettew" wrote in message ... I have a running tally on a summary sheet. I don't want to lose those numbers. If I make the specific cell equal to another cell in another workbook, it will change my summary. All I really want to do is add the summary workbook cell with any updates made in the current year workbook. For example, if the total number of licenses for a product is 7 (summary), and I purchase 2 more licenses this year, I want the summary to now show 9. Does this make sense? Thanks, SP "macropod" wrote: Well, at it's simplest: ..Select the target cell (i.e. the one on your Summary sheet you want to have updated automatically), .. press the '=' sign .. select the source cell .. press <Enter Now the two are linked and the value on the Summary sheet should update whenever the source cell is updated. In essence, the procedure is the same as you might use with the mouse to reference another cell on the same worksheet - the only significant difference is that you're selecting another worksheet's tab along the way. Cheers "srpettew" wrote in message ... Hi macropod, I'm trying to figure out how to do this (formula linking to others). Thanks. "macropod" wrote: Hi srpettew, Assuming you summary sheet has formulae linking to the others, the data should update automatically anytime the worksheet re-calculates. Cheers "srpettew" wrote in message ... Hi, I have several tabs (worksheets) representing purchases for a specific year. There is one tab (worksheet) that is a Summary of all purchases for all years. How do I get Excel to automatically update the Summary worksheet anytime one of the other worksheets are modified? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically updating summary worksheet
Hi srpettew,
Ah, but if they're all in the same column, it's pretty straightforward. For example, say your source values are in column A on Sheet 2: =SUM(Sheet2!A:A) Even if sheet2 has a column total, it's no harder than: =SUM(Sheet2!A:A)/2 and spanning multiple worksheets is just a small step from there. For example, say your source values are in column A on Sheets 2 to 6: =SUM(Sheet2:Sheet6!A:A) to get the total of sheets2 and 6, plus any whose tabs are between them! And, by extension if all the sheets a column total, it's no harder than: =SUM(Sheet2:Sheet6!A:A)/2 If your sheets have sub-totals, but you've also got text that uniquely identifies the sheet total (eg 'Total') in column A and the values in Column B, you could use something like: =SUMIF(Sheet2!A:A,"Total",Sheet2!B:B) or, for multiple sheets: =SUMIF(Sheet2!A:A,"Total",Sheet2!B:B)+SUMIF(Sheet3 !A:A,"Total",Sheet3!B:B)+S UMIF(Sheet4!A:A,"Total",Sheet4!B:B)+SUMIF(Sheet5!A :A,"Total",Sheet5!B:B)+SUM IF(Sheet6!A:A,"Total",Sheet6!B:B) If you data are organised across rows instead of down columns, just use row ranges instead. Cheers "srpettew" wrote in message ... Hi Macropod, I think I've figured out what I need to do, and there is no easy way to go about it. Yes, I have multiple worksheets with a list of purchased licenses for a specific year. Problem is, the rows are all different because different licenses were purchased each year, so the cells won't match up. I think I just need to go in the summary sheet and reference the specific cell on each sheet. A couple of them have over 400, so this should be fun! Thank you for your responses. They are appreciated. SP "macropod" wrote: Hi srpettew, OK, so you have a 'current year *workbook*' which, if I understand you correctly, has 'a running tally on a summary sheet'. I assume that's not the same *worksheet* that has the licence data, since your original post said you 'have several tabs (worksheets) representing purchases for a specific year'. In that case, a solution along the lines I proposed will work - I didn't mention anything about linking to another *workbook* - though you could do that too if there was a case for doing so. So, what exactly do you want to appear in your summary: - the sum of all cells in a specified location; or - the sum of all cells in a specified column, on all worksheets? Something else? Cheers "srpettew" wrote in message ... I have a running tally on a summary sheet. I don't want to lose those numbers. If I make the specific cell equal to another cell in another workbook, it will change my summary. All I really want to do is add the summary workbook cell with any updates made in the current year workbook. For example, if the total number of licenses for a product is 7 (summary), and I purchase 2 more licenses this year, I want the summary to now show 9. Does this make sense? Thanks, SP "macropod" wrote: Well, at it's simplest: ..Select the target cell (i.e. the one on your Summary sheet you want to have updated automatically), .. press the '=' sign .. select the source cell .. press <Enter Now the two are linked and the value on the Summary sheet should update whenever the source cell is updated. In essence, the procedure is the same as you might use with the mouse to reference another cell on the same worksheet - the only significant difference is that you're selecting another worksheet's tab along the way. Cheers "srpettew" wrote in message ... Hi macropod, I'm trying to figure out how to do this (formula linking to others). Thanks. "macropod" wrote: Hi srpettew, Assuming you summary sheet has formulae linking to the others, the data should update automatically anytime the worksheet re-calculates. Cheers "srpettew" wrote in message ... Hi, I have several tabs (worksheets) representing purchases for a specific year. There is one tab (worksheet) that is a Summary of all purchases for all years. How do I get Excel to automatically update the Summary worksheet anytime one of the other worksheets are modified? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically updating summary worksheet
Well, my problem is like this. I have software titles in Column A and then
columns B-M represent each month of the year. So if an Adobe Acrobat 7.0 license was purchased in March of a particular year, then column D would have a "1" for whatever row Adobe Acrobat 7.0 is on. The problem is that Adobe Acrobat 7.0 will not show up on certain worksheets because it did not exist. So the rows will not match up on the worksheets. This is why I have to treat every cell individually on the summary sheet. Thanks again, SP "macropod" wrote: Hi srpettew, Ah, but if they're all in the same column, it's pretty straightforward. For example, say your source values are in column A on Sheet 2: =SUM(Sheet2!A:A) Even if sheet2 has a column total, it's no harder than: =SUM(Sheet2!A:A)/2 and spanning multiple worksheets is just a small step from there. For example, say your source values are in column A on Sheets 2 to 6: =SUM(Sheet2:Sheet6!A:A) to get the total of sheets2 and 6, plus any whose tabs are between them! And, by extension if all the sheets a column total, it's no harder than: =SUM(Sheet2:Sheet6!A:A)/2 If your sheets have sub-totals, but you've also got text that uniquely identifies the sheet total (eg 'Total') in column A and the values in Column B, you could use something like: =SUMIF(Sheet2!A:A,"Total",Sheet2!B:B) or, for multiple sheets: =SUMIF(Sheet2!A:A,"Total",Sheet2!B:B)+SUMIF(Sheet3 !A:A,"Total",Sheet3!B:B)+S UMIF(Sheet4!A:A,"Total",Sheet4!B:B)+SUMIF(Sheet5!A :A,"Total",Sheet5!B:B)+SUM IF(Sheet6!A:A,"Total",Sheet6!B:B) If you data are organised across rows instead of down columns, just use row ranges instead. Cheers "srpettew" wrote in message ... Hi Macropod, I think I've figured out what I need to do, and there is no easy way to go about it. Yes, I have multiple worksheets with a list of purchased licenses for a specific year. Problem is, the rows are all different because different licenses were purchased each year, so the cells won't match up. I think I just need to go in the summary sheet and reference the specific cell on each sheet. A couple of them have over 400, so this should be fun! Thank you for your responses. They are appreciated. SP "macropod" wrote: Hi srpettew, OK, so you have a 'current year *workbook*' which, if I understand you correctly, has 'a running tally on a summary sheet'. I assume that's not the same *worksheet* that has the licence data, since your original post said you 'have several tabs (worksheets) representing purchases for a specific year'. In that case, a solution along the lines I proposed will work - I didn't mention anything about linking to another *workbook* - though you could do that too if there was a case for doing so. So, what exactly do you want to appear in your summary: - the sum of all cells in a specified location; or - the sum of all cells in a specified column, on all worksheets? Something else? Cheers "srpettew" wrote in message ... I have a running tally on a summary sheet. I don't want to lose those numbers. If I make the specific cell equal to another cell in another workbook, it will change my summary. All I really want to do is add the summary workbook cell with any updates made in the current year workbook. For example, if the total number of licenses for a product is 7 (summary), and I purchase 2 more licenses this year, I want the summary to now show 9. Does this make sense? Thanks, SP "macropod" wrote: Well, at it's simplest: ..Select the target cell (i.e. the one on your Summary sheet you want to have updated automatically), .. press the '=' sign .. select the source cell .. press <Enter Now the two are linked and the value on the Summary sheet should update whenever the source cell is updated. In essence, the procedure is the same as you might use with the mouse to reference another cell on the same worksheet - the only significant difference is that you're selecting another worksheet's tab along the way. Cheers "srpettew" wrote in message ... Hi macropod, I'm trying to figure out how to do this (formula linking to others). Thanks. "macropod" wrote: Hi srpettew, Assuming you summary sheet has formulae linking to the others, the data should update automatically anytime the worksheet re-calculates. Cheers "srpettew" wrote in message ... Hi, I have several tabs (worksheets) representing purchases for a specific year. There is one tab (worksheet) that is a Summary of all purchases for all years. How do I get Excel to automatically update the Summary worksheet anytime one of the other worksheets are modified? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically updating summary worksheet
Hi,
In that case you could use something like: =SUMIF(Sheet2!A:A,"Adobe Acrobat 7.0",Sheet2!B:B) The lack of an ,"Adobe Acrobat 7.0" entry on some sheets won't matter - those sheets will simply return '0'. Cheers "srpettew" wrote in message ... Well, my problem is like this. I have software titles in Column A and then columns B-M represent each month of the year. So if an Adobe Acrobat 7.0 license was purchased in March of a particular year, then column D would have a "1" for whatever row Adobe Acrobat 7.0 is on. The problem is that Adobe Acrobat 7.0 will not show up on certain worksheets because it did not exist. So the rows will not match up on the worksheets. This is why I have to treat every cell individually on the summary sheet. Thanks again, SP "macropod" wrote: Hi srpettew, Ah, but if they're all in the same column, it's pretty straightforward. For example, say your source values are in column A on Sheet 2: =SUM(Sheet2!A:A) Even if sheet2 has a column total, it's no harder than: =SUM(Sheet2!A:A)/2 and spanning multiple worksheets is just a small step from there. For example, say your source values are in column A on Sheets 2 to 6: =SUM(Sheet2:Sheet6!A:A) to get the total of sheets2 and 6, plus any whose tabs are between them! And, by extension if all the sheets a column total, it's no harder than: =SUM(Sheet2:Sheet6!A:A)/2 If your sheets have sub-totals, but you've also got text that uniquely identifies the sheet total (eg 'Total') in column A and the values in Column B, you could use something like: =SUMIF(Sheet2!A:A,"Total",Sheet2!B:B) or, for multiple sheets: =SUMIF(Sheet2!A:A,"Total",Sheet2!B:B)+SUMIF(Sheet3 !A:A,"Total",Sheet3!B:B)+S UMIF(Sheet4!A:A,"Total",Sheet4!B:B)+SUMIF(Sheet5!A :A,"Total",Sheet5!B:B)+SUM IF(Sheet6!A:A,"Total",Sheet6!B:B) If you data are organised across rows instead of down columns, just use row ranges instead. Cheers "srpettew" wrote in message ... Hi Macropod, I think I've figured out what I need to do, and there is no easy way to go about it. Yes, I have multiple worksheets with a list of purchased licenses for a specific year. Problem is, the rows are all different because different licenses were purchased each year, so the cells won't match up. I think I just need to go in the summary sheet and reference the specific cell on each sheet. A couple of them have over 400, so this should be fun! Thank you for your responses. They are appreciated. SP "macropod" wrote: Hi srpettew, OK, so you have a 'current year *workbook*' which, if I understand you correctly, has 'a running tally on a summary sheet'. I assume that's not the same *worksheet* that has the licence data, since your original post said you 'have several tabs (worksheets) representing purchases for a specific year'. In that case, a solution along the lines I proposed will work - I didn't mention anything about linking to another *workbook* - though you could do that too if there was a case for doing so. So, what exactly do you want to appear in your summary: - the sum of all cells in a specified location; or - the sum of all cells in a specified column, on all worksheets? Something else? Cheers "srpettew" wrote in message ... I have a running tally on a summary sheet. I don't want to lose those numbers. If I make the specific cell equal to another cell in another workbook, it will change my summary. All I really want to do is add the summary workbook cell with any updates made in the current year workbook. For example, if the total number of licenses for a product is 7 (summary), and I purchase 2 more licenses this year, I want the summary to now show 9. Does this make sense? Thanks, SP "macropod" wrote: Well, at it's simplest: ..Select the target cell (i.e. the one on your Summary sheet you want to have updated automatically), .. press the '=' sign .. select the source cell .. press <Enter Now the two are linked and the value on the Summary sheet should update whenever the source cell is updated. In essence, the procedure is the same as you might use with the mouse to reference another cell on the same worksheet - the only significant difference is that you're selecting another worksheet's tab along the way. Cheers "srpettew" wrote in message ... Hi macropod, I'm trying to figure out how to do this (formula linking to others). Thanks. "macropod" wrote: Hi srpettew, Assuming you summary sheet has formulae linking to the others, the data should update automatically anytime the worksheet re-calculates. Cheers "srpettew" wrote in message ... Hi, I have several tabs (worksheets) representing purchases for a specific year. There is one tab (worksheet) that is a Summary of all purchases for all years. How do I get Excel to automatically update the Summary worksheet anytime one of the other worksheets are modified? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying info entered on one worksheet automatically to another | Excel Discussion (Misc queries) | |||
automatically save some columns in another worksheet | Excel Discussion (Misc queries) | |||
Updating 1 worksheet with result from another worksheet | Excel Discussion (Misc queries) | |||
Figures not updating on my summary sheet | Excel Worksheet Functions | |||
Copying Totals of separate worksheets to a single Summary Worksheet | Excel Discussion (Misc queries) |