Cell references

[WadeWatts]

Hi all,
I think a simple one for most of you.

I know if i put $ in a formula for cell reference, and how that works.
is there a way of doing the opposite?

i mean if cell A1 is looking at C1, but i then move column C's data out to column D, can i have A1 keep looking at whatever now falls into C1, rather than follow it to D1.

many thanks
WoW

 

[SanjayGMusafir]

i mean if cell A1 is looking at C1, but i then move column C's data out to column D, can i have A1 keep looking at whatever now falls into C1, rather than follow it to D1.

If you cut paste data - then Excel shall automatically re-populate the cell reference

Else you have to edit Cell formula in A1 or column A:A for collective change using Find & replace method while looking in formulae...

Hope it helps you in some way...

 

[WadeWatts]

If you cut paste data - then Excel shall automatically re-populate the cell reference

Else you have to edit Cell formula in A1 or column A:A for collective change using Find & replace method while looking in formulae...

Hope it helps you in some way...

thanks
so you dont think there is a way to always having cell A1 looking at cell C1, regardless if i move it whatever was in C1.

I was hoping i could automate it somehow,

 

[Legacy 143009]

Try to use named ranges or INDIRECT function btw which is not reccomended.

 

[SanjayGMusafir]

thanks
so you dont think there is a way to always having cell A1 looking at cell C1, regardless if i move it whatever was in C1.

I was hoping i could automate it somehow,

There can be some ways -

It it just one cell or a range of cells?

 

[SanjayGMusafir]

Try to use named ranges or INDIRECT function btw which is not reccomended.

Just to clarify on what @Flashbond has suggested...

Named range can be a way. I was about to suggest the same. even with that you can only cut-paste a cell or a range or move a cell to keep refrences working...

INDIRECT as @Flashbond already suggested is vulnerable and generally not recommended

 

[WadeWatts]

This is what i am trying to do, essentially i need to be able to easily keep track (in this example, cell A3 is being pulled into a dashboard) of whatever the next priority is in column C, more things are added and ticked off as we go along

 

[SanjayGMusafir]

It's all together a different scenario. Now I understand why and what you are asking for...

Please confirm that you want A3 to return value from cell C3 or is there anything else to it too? It should be clear now...

 

[SanjayGMusafir]

Is this what you want? Check and revert...

Book1
	A	B	C	D	E	F
1
2	Priority		Oprtion 1	Oprtion 2	Oprtion 3	Oprtion 4
3	Test 1		Test 1	Test 2	Test 3	Test 4
4	Test 1
Sheet1

Cell Formulas
Range	Formula
A3	A3	=OFFSET(B3,0,1)
A4	A4	=OFFSET(A5,-2,2)

 

[Legacy 143009]

Is this what you want? Check and revert...

Book1
	A	B	C	D	E	F
1
2	Priority		Oprtion 1	Oprtion 2	Oprtion 3	Oprtion 4
3	Test 1		Test 1	Test 2	Test 3	Test 4
4
Sheet1

Cell Formulas
Range	Formula
A3	A3	=OFFSET(B3,0,1)

Yes, OFFSET is another option I was thinking of but it is also another volatile function.