Can's work this out, probably easy for an expert!

[Guest]

I am a new user of excel, self taught and am not finding it easy!
I am making up a simple spreadsheet for members of a club. Each week they
pay different amounts of money, which is broken down into 3 categories. My
workbook is made up of worksheets for each member (approx 60 people/'tabs').
I have columns as follows

A B C D E
F G
Date Balance b/f Total due Admission Tea Fund Holiday Fund
Balance c/f
1 Jan 10.00 4.00 1.00 1.00
2.00 10.00
7 Jan
14 Jan
etc

Cols D, E & F total to Col C
Col G = B+C-D-E-F
There will be 52 columns, one for each week of the year.

I will bank the money collected every week, into 3 separate accounts. I
therefore want to do a 'summary sheet' as follows

A B C D E
F G

Name B/f Total Admission Tea
Holiday C/f
J Smith
D Brown
etc

Amounts banked x x x
x

How can I easily transfer the figures in colums B-G of members' sheets to
the summary sheet? I could do it if the rows were static, but as they will
move forward by one row each week, I am stuck, as I am really new at this.
Thanks in advance for any help anybody can give me.

 

[Guest]

I think what you are saying is that you want your summary sheet to show each
person's info for the current week only. So what you need to do is tell that
summary sheet what week you want to report, then it will know to pull each
person's data for that week.
First, off to the right somewhere (maybe column J, whatever is off of your
report's print area, I would suggest putting each person's data range in this
format (assuming J Smith's sheet name is J_Smith):
J_Smith!$A$2:$G$53
You can use the fill handle to copy the formula down, then just change the
part in front of the exclamation.

Next, I'd put a place at the top for what week you want to report. Just
make a place, say in A1, for the date (1 Jan, 7 Jan, whatever).

Now, for your formulas, just use something like this:
B2 =VLOOKUP($A$1,INDIRECT($J2),2,FALSE)
copy this formula across to G, then just change the 2 to 3 for C, 4 for D, 5
for E, etc. So when you're done,
C2 =VLOOKUP($A$1,INDIRECT($J2),3,FALSE)
....
G2 =VLOOKUP($A$1,INDIRECT($J2),7,FALSE)
Select B2:G2 and use the fill handle to drag down as far as needed.

I must comment on your design for a moment. It seems a bit odd that you
have C as a total of D:F and then G is B+C-D-E-F (or, put simply, B+C-C). I
think you are missing a column just before G for "Paid In". Then H would be
the carryforward balance and would =B-C+G...thus their true balance after
paying in is what is carried forward.

Excel is definitely worth learning to do well, as it is extremely functional
and a huge time saver when used efficiently and to its full potential. It
can automate many, many things for you. Good luck!
-KC

 

[Guest]

Thank you for taking the trouble to reply. I have tried your suggestions and
they work, so what a lot of time I will be saving! I take your point on the
layout of the spreadsheet, I think some more tweaking will be required until
I get it right. I love excel, but it's a struggle sometimes...
Thanks again.

 

[Guest]

Glad I could help. Don't get discouraged!
I think if you insert a column before G on every page as I suggested, then
your spreadsheet will be in great shape. You can actually highlight all the
worksheet tabs and insert the column and formulas one time...when you group
sheets like that, the change you make on the sheet you see applies to all
sheets you've highlighted.
Good luck!

 

[Guest]

Thank you once again. I have tried inserting the column the way you
suggested - another wee trick I have learned this week!
Slowly but surely.... I will get there. No doubt I will be back soon!