How to duplicate a block of cells without losing formula references

[bryen79]

Please note: I have posted this question on another website a few days ago and have not received any answers to it. I am hoping on of you kind folks here might know the answer.

I am creating time cards and need 260 of them. I want to simply create one, and then copy paste, duplicating each copy/paste until I have 260. The problem is, formula references are taking account for cells in between each other. In other words, If I have a formula in A1 and A6, and data in the cells in between that also needs to be copied.

I have attached the sample workbook and a photo showing what I want to copy and where.

I need the formulas I copy down without loosing reference (staying sequentially correct) despite other rows in between have data. As it stands now, when I try to paste formula, it calculates all the lines in between the yellow boxes so numerically, it's not referencing the correct cell in the different sheet.

I found this formula listed on another website post and wonder if this would work for what I need to do: =INDIRECT("sheet1!B"& ROW(A5)/5)

I am using VLOOKUP and I tried this code:

=IFERROR(VLOOKUP(B6,Sunday!$A6:$K6,6,FALSE),"")

However I can't figure out how to combine INDIRECT with the VLOOKUP. In my original (real workbook) there are 29 rows between B6 and B35. Same between G5 and G34, and also between D12 and D41.

Can someone help me figure out a way to be able to copy the whole timesheet and duplicate it again and again? I need 260 of them.

Links to other forums I have posted my query on:

https://www.excelforum.com/excel-formulas-and-functions/1392282-how-to-duplicate-a-block-of-cells-th...

https://techcommunity.microsoft.com/t5/excel/how-to-duplicate-a-block-of-cells-without-losing-formul...

https://www.msofficeforums.com/excel/49890-how-duplicate-block-cells-contain-formulas-without.html#p...

https://www.mrexcel.com/board/threads/how-to-duplicate-a-block-of-cells-without-losing-formula-refer...

[#2]

@bryen79 

Your workbook doesn't contains sheets named Sunday etc. so I have no idea what to do.

[#3]

Sorry, I only supplied a sample. I have uploaded a better workbook with the daily sheets included.

[#4]

@bryen79 

The formula in D15 on Timesheet is =Monday!B3. Shouldn't that be =Monday!C3 ?

[#5]

@Hans Vogelaar 

Hi thanks for catching this... I've been staring at my screen for so long I am getting sloppy now.

So...

For Mary, D45 should be =Sunday!C4
For Susie, D76 should be =Sunday!C5
For Peter, D107 should be =Sunday!C6

I've updated the sample book and made those lead in examples for you.

Thank you by the way, for helping me on this!

[#6 · Accepted solution]

@bryen79 

See the attached version.

[#7]

OMG you're the best!!!!! I've never used INDEX or QUOTIENT before but this is perfect!! I texted it by adding another person for each day and it pulled the correct data as I used unique but random times to verify! Works like a charm! My hats off to you!!

[#8]

Hi, sorry, if I may, could I ask for you to explain what these numbers do in the formula please?

-14,31)+3,COLUMN(C13)-3)

Once I understand them, I can tweak them for a different excel file I am working on.  Thank you!

[#9]

@bryen79 

In =INDEX(Sunday!$C:$J,QUOTIENT(ROW(D14)-14,31)+3,COLUMN(D14)-3):

QUOTIENT(ROW(D14)-14,31)+3 is the row number within Sunday!C:J

ROW(D14) is the row number of the cell with the formula. This will automatically be adjusted when you copy this down: in D45 it will be ROW(D45) etc.

We subtract the row number of the first row with such a formula: 14

In D14, ROW(D14)-14 = 0, in D45, ROW(D45)-14 =31, etc.

QUOTIENT(...,31) performs integer division: it divides the first argument by 31 and returns only the whole number part. In D14 this will be 0, in D45 it will be 1, etc.

Finally, we add 3 to get the correct row number on the Sunday sheet: for D14 it is 0+3 = 3, for D45 it is 1+3 = 4, etc.

COLUMN(D14)-3 is the column number within Sunday!C:J. In D13, it is 4-3 = 1, so we refer to the 1st column (C). When we copy the formula to E14, we get COLUMN(E14)-3 = 5-3 = 2, so we refer to the 2nd column in Sunday!C:J, i.e. column D, etc.

Does that help?