Can you "freeze" relative reference?

[raduray]

I’m trying to do something that seems as it would be fairly simple, but can’t figure out how to do it.


The challenge can be abstracted to a two row spreadsheet:


A B C D E F G H I J
A B C


Row 1 is constants, A, B, C….J
Row 2 is only three columns and the cell values are equal to the cell value above.


What I would like to do, having defined the equation in the second row, is cut and paste the second row and paste it one column over to get:
A B C D E F G H I J
B C D


If I cut and paste the original to move it two columns over, the results should be
A B C D E F G H I J
C D E

And so on..


Any ideas?

 

[raduray]

I'm a new user. It seems that tabs aren't recognized and I don't have permission to edit. So I'm reposting the question, but using hyphens to represent blank cells in the second row in order to the the spacing right.

The challenge can be abstracted to a two row spreadsheet:


A B C D E F G H I J
A B C


Row 1 is constants, A, B, C….J
Row 2 is only three columns and the cell values are equal to the cell value above.


What I would like to do, having defined the equation in the second row, is cut and paste the second row and paste it one column over to get:
A B C D E F G H I J
--B C D


If I cut and paste the original to move it two columns over, the results should be
A B C D E F G H I J
-----C D E

And so on..

 

[mikerickson]

the formula =A1 should do what you want.

 

[raduray]

the formula =A1 should do what you want.

That's the first thing I tried. Unfortunately it doesn't work.

I apply =A1 to A2, and it displays "A". But when I cut and post A2 to B2, the result is "A", not "B".

 

[Scott T]

Excel does not update the cell reference when you cut and paste only when you copy. You can copy the cells and then delete the unneeded cells.

 

[mikerickson]

Or you could use names.
Select A2 and define a Name: myName RefersTo: =Sheet1!A$1
(note the relative reference)

Then putting =MyName in A2 and cut pasting will do what you want.

 

[FormR]

Hi, just another option for the specific example.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	A	B	C	D	E	F	G	H	I	J
2	A	B	C
Sheet1

Cell Formulas
Range	Formula
A2	=INDEX(1:1,COLUMN())