|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to duplicate a block of cells that contain formulas without losing reference?
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: How to duplicate a block of cells that contain formulas without losing reference? How to duplicate a block of cells without losing formula references - Microsoft Community Hub https://www.msofficeforums.com/excel...tml#post171318 How to duplicate a block of cells without losing formula references | MrExcel Message Board Last edited by ColtonYYZ; 10-29-2022 at 08:33 AM. Reason: Including links to other sites I have asked this question on. |
#2
|
|||
|
|||
Thanks to another member on one of the other forums I posted on, I now have the answer I've been looking for:
=INDEX(Sunday!$C:$J,QUOTIENT(ROW(D45)-14,31)+3,COLUMN(D45)-3) |
#3
|
|||
|
|||
Change each weekday chart to a table. References will follow table name.
Formula in D14>> =INDEX(tblSunday[Travel Start],MATCH($C$8,tblSunday[[Employee Name ]:[Employee Name ]],0)). Copy across for Sunday times. Add a drop down list of employees and info will up date on form. On my timesheet C8 is employee name. |
Tags |
bulk copy paste, formulas, reference |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
how to create a block text and when click on the title of block hide and unhide the block | labasritas@free.fr | Word | 4 | 09-17-2017 01:17 PM |
Does Excel have a way of using variables to easily update reference formulas? | Lluewhyn | Excel | 1 | 10-19-2016 07:59 AM |
large workbook needs restarted every month with deletions and resort without losing formulas | llhail | Excel Programming | 1 | 06-02-2015 05:34 PM |
Formulas/Scripts That Cross Reference & Import Data | ekeithjohnson | Excel Programming | 3 | 08-23-2014 11:26 PM |
Losing rows when pasting split cells | Gitley | Word Tables | 1 | 01-15-2013 07:49 AM |