Forum Discussion
INDIRECT with Quoted ADDRESS
you don't need the quotes in this case because the result of ADDRESS is already TEXT so try:
=INDIRECT(ADDRESS(ROW(A1),COLUMN(A1)))
- ourdogsover20kgFeb 03, 2025Copper Contributor
"B1 formula is =INDIRECT("A1").
If A1 is cut and pasted elsewhere leaving a blank, B1 would become blank instead of referencing wherever xyz has moved to.
The above works as intended."The quotes are needed, for B1 to keep referencing the A1 cell absolutely and not elsewhere.
Please advise further. Thank you!
- m_tarlerFeb 04, 2025Bronze Contributor
You can't just add "" to make work. The quotes in the original make that "A1" text instead of a cell reference. In your example:
But incurs #REF! once nested like =INDIRECT(""""&ADDRESS(ROW(A1),COLUMN(A1),4)&"""").
fails because you are adding "" to text and indirect doesn't understand that. So INDIRECT ("A1") will return a reference A1 but in the above case it will be like INDIRECT( " "A1" ") and try to find the reference to a cell "A1" (quotes included) and that is not a valid reference.
Your final question was:
How should B1 be formulated so, when copied, B2 would become =INDIRECT("A2")?
and with respect to the prior formula using ROW() and COLUMN() that would be answer I provided.
But if I understand you correctly you want the formula in B1 to have CELL reference if and when copy and paste is acted on B1 but NOT if and when copy or move is acted on the Cell reference that is in the formula in B1. Sorry you can't have it both ways, either the references in B1 are CELL references and will change or they are text and will not change.
BUT maybe what you want is to use RC format. So Try this:
=INDIRECT("RC[-1]",0)
In this case the relative offset is -1 column and as you copy down will stay -1 column. AND if you move A1 it will not change. NOTE: if you want to use this style throughout the sheet you can change the setting in the options to use that as the reference style throughout (select the R1C1 reference style checkbox):
That said however if you don't use the text inside of INDIRECT it will still be a cell reference and move when the target reference moves, so doesn't help for this issue. (i.e. you still can't use =RC[-1] as the formula to get the behavior you want)
- ourdogsover20kgFeb 05, 2025Copper Contributor
Apparently, when the address need to be quoted, the quotes have to be hardcoded for INDIRECT to work. I tried some ways to make the address relative by indirectly inserting the quotes via another formula, like using SUBSTITUTE, but INDIRECT simply threw #REF!.
So, I assume one just cannot have things both ways for now, as you pointed out.
Offsetting is not practical in my case, but thank you so much for your explanation.