How to duplicate a block of cells without losing formula references

ColtonYYZ

New Member
Joined
Oct 29, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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


Sample Book.xlsx
E
45
Timesheet


Sample Book.xlsx
CDEFGHIJ
2922:0023:0024:0025:0026:0027:0028:0029:00
Raw Entries


Also posted here How to duplicate a block of cells that contain formulas without losing reference?
here How to duplicate a block of cells that contain formulas without losing reference?
here How to duplicate a block of cells without losing formula references
 

Attachments

  • copy down2.jpg
    copy down2.jpg
    181.9 KB · Views: 22
Last edited by a moderator:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Probably a dumb question but can't you just copy a set of rows and use Paste Special from the ribbon and choose All? If that works then I suspect ctrl+Y will repeat the exact operation without you having to use the ribbon again and again. I didn't try to download your file from M$ site because I'm not a member there and am not sure I can.

If you're saying you don't want to do that 260 times I get it. In that case I'd be looking for a vba solution.
 
Upvote 0
Hi, no, that doesn't work. Because of the formula's reference in D14, when you paste it in D45, instead of being what I need it to be (=Sunday!C4), it enters in =Sunday!C34 instead. This is because it's physically counting the number of rows between the two formula cells D14 and D45 (29 rows).

This is what each person's first cell (of the red block highlighted), should be:

For Mary, D45 should be =Sunday!C4
For Susie, D76 should be =Sunday!C5
For Peter, D107 should be =Sunday!C6
 
Upvote 0
No because that will copy that exact cell for every single person's timecards. BUT.... I have the answser!!!
 
Upvote 0
The answer (thanks to a member on one of the other forums I posted in) is:

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

I'm so happy and grateful!!!
 
Upvote 0
Does anyone know how to translate the last half of the formula? I mean, can anyone explain that they mean/do? Specifically what I have highlighted in red bold?

"-14,31)+3,COLUMN(D45)-3"
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top