How to detect the cell reference in a formula

[chris500]

Hello,

I think I may be missing something obvious here, but I have a problem with transposed data.

I have 7 days data held in columns A to G which I then link to in a different workbook. Copying the formulas is no problem with relative referencing.
The problem is with copying data for the next week - in the destination workbook, I carry on moving across columns (ie week2 day one is in column H in the same row), but the data in the source is held in the next row down (ie A2). I can't see how to set up a formula that I can copy easily (ie that goes back to col A but moves down a row).

Hope that makes sense! I'd be grateful for any help on this.

[MSP77079]

I suggest using the OFFSET function.

In the example below, I have a formula on Sheet2 that refers to cells on Sheet1. This shows the idea. You would have to modify this only slightly to make it refer to a different workbook.

=OFFSET(Sheet1!$A$2,INT((COLUMN(A1)-COLUMN($A$1))/7),MOD(COLUMN(A1)-COLUMN($A$1),7),1,1)

[chris500]

Thanks for that - it worked a dream!