# Excel copy formula Q **URL:** \ **Category:** Factual Questions\ **Created:** [August 13, 2020, 4:15pm UTC](https://boards.straightdope.com/t/excel-copy-formula-q/918003 "2020-08-13T16:15:01Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [August 13, 2020, 4:15pm UTC](https://boards.straightdope.com/t/excel-copy-formula-q/918003/1 "2020-08-13T16:15:01Z")
I understand absolute and relative cell references, but they don’t seem to help me. I’m sure someone has a solution! In cells T1, U1, V1 there are formulas that reference cell A1. I want W1, X1, Y1 to have the same formulas reference B1, but when copied from T1:V1, they reference D1 (because it is 3 cells to the right). Any ideas? ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [August 13, 2020, 4:37pm UTC](https://boards.straightdope.com/t/excel-copy-formula-q/918003/2 "2020-08-13T16:37:40Z")
Never mind. Brain damage. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [August 13, 2020, 4:43pm UTC](https://boards.straightdope.com/t/excel-copy-formula-q/918003/3 "2020-08-13T16:43:36Z")
The least confusing answer is to use absolute references to A1 in T1:V1 and after copying them to W1:Y1 editing each individual absolute reference from A1 to B1. Then repeat as necessary if you’re going to be creating more groups of 3 cells in a row that all refer to the same single cell elsewhere. Ultimately copying relative references assumes every cell referenced in the formula wants to slide the same amount left/right and/or up/down. In your case that’s not true, so copying relative references makes a mess. ---
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [August 13, 2020, 4:57pm UTC](https://boards.straightdope.com/t/excel-copy-formula-q/918003/4 "2020-08-13T16:57:29Z")
LSLGuy - glad you recovered. I’m pretty sure the INDIRECT function will do the job. Although I was hoping for copy-paste trick that wouldn’t require major adjustments to the formulas. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [August 13, 2020, 5:02pm UTC](https://boards.straightdope.com/t/excel-copy-formula-q/918003/5 "2020-08-13T17:02:03Z")
You could hide two place holder columns after A. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [August 13, 2020, 7:06pm UTC](https://boards.straightdope.com/t/excel-copy-formula-q/918003/6 "2020-08-13T19:06:52Z")
You need to rewrite the formulas in T1:V1, although I would recommend almost anything but INDIRECT. What are the formulas? ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [August 13, 2020, 7:27pm UTC](https://boards.straightdope.com/t/excel-copy-formula-q/918003/7 "2020-08-13T19:27:07Z")
You can do it with INDIRECT, although it ain’t pretty. Here’s a sample formula: =INDIRECT(ADDRESS(ROW(),(COLUMN()-2)/3-5)) ---
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [August 13, 2020, 7:40pm UTC](https://boards.straightdope.com/t/excel-copy-formula-q/918003/8 "2020-08-13T19:40:29Z")
Munch - yes, that works. Although kind of a PITA if there are a lot of groups CookingWithGas: ``` In P4 Q4 R4 =IF(B4<>"",B$2,"" ) =IF(B4<>"",$A4,"") =IF(B4<>"",B4,"") ``` (the idea being to show in the three cells: column header from row 2, date from column A, value from B4) When copied to S4:U4, the B4 references become E4. I want them to be C4 ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [August 14, 2020, 1:59am UTC](https://boards.straightdope.com/t/excel-copy-formula-q/918003/9 "2020-08-14T01:59:51Z")
I’m not sure this is any better than the INDIRECT solution but I don’t like to use INDIRECT except to reference values in another cell, like a sheet name. ``` P4: =IF(OFFSET($A4,0,(COLUMN()-13)/3)<>"",OFFSET($A$2,0,(COLUMN()-13)/3),"") Q4: =IF(OFFSET($A4,0,(COLUMN()-14)/3)<>"",$A4,"") R4: =IF(OFFSET($A4,0,(COLUMN()-15)/3)<>"",OFFSET($A4,0,(COLUMN()-15)/3),"") ``` ---
**Author:** ![Maserschmidt](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/maserschmidt/32/18829_2.png) [@Maserschmidt](https://boards.straightdope.com/u/Maserschmidt)\ **Post date:** [August 14, 2020, 2:05am UTC](https://boards.straightdope.com/t/excel-copy-formula-q/918003/10 "2020-08-14T02:05:27Z")
I am lazy, I would 1. Copy cells T1, U1, V1 down to row 2 2. **Move** these new formulas to W2, X2, Y2 3. Copy them up to W1, x1, y1 4. Ctrl-h these new cells and replace A1 with B1 ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [August 15, 2020, 2:57am UTC](https://boards.straightdope.com/t/excel-copy-formula-q/918003/11 "2020-08-15T02:57:22Z")
> [@Maserschmidt](#): > > I am lazy, I would > > 1. Copy cells T1, U1, V1 down to row 2 This is a great solution–if you only have to do it once.