adding across worksheets

[Glenn]

I have a workbook that has several worksheets in it.

The first sheet is basically a membership roster. It contains names, social
security numbers, membership category, how much their total dues are, and
how many paychecks their dues will be deducted out of (most are 16, but it
can vary).

The other 16 pages in the workbook are for each payroll period. They list
names, social security numbers, and how much was deducted during that pay
period.

Sometimes members are added or deleted during the year.

I'd like to add a column in the first worksheet that shows how much they've
paid to date in dues. This would be calculated by searching through the
other 16 worksheets to see if their social security number appears, and if
it does, how much they paid during that period. (For the majority of people
they will be on all 16 sheets, however if a new person comes along during
the 5th pay period, they won't be on the first 4 sheets, and if someone
leaves during the 10th pay period, they won't be on the remaining six
sheets).

Is it possible to do this? If so could someone tell me how?

Thanks,
Glenn

 

[Frank Kabel]

Hi
as you did not provide so much detail about the specific layout of your
file below some general solutions:
1. Harlan Grove showed a formula approach for a conditional sum accross
multiple worksheets. Have a look at this thread
http://tinyurl.com/2manj

2. You may also try to download the free add-in Morefunc.xll
(http://longre.free.fr/english). The function THREED converts a 3D
array to a 2D array. e.g. you may use the following formula on sheet X
=SUM((THREED('sheet1:sheet3'!A1:A1000)="Person1")*(THREED('sheet1:sheet
3'!B1

B1000)))

enter this as array formula (CTRL+SHIFT+ENTER). This will sum all
values from column B in which column A contains our criteria. You can
replace the hardcode "person1" with a cell reference on sheet x.
Something
like the following formula in B1 on sheetx:
=SUM((THREED('sheet1:sheet3'!$A$1:$A$1000)=A1)*(THREED('sheet1:sheet3'!
$B$1:$B$1000)))
and copy down

 

[Debra Dalgleish]

Instead of storing the data on sixteen separate sheets, on the first
sheet, you could add a column for each payroll period, with the date as
the heading. Add a final column, in which you total the payments for
each member. Total each column to calculate the amount collected during
each payroll period.

You could hide these columns if you need to print the membership roster.

 

[Don Guillett]

try this where numbers are on the Totals worksheet.

Sub SumAllWorksheets()
For Each c In Selection
mt = 0
For Each ws In Worksheets
If ws.Name <> "Total" Then
On Error Resume Next
Set x = ws.Columns(1).Find(What:=c, LookAt:=xlWhole)
If Not x Is Nothing Then mt = mt + x.Offset(, 1)
End If
Next ws
c.Offset(, 1) = mt
Next c
End Sub

 

[Glenn]

I think that will work, but I can't figure out how to get the list of
worksheets made, using WSLST.

 

[Frank Kabel]

Hi
- put the names of your worksheets in a range of cells. e.g. A1:A10
- select this range
- goto 'Insert - Name - Define' and assign the name 'WSLST' for this
range

 

[Glenn]

Excellent, that worked. Now I'm stumped on something else.

Say Worksheet 1 / Column A contains a list of last names, and Column B
contains a list of first names.

In the remaining worksheets, Columns A and B also contain a list of last and
first names. Along with the amounts, in column C, that I'm looking to get
totals for.

Worksheet 1 contains a list of the people who could be on any of the
remaining worksheets. But, Worksheet 3 for example might not have all the
names that are listed on Worksheet 1.

So, I need to compare the names that are on Worksheet 1 to the names on
Worksheet 2 (3, 4, etc.) before taking the amount from Column C to add.

For Instance:

Worksheet 1
Column A Column B Column C
Smith John 150 (Column C would be added up
from the Smith John's on the other worksheets in wslst)
Doe Jane $130
Jones Loretta $40
Mugg Wilbur $40

Worksheet 2
Smith John $50.00
Doe Jane $50.00
Jones Loretta $40.00

Workseheet 3
Smith John $50.00
Doe Jane $40.00

Worksheet 4
Smith John $50.00
Doe Jane $40.00
Mugg Wilbur $40.00

 

[Glenn]

Should I post this question in another excel group?