Discussion in 'Business Applications' started by Jo1lygrngian, Jan 1, 2006.

Not open for further replies.

Joined:
Nov 3, 2005
Messages:
87
Hi there, im getting really stuck with this excel formula, and wondering if any bright sparks out there can help an aging man lol

right here goes.. I'm working on a spreadsheet, for my accounts, on a week to week basis.

There is a final page, which will store all the calculations from wk1 to wk52. When i try and copy the values from week to week, it always adds a cell below..

example being, I want to copy page1, cell b13, page2 cell b13, page 3 cell b13, onwards.. paste it into the final page, and it comes out with

=+'wk 8'!B25, then the cell below, is =+'wk8'!B26, so on, and so on.

Is there any way at all, of making it take the initial cell and then not the one below in the calculation??

I've probably explained this all wrong, so if you need any more clues, plse dont hesitate to shout, and ill try and explain it better.

Btw, if it involves a macro, then im WELL stuck, as i dont know where to start.. and would really appreciate a step by step help on writing one.

(Bring back lotus!!) lol

thanks for your time, and any help you can give, and happy new year to one and all

Be safe

Nige

2. bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
I don't get "it always adds a cell below". If you mean "the row number increments", you could try using an absolute reference in your formula to anchor it, such as:

='wk 1'!\$B\$13

However, that won't make the sheet number increment.

You can use a formula on the last sheet to sum (across) all the other sheets, such as:

=SUM('wk 1:wk 52'!B13)

, that might come in useful.

Any chance you could upload a sample file to give a clearer idea?

Rgds,
Andy

3. seamanken

Joined:
Dec 30, 2005
Messages:
9
Here's the quickest way to do that goto the summary page you have setup click the cell were your totals will be. got to sheet 1 hold down the shift key and click the cell you want it will add it for you the goto the next sheet so on and so on. remember though you can only have 30 0f these per cell so I break mine down into quaters and have the quaters added on the same summary page

4. seamanken

Joined:
Dec 30, 2005
Messages:
9
I am not sure sending you a file would help it has to be done on the book your working on

Joined:
Nov 3, 2005
Messages:
87
Hi guys, and thanks for getting back to me, i really do appreciate it.

thanks so much for getting back to me on the thread. I have uploaded the ss for you, so that you can see exactly what i mean by 'it always adds the cell below'.

A good example being, on page Gross takings summ, I want it to take the total of wk1 b13, and place it in cell b4, then take the total of wk2 b13, put that in b5, etc. I have tried using the =SUM('wk 1:wk 52'!B13) formula, and it comes up with #ref. Using the 'shift and click, sorry to say has made little difference.

I would really appreciate any help you can give here, as it is really starting to do my head in!!

Hope you have a grt day

be safe nige

File size:
39 KB
Views:
71
6. bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
So it's just a summary? For starters, try this in B2 of Sheet2 and copy it down:

=INDIRECT("wk"&\$A2&"!B13")

Joined:
Nov 3, 2005
Messages:
87
Andy.. THANK YOU!!!

It works in the sml sheet a treat. Ive copied it into the main sheet, and it works from week 16-52, but for some reason not wks1-15 Ill have a play around, to see what the cause of this is.

Long ago are the days when i used SS's on a reg basis, ahem, lotus123 anyone?? lol

I simply cannot thank you enough.

Hope you have a grt day

thanks once again nige

8. bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
You got the REF error before because (a) your sheet names don't actually have spaces in (b) there was no wk52 sheet.

I still don't fully understand what you're trying to get from where -- e.g. there's no actual "page Gross takings". But by all means tinker with it & post back if you get stuck, or can provide more info. There are always people here able to help with Excel stuff.

I think I had Lotus once, got it free on a cover disk ... says it all, probably.

Joined:
Nov 3, 2005
Messages:
87
Thanks for the input Andy. It is now working perfectly

As Seen On