Linking more than one cell with multiple cells in the same workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an expenses list with itemised expenses e.g. house, car, medical, groceries. Each cell totals for the month on the one sheet - e.g. April Expenses. The second sheet shows all the totals for all the months for all the expenses e.g.
Row A - house, car, medical, groceries. Columns across top show each month - Jan, Feb. March, April. I have to do a copy and paste for every expenses for every month. There has to be an easier way??!!Please tell me there is!!
 
Hi
you may have a look at pivot tables for this
-----Original Message-----
I have an expenses list with itemised expenses e.g.
Click to expand...
house, car, medical, groceries. Each cell totals for the
month on the one sheet - e.g. April Expenses. The second
sheet shows all the totals for all the months for all the
expenses e.g.
Row A - house, car, medical, groceries. Columns across
Click to expand...
top show each month - Jan, Feb. March, April. I have to
do a copy and paste for every expenses for every month.
There has to be an easier way??!!Please tell me there is!!
 
Thanks Frank for your suggestion, but it doesn't link the cells in the two different worksheets the way it needs to. Any other suggestions?? Anyone??
 
Beth, I'm not quite sure I understand how you have work workbook set up. Do
you have a separate worksheet for each month, with a summary sheet for all
the months? On your summary sheet, you say the columns represent the months,
do you have a row for each expense?

In any case, if your worksheets are properly set up, linking cells is no
problem. You can even set up your worksheet for the whole year in advance.
Say your January rent is on the January sheet, in Cell B2. If you want to
link that to cell B2 on your summary sheet, go to your summary sheet, click
Cell B2, type =January!B2 and press Enter. Formula for C2 would be
=February!B2 (assuming rent figure is in B2 on that sheet), and so on. If
your other expenses are in the rows below, you can just copy the formulas
down.

You can also create links by pointing and clicking. On your summary sheet,
for example, you can click Cell B2, type =, switch to January, click B2, and
press Enter to create a link. Again, you can copy this formula down (row
will increment) or to the right (column will increment).

If you want to sum a range of cells where that range spans worksheets, you
can enter a formula like this one: =SUM(January:December!A1).

Hope this helps.

--
DDM
"DDM's Microsoft Office Tips and Tricks"
Visit us at www.ddmcomputing.com


Beth said:
Thanks Frank for your suggestion, but it doesn't link the cells in the two
Click to expand...
different worksheets the way it needs to. Any other suggestions?? Anyone??
 
Hi
you explain with some more detail:
- what does not work
- how your expected result should look like
you may post some sample data (plain text - no attachment please)

--
Regards
Frank Kabel
Frankfurt, Germany

Beth said:
Thanks Frank for your suggestion, but it doesn't link the cells in
Click to expand...
the two different worksheets the way it needs to. Any other
suggestions?? Anyone??
 
I think it ALMOST works. Sample: May Expenses sheet:
House Groceries Car Maintenanc
28.00 15.00 68.00 (running totals
roofing 12.0
Water rates 16.0
Meat & Veges 15.0
Car Rego 25.0
Tyres 43.0

Totals Sheet
April May June Total for Fin Yr.
House 15.00 28.00 0.00 (updates automatically
Groceries 40.00 15.00 0.0
Car Maint. 30.00 68.00 0.0

I can't get the Pivot Wizard to let me put in the Months. I get the House, Car etc. as Data but can't get the months to show across the top. What am I doing wrong. Thanks for your help. Youre a wiz.
 
Hi
there're the dates in your source data range (didn't see any dates in
your example)
 
Your message: there're the dates in your source data range (didn't see any dates i
your example) I am not sure what you mean here. The only ref. to dates is in the names of the months at the top. I have worked out to do the x axis in one action and the y axis in the other, but I get a whole heap of extra boxes all over the place instead of just the labels. I will have one more try (and one more go at pestering you) then I think I will give up as defeated. Thanks for your help, but I just must be thick or something
 
Beth, if I understand your original post and the one below, what you are
trying to do is reproduce, for example, the 28.00 house expense from the May
Expenses sheet in your Totals Sheet without, as you stated in your original
post, copying and pasting it, as well as the other figures, month by month.
Is this so?

If it is, then assuming the 28.00 is in Cell B2 on the May Expenses sheet,
this is what you would enter on the Totals Sheet (put it where the 28.00 on
the Totals Sheet is now): ='May Expenses'!B2.
For Groceries: ='May Expenses'!C2.
For Car Maint.: ='May Expenses'!D2.

For June, the formulas would go something like this:
House ='June Expenses'!B2
Groceries ='June Expenses'!C2
Car Maint ='June Expenses'!D2

Set up the formulas on the Totals Sheet in advance, and all you'll have to
do is enter the numbers on the Expenses sheets. They'll automatically appear
on the Totals Sheet. That should get you the result you're looking for.

IMHO, a pivot table is not really what you need. In any case, as you've
seen, you can't create a proper pivot table the way your data is arranged on
your Totals Sheet. If you really want to do a pivot table, you'll have to
set up your Totals Sheet like this:

April | House | 15
April | Groceries | 40
April | Car Maint | 30
May | House | 28
May | Groceries | 15
May | Car Maint | 68

....and so on. Then you can create a pivot table with Line Items (House,
etc.) as row headings, Months as column headings, and your numbers in the
data area, summed.

Hope this helps.

--
DDM
"DDM's Microsoft Office Tips and Tricks"
Visit us at www.ddmcomputing.com


Beth said:
I think it ALMOST works. Sample: May Expenses sheet:
House Groceries Car Maintenance
28.00 15.00 68.00 (running totals)
roofing 12.00
Water rates 16.00
Meat & Veges 15.00
Car Rego 25.00
Tyres 43.00

Totals Sheet:
April May June Total for Fin Yr.
House 15.00 28.00 0.00 (updates automatically)
Groceries 40.00 15.00 0.00
Car Maint. 30.00 68.00 0.00

I can't get the Pivot Wizard to let me put in the Months. I get the House,
Click to expand...
Car etc. as Data but can't get the months to show across the top. What am I
doing wrong. Thanks for your help. Youre a wiz.
 
Hi
o.k. to explain it with more detail :-)
- in your total sheet you're showing month names as the top row
- you're trying to split your values for the months
- your first sheets seems to be the source sheet from which you get the
data which is totalled on sheet 2

Now my questions was related to the fact: How do you know which part of
sheet1 has to go in which month. As your source sheet does not contain
any date information how should Excel (or even how do you) know which
amount is created in which month?
 
As you wrote Frank: "Now my questions was related to the fact: How do you know which part o
sheet1 has to go in which month." ( The whole sheet1 is May Expenses. All the expenses shown on that sheet are incurred in May. Sheet 2 is June Expenses and so on. The total Sheet is a separate sheet which brings together each month's totals for each expense.
DDM what you are saying is what I (think) I am trying to do. I used the copy - paste-paste options routine but this seemed so tedious for every month for every expense. I will give your suggestion a try tomorrow (it is very late here in Australia now!!!) and see how I go.
I really appreciate both your comments and am happy to give anything a go. I'll check your replies on MOnday. Sunday is a "no-computer day" around here!

Thanks again, Bet
 
G'day DDM, I tried what you suggested and it does work, however, I still hve to enter May Expenses B2 etc 18times - for each category, and changing the reference from B2 to C2 to D2 for each one. Is this what is going to be required? I tried copy - paste special- transpose, but it messed up and doesn't move things correctly. Perhaps as Frank says I need to use the Pivot table, but as you suggest, I need to have the whole thing set up differently to achieve what I want here? Break the news to me gently eh!

Any more help would be appreciated - if you don't mind this continuing saga
 
G'day, Beth! Given that your expense categories are in rows on your monthly
sheets and in columns on your totals sheet, you will, AFAIK, have to enter
the formulas manually (as you describe below), since it isn't possible to
transpose and link at the same time. Oh well. But look at it this way: You
can set aside half an hour or so and set up the rest of the year. Then
forget about it. Next year, maybe you can set up your expense sheets (or
your totals sheet) differently.

Hope this helps.

--
DDM
"DDM's Microsoft Office Tips and Tricks"
Visit us at www.ddmcomputing.com


Beth said:
G'day DDM, I tried what you suggested and it does work, however, I still
Click to expand...
hve to enter May Expenses B2 etc 18times - for each category, and changing
the reference from B2 to C2 to D2 for each one. Is this what is going to be
required? I tried copy - paste special- transpose, but it messed up and
doesn't move things correctly. Perhaps as Frank says I need to use the
Pivot table, but as you suggest, I need to have the whole thing set up
differently to achieve what I want here? Break the news to me gently eh!!
 
You could use the Indirect function to return the sheet names. For
example, with your summary sheet month names in cells B1:D1, and
categories in column A:


=SUMPRODUCT((INDIRECT("'"&B1&"'!$B$3:$D$22"))*(INDIRECT("'"&B1&"'!$B$1:$D$1")=Summary!$A2))
 
Wow!!!!! I did it. DDM - I will set up the next financial year in the way suggested. I tried it out and it actually works. What a wonderful discovery you have led me to!!!
Everyone else - Frank and Debra, thank you for your help. I have actually learnt so much from the exchange that I would never have even thought possible. I appreciate all of your patience, and especially DDM for giving me the sane solution to my problem

Look out financial year 2004-2005!!!!!Here I come with bells on

Cheers and thanks, Beth
 
Back
Top