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

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

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

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

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

Help with changing formula
 

glad it helped


--
Don Guillett
SalesAid Software

"Brad K." wrote in message
...
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