transferring Sheet 1 to Sheet 2

[Guest]

Greetings all!

My name is Audra and I'm an executive assistant for a dance company and I
have a query.

I'll just jump right in.

We want to create Sheet 1 as an itemized sheet, something as simple as
listing 10 students' names, how much they paid for their one Salsa class and
the sum followed by a second list of 10 students' names, how much they paid
for their Tango series class followed by its sum.

Sheet 2 needs to be just the sum of the Salsa class and the sum of the Tango
classes.

Is there a way this can be done automatically, transferring sheet 1 to sheet
2?

Any help would be greatly appreciated ASAP.

Many thanks.

 

[Guest]

A relatively painless way to do this would be to use a VLOOKUP() formula
along with unique entries for the total rows for the classes on the first
sheet. Since I would think that overtime you might have many Salsa, Tango,
Waltz, etc classes, you'd want to identify them uniquely. For this example
we will pretend you identify them uniquely by their starting date, so on the
first sheet, in column A you would have 10 student names followed by:
Salsa 010107 Total
in column A with the total in B. Later on you might have 'Tango 010807 Total'

On the second sheet, where you want the totals to appear, you could have a
formula like this (where Sheet2 is the name of the sheet the class lists are
on)
=VLOOKUP("Salsa 010107 Total",Sheet2!A:B,2,0)
or
=VLOOKUP("Tango 010807 Total",Sheet2!A:B,2,0)

A second solution that may take some experimenting with the math to get it
right because I suspect there are label rows (NAME AMT PD) involved and
perhaps blank rows between class groups, are actually involved. It depends
on a consistent layout of the first/class list sheet.

But if you have 10 names starting at row 1, the sum of amounts paid for that
class in row 11, column B followed immediately by another group of 10 names
(rows 12-21) and its sum in B22 then this formula will work if placed into a
cell on row 1 of the 2nd sheet and filled on down that sheet:

=OFFSET(Sheet2!A$1,(ROW()-1)*10+ROW()+9,1)

I'll try to explain: the Row()-1 gets the row number the formula is in and
subtracts 1 from it and multiplies that result by 10 (the size of the class)
and then adds that result to the current row number + 9 (size of class minus
1) and because it's working from column A and amounts paid are in column B on
first sheet, it offsets over to 1 column and gets the value there.

If you do the math for that row offset, the formula in a cell in row 1 is
the same as
=OFFSET(Sheet2!A$1,(1-1)*10+1+9,1)
which simplifies to
=OFFSET(Sheet2!A$1,(0)*10+1+9,1)
and finally to
=OFFSET(Sheet2!A$1,10,1)
and because that's an offset, it points to B11 on Sheet2

When it's moved into a cell on row 2 of the second sheet it works out like
this:
=OFFSET(Sheet2!A$1,(2-1)*10+2+9,1)
which simplifies to
=OFFSET(Sheet2!A$1,(1)*10+2+9,1)
and finally to
=OFFSET(Sheet2!A$1,21,1)
and because that's an offset, it points to B22 on Sheet2

 

[Guest]

Audra,
Rather than complicate that first post any worse than it is, I'm going to
add this one that gives a general formula to help you if your class groups
are not "back to back" without any non student entries in between them (and
above the first group). You can take the second formula above and 'pretent'
or include any extra rows between groups as students! Let's say you have
this setup:

Row
1 NAME Amt Pd
2 TANGO Class 1
3 student 1 $1.95
....
12 student 10 $1.95
13 Total Pd $19.50
14 ---- an empty row ----
15 SALSA Class 3
16 student 1 $2.25
....
25 student 10 $2.25
26 Total pd $22.50
and this pattern repeats: 2 rows, 10 students, 1 sum, on down the sheet.

that second formula adapts very well, there are 13 rows involved:
=OFFSET(Sheet2!A$1,(ROW()-2)*13+(ROW()-1)+12,1)
placed into a cell on ROW 2 of the second sheet and then filled down will
bring over all of the Total Pd entries from the first sheet out of column B.