I want to copy a formula across 52 worksheets that relates to the same cell in each sheet

Hi, hope someone can help.

I have a master worksheet plus 52 identical worksheets (one for each week). On the master worksheet in cell A1 I have a formula that relates to Sheet2A1. In Cell B1 I have a formula that relates to Sheet3A1 etc all the way through to Sheet52 A1. I could manually input all these but is there a simple way of dragging?/ copying the formula across the master worksheet.

Thanks for your response Jeff, I have failed, not for the first time...probably cos I haven't explained it well enough.

The actual cell reference in sheets 1-52 is G17, what should the above formula look like on the master sheet using your above example. The sheets are named 1,2,3,4 etc up to 52

So cell A1 will refer to Sheet1, rather than Sheet2 as in the original example. Also, the cell reference will be G17 rather than A1. So the formula changes to

Formula:

Please Login or Register  to view this content.

INDIRECT is a function that creates a range reference based on a string given as an argument. COLUMN returns the column number of the current cell. So when this formula is in A1, it will evaluate as

Formula:

Please Login or Register  to view this content.

which will be equivalent to using

Formula:

Please Login or Register  to view this content.

Hopefully that helps explain how this works and what I changed based on your latest update.

6String's got it pretty much right.
Except...

Originally Posted by bbonzo

The sheets are named 1,2,3,4 etc up to 52

This =INDIRECT("Sheet"&COLUMN()&"!G17") will be referring to Sheet1 Sheet2 Sheet3
But I suppose you actually want just 1 2 3

Should be
=INDIRECT(COLUMN()&"!G17")

Jonmo, do I need to put a cell reference in the brackets following COLUMN? The formula works for the first sheet, as it would, but doesn't work when I drag the formula across on the master sheet, it stays as reference to Sheet 1 rather than Sheets 2, 3,4 etc.

Thanks for all your responses, I am sure you could all solve this query quickly if you sat at my desk!!

As Column() is dragged right, it's returning the # corresponding to the column the formula is entered in.

So in A1, Column() = 1
Dragged right to B1, Column() = 2
to C1, Column()=3
Etc..

but Xor is right, it's best to include an actual reference.
And I honestly prefer columnS over column..

Try
=INDIRECT(COLUMNS($A:A)&"!G17")

It works! Many thanks to all who replied. Relief.

You're welcome.

I overlooked the sheet names being just numbers, sorry about that.

Just for the sake of closure I agree in principle with the points made by Jonmo1 and XOR LX. The rationale is that using cells references and COLUMNS you avoid a dependency on specifically which columns the formulas fall in. In this case I took a bit of a shortcut, taking advantage of the fact that the first item fell in column A. That solution is simple but not very portable or robust against changes.