Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with changing formula
I have a workbook that will be used by several others. I need to find a way
to get totals from each sheet and add them together on the "Overview" sheet. For example, on Overview sheet Cell C5 might have ='Account1!AF100+'Account2'!AF100+'Account3'!AF100 +'Account4'!AF100 The issue I am having is that each user of this sheet will have a different number of "Account?" sheets and there will be no naming consistancy as they would put the actual account name for the sheet name. Is there a way to simply do this or else what is the best programatically way to accomplish this? Thanks, BradK |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with changing formula
try
=sum(sheetfirst:sheetlast!af100) where they fill in the name of the 1st and last sheet -- Don Guillett SalesAid Software "Brad K." wrote in message ... I have a workbook that will be used by several others. I need to find a way to get totals from each sheet and add them together on the "Overview" sheet. For example, on Overview sheet Cell C5 might have ='Account1!AF100+'Account2'!AF100+'Account3'!AF100 +'Account4'!AF100 The issue I am having is that each user of this sheet will have a different number of "Account?" sheets and there will be no naming consistancy as they would put the actual account name for the sheet name. Is there a way to simply do this or else what is the best programatically way to accomplish this? Thanks, BradK |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with changing formula
a macro
Sub getsum1() Range("A8").Formula = _ "=SUM('" & Worksheets(2).Name & ":" & _ Worksheets(Worksheets.Count - 1).Name & "'!A8)" End Sub -- Don Guillett SalesAid Software "Brad K." wrote in message ... I have a workbook that will be used by several others. I need to find a way to get totals from each sheet and add them together on the "Overview" sheet. For example, on Overview sheet Cell C5 might have ='Account1!AF100+'Account2'!AF100+'Account3'!AF100 +'Account4'!AF100 The issue I am having is that each user of this sheet will have a different number of "Account?" sheets and there will be no naming consistancy as they would put the actual account name for the sheet name. Is there a way to simply do this or else what is the best programatically way to accomplish this? Thanks, BradK |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with changing formula
Hi,
I suggest you not use the "name" of the avccount, but the account numer, then a solution would be to use a vlookup, something like this =VLOOKUP(A2,Sheet2!A2:B4,2,FALSE)+VLOOKUP(A2,Sheet 3!A2:B4,2,FALSE)+VLOOKUP(A2,Sheet4!A2:B4,2,FALSE) Where the lookup value is in "A1" sheet1. If you make the formula absolute, you can copy it down. Then the formula looks like this =VLOOKUP(A2,Sheet2!$A$2:$B$4,2,FALSE)+VLOOKUP(A2,S heet3!$A$2:$B$4,2,FALSE)+VLOOKUP(A2,Sheet4!$A$2:$B $4,2,FALSE) Thanks, -- David "Brad K." wrote: I have a workbook that will be used by several others. I need to find a way to get totals from each sheet and add them together on the "Overview" sheet. For example, on Overview sheet Cell C5 might have ='Account1!AF100+'Account2'!AF100+'Account3'!AF100 +'Account4'!AF100 The issue I am having is that each user of this sheet will have a different number of "Account?" sheets and there will be no naming consistancy as they would put the actual account name for the sheet name. Is there a way to simply do this or else what is the best programatically way to accomplish this? Thanks, BradK |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with changing formula
Thanks Don!!! This was exactly what I needed.
BradK "Don Guillett" wrote: a macro Sub getsum1() Range("A8").Formula = _ "=SUM('" & Worksheets(2).Name & ":" & _ Worksheets(Worksheets.Count - 1).Name & "'!A8)" End Sub -- Don Guillett SalesAid Software "Brad K." wrote in message ... I have a workbook that will be used by several others. I need to find a way to get totals from each sheet and add them together on the "Overview" sheet. For example, on Overview sheet Cell C5 might have ='Account1!AF100+'Account2'!AF100+'Account3'!AF100 +'Account4'!AF100 The issue I am having is that each user of this sheet will have a different number of "Account?" sheets and there will be no naming consistancy as they would put the actual account name for the sheet name. Is there a way to simply do this or else what is the best programatically way to accomplish this? Thanks, BradK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing the range for averages with out changing the formula. | Excel Worksheet Functions | |||
changing formula | Excel Worksheet Functions | |||
how to edit formula without changing formula of each cell | Excel Worksheet Functions | |||
Changing a Formula | Excel Worksheet Functions | |||
Copy Formula Down Without Changing Entire Formula | Excel Discussion (Misc queries) |