Adding a series of tables
 

Hello, all. I am an experienced programmer but a rookie Excel user.
I wonder if someone can help me with a simple problem?

I am the scorekeeper for my son's Little League team and I want
to keep track of the team's statistics. I have two tables per game,
showing the hitting and pitching statistics in the usual way, e.g.,

AB R H RBI
Fred 5 2 3 2

and so on. I have my spreadsheet set up with each game in
a separate worksheet.

What I want is a "season total" table that sums up the statistics
for all of the games. Since the players' names will appear in
different orders from game to game, my first shot at this was
to use a series of VLOOKUPs, e.g.,

=VLOOKUP($A1, Batting1, COLUMN(), FALSE)+VLOOKUP($A1, Batting2,
COLUMN(), FALSE)

where Batting1 and Batting2 are the tables of batting information
for games 1 and 2. Since not every player appears in every game,
this actually has to be =IF(ISERROR(VLOOKUP(...)), 0, VLOOKUP(...))
+...
in case a name is missing from one of the tables.

Copying this formula across my "season total" table gives me
the right answer, but as the number of games increases, the
formula gets increasingly long and clumsy, so I am looking for
a better way. What I want in effect is a loop:

total = 0
for each game {
if (player appears in table) {
total += statistic for this game
}
}

I could do this easily enough with a VB function, but I wanted to
avoid
that, since I am afraid that it will be a nuisance when I send the
spreadsheet
around to other people.

I thought that I could do something with an array expression, but I
couldn't
get it to work. I tried

={SUM(VLOOKUP(...,INDIRECT(List_of_tables),...))

where List_of_tables is a region containing the names of
the individual tables (Batting1, Batting2, ..), but it didn't give
me the right answer.

How would an experienced Excel user solve this problem?

Adding a series of tables
 

If you Goggle with "Excel consolidation" you will see this is a big topic.
There are may bits of free advice but lots of commercial products suggesting
"it's aint" easy!
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

wrote in message
...
Hello, all. I am an experienced programmer but a rookie Excel user.
I wonder if someone can help me with a simple problem?

I am the scorekeeper for my son's Little League team and I want
to keep track of the team's statistics. I have two tables per game,
showing the hitting and pitching statistics in the usual way, e.g.,

AB R H RBI
Fred 5 2 3 2

and so on. I have my spreadsheet set up with each game in
a separate worksheet.

What I want is a "season total" table that sums up the statistics
for all of the games. Since the players' names will appear in
different orders from game to game, my first shot at this was
to use a series of VLOOKUPs, e.g.,

=VLOOKUP($A1, Batting1, COLUMN(), FALSE)+VLOOKUP($A1, Batting2,
COLUMN(), FALSE)

where Batting1 and Batting2 are the tables of batting information
for games 1 and 2. Since not every player appears in every game,
this actually has to be =IF(ISERROR(VLOOKUP(...)), 0, VLOOKUP(...))
+...
in case a name is missing from one of the tables.

Copying this formula across my "season total" table gives me
the right answer, but as the number of games increases, the
formula gets increasingly long and clumsy, so I am looking for
a better way. What I want in effect is a loop:

total = 0
for each game {
if (player appears in table) {
total += statistic for this game
}
}

I could do this easily enough with a VB function, but I wanted to
avoid
that, since I am afraid that it will be a nuisance when I send the
spreadsheet
around to other people.

I thought that I could do something with an array expression, but I
couldn't
get it to work. I tried

={SUM(VLOOKUP(...,INDIRECT(List_of_tables),...))

where List_of_tables is a region containing the names of
the individual tables (Batting1, Batting2, ..), but it didn't give
me the right answer.

How would an experienced Excel user solve this problem?

Adding a series of tables
 

On Mar 19, 3:09 am, wrote:
Hello, all. I am an experienced programmer but a rookie Excel user.
I wonder if someone can help me with a simple problem?

I am the scorekeeper for my son's Little League team and I want
to keep track of the team's statistics. I have two tables per game,
showing the hitting and pitching statistics in the usual way, e.g.,

AB R H RBI
Fred 5 2 3 2

and so on. I have my spreadsheet set up with each game in
a separate worksheet.

What I want is a "season total" table that sums up the statistics
for all of the games. Since the players' names will appear in
different orders from game to game, my first shot at this was
to use a series of VLOOKUPs, e.g.,

=VLOOKUP($A1, Batting1, COLUMN(), FALSE)+VLOOKUP($A1, Batting2,
COLUMN(), FALSE)

where Batting1 and Batting2 are the tables of batting information
for games 1 and 2. Since not every player appears in every game,
this actually has to be =IF(ISERROR(VLOOKUP(...)), 0, VLOOKUP(...))
+...
in case a name is missing from one of the tables.

Copying this formula across my "season total" table gives me
the right answer, but as the number of games increases, the
formula gets increasingly long and clumsy, so I am looking for
a better way. What I want in effect is a loop:

total = 0
for each game {
if (player appears in table) {
total += statistic for this game
}
}

I could do this easily enough with a VB function, but I wanted to
avoid
that, since I am afraid that it will be a nuisance when I send the
spreadsheet
around to other people.

I thought that I could do something with an array expression, but I
couldn't
get it to work. I tried

={SUM(VLOOKUP(...,INDIRECT(List_of_tables),...))

where List_of_tables is a region containing the names of
the individual tables (Batting1, Batting2, ..), but it didn't give
me the right answer.

How would an experienced Excel user solve this problem?

There're plenty of spreadsheet templates around for this. check out
http://www.michaelwray.co.nz/sheets.html

Cheers
Westie
---
Excel training courses & Tutorials
http://www.tayop.net.au/ExcelTrainin...9/Default.aspx

Adding a series of tables
 

On Mar 18, 7:04 pm, wrote:

There're plenty of spreadsheet templates around for this. check outhttp://www.michaelwray.co.nz/sheets.html

Cheers
Westie

Thanks for the pointer. Michael Wray's spreadsheets look very nice,
but they are not
really what I wanted. They consist mainly of password-protected
Visual Basic, while I
was looking (for my own education as much as anything) for a "native"
Excel solution.
At this point, I think that I may throw in the towel and use something
like PythonOffice
to do the calculations externally and just use Excel to hold the data.

Best regards,
Zeb