Excel copy formula Q

Factual Questions
1

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?

2

Never mind. Brain damage.

3

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.

4

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.

5

You could hide two place holder columns after A.

6

You need to rewrite the formulas in T1:V1, although I would recommend almost anything but INDIRECT. What are the formulas?

7

You can do it with INDIRECT, although it ain’t pretty. Here’s a sample formula:

=INDIRECT(ADDRESS(ROW(),(COLUMN()-2)/3-5))

8

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

9

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),"")
10

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
11

This is a great solution–if you only have to do it once.