Excel Formula help

BrianJohns

I am trying to copy a formula from Wk 1 to Wk 2 on an excel workbook it generates the following formula
=('Wk 1'!K53) I have 52 sheets (ie weeks) and it copies Wk1 in all of them! I need a formula that when copied will progress by one each time ie Wk 2 Wk 3 etc so each sheet gets its info from the previous sheet I've tried everything, googled it, ask friends. I'm stuck can anyone help? Thanks.

rmg1

The short answer is......you can't (to my knowledge anyway).
The sheet number is a fixed and can't be changed in the way you want to do it.
What you can do, is copy the formula anyway and then do a find-and-replace on each sheet to alter it.

Le_Kirk

If you are experienced, even a little bit, in visual basic, you could write a short routine to do it for you. Something like: -
Dim a
For a = 2 to 52 (You don't need to do it on Worksheet 1 as it is already there)
Cell (K53) Select.Copy
Worksheet a.Select
Cell (K53).Select.Paste
Next a

That's not perfect but it should be a start (if I have interpreted what you want correctly) but to be honest, unless you are doing this multiple times, it will be quicker to do as post #2 suggested.

BrianJohns

I've tried the find and replace you suggested but it doesn't find formulas unless I'm doing it wrong

andybirch

There is a option to include formulas or just contents when you find and replace. I thought it always looked at formulas first by default though as I often change formulas by find & replace.

BrianJohns

Got "Find and Replace" to work now thanks to all

rmg1

Just realised there is a fairly complicated method using INDIRECT if you wanted to go down that route.
You would need to store the sheet name (just the numeric part) in a cell and then you could use something like:-
=indirect("'Wk "&A1&"'!K53)

The above assumes you have the week number in cell A1. I'm not entirely sure the syntax is correct but it should give you a good start.

cookie365

Use VBA

http://excel.tips.net/T003088_Relative_Worksheet_References.html