Copy a table to multiple sheets and advance the formulae

[benjaminjrp]

I have a workbook which contains a data sheet (Records) and then 52 worksheets (Week 1, Week 2, etc).

Each weekly worksheet contains a table displaying data from the Records worksheet. The table is made and works very well (but only for Week 1).

In the Records worksheet, I have 52 rows (row 18 to row 69), with each row representing one week. Each row has a cell in column B indicating the week number.

Each row has user inputted data, followed by calculations made from this data in the subsequent cells of the row.


I would like each table in the weekly worksheets to automatically reference the correct row. I have tried to copy and paste the table to each weekly sheet, which works fine, except the data in each table only refers to Week 1 (row 18) as all I have done is copy and paste the table from the first weekly sheet (Week 1).

Effectively, I would like to 'drag-copy' the table across the 52 sheets, so that all referencing formulae in the table advance and display the data from the correct row.

For example, a formula in a cell of the 'Week 1' worksheet is "=Records!R18". I want the formula in the same cell of the 'Week 2' worksheet to become "=Records!R19".

It seems like there should be a simple way to do this, like "copy table while advancing formulas" or "if table name is Week 24, then retrieve data from the Week 24 row (row 41) on the Records sheet" or something to that effect.

Thanks!

[amotto11]

the easiest way to do this would be to write a macro, but without a macro a way to do it would be to copy the formula itself and paste it in each worksheet, then change the reference.

First go to week 1, click on the cell that contains the records formula, copy the formula out of the function box.
Next click to open week 2 sheet, then scroll over till you see week 52 sheet. Hold down shift and click week 52, this should choose each sheet.
Then click on the cell that you want to put the formula in and hit ctrl (in your case the apple) + v, this will give you the original formula in each sheet.
all you have to do now is in each sheet change the number from 18 to 19 and so on in your formula.

[benjaminjrp]

Yes, that is exactly what I am trying to avoid. There are around 20 formulae in each weekly table, and 52 tables...

Happy to look into macros if anyone can provide one that would approximate the job.

Thanks

[oeldere]

See the tips in this link to get better result.

http://www.excelforum.com/the-water-...-question.html