# Solved: Excel query please :)

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### Jo1lygrngian

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

#### bomb #21

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

#### seamanken

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

#### seamanken

I am not sure sending you a file would help it has to be done on the book your working on

#### Jo1lygrngian

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

#### Attachments

• 39 KB Views: 71

#### bomb #21

So it's just a summary? For starters, try this in B2 of Sheet2 and copy it down:

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

#### Jo1lygrngian

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

#### bomb #21

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.

#### Jo1lygrngian

Thanks for the input Andy. It is now working perfectly

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On