Copying formulas between cells!

[jimkaten]

Hi,
This is not so easy as the heading makes it seem, I attach a sample spreadsheet. I am trying to use a formula that copies the totals of each site month (e.g Jan, Feb, March etc) onto an overview sheet. But I have a date column in between the month columns on the site sheets but do not want that column put in the overview sheet. So in theory, this sounds easy, highlight the two cells that I need (i.e. B15 and D15) and click and drag to copy the formula but for some reason it copies the next cells which end up (D15 and F15). So in part it is doing it right but not continuing the sequence (i.e. B15, D15, F15, H15). Does this make any sense? What I want to do is not have to type in the formula for each cell but to copy it from cell to cell on each row, can somebody help please?

Thanks.

[mrice]

Its a bit convoluted but try

=INDIRECT("'" & A5 & "'!R15C" & CELL("col",INDIRECT(CHAR((CELL("col",B7)*2)+62)& "5")),FALSE)

pasted into B5 on the overview sheet. This can be dragged across row 5 and down. Note this only works because you have columns in the other sheets that are represented by single letters (i.e. if you went to AA, AB etc, this would fail).

[jimkaten]

I'm incredibly thick but this did not work when I pasted it in. It came up with #REF!

Can you please explain what I'm doing wrong!

Thanks.

[oldchippy]

Hi,

The $ sign is missing

=INDIRECT("'" & $A5 & "'!R15C" & CELL("col",INDIRECT(CHAR((CELL("col",B7)*2)+62)& "5")),FALSE)

[jimkaten]

Thank you that worked a treat but how do I change the formula to do the other rows?

[oldchippy]

Hi,

AUTO-FILL is the answer, if your cursor is on cell B5, hold the cursor over the little black square in the bottom right of the cell untill it changes to a black cross. Then hold your mouse button and drag across to cell M5 and also down to M7

[jimkaten]

Hi,

Have tried doing that but it comes up with #REF!. Is this because the totals are in different cells and not B15 on every site sheet, i.e. on some site sheets the Jan Total is B16 or B14.

Thanks.

[oldchippy]

Hi,

Can't you make your totals in the same row on each sheet? If row 16 is the max, make them all row 16.

[jimkaten]

Yep I could but does the formula then need to be changed because it is looking at a different cell. If so, please tell me which bit needs to be change. As this formula is way too advanced for me to even understand.

[oldchippy]

=INDIRECT("'" & $A5 & "'!R16C" & CELL("col",INDIRECT(CHAR((CELL("col",B7)*2)+62)& "5")),FALSE)