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)))
"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!
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)
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.
I think you are still thinking about the quotes wrong in that Excel typing "A1" is telling excel to interpret A1 as text as opposed to a function or reference or something else. The same would be true if you type "SUM(1,2,3)" in a formula it tell Excel to use that as a string/text and not parse that SUM as a function call. This is also no different than if type Z100 into cell A1 and you want to use indirect to reference cell Z100. You would use =INDIRECT(A1) and you do NOT need to add quotes to it and you can move cell Z100 anywhere you want it will still reference Z100 because the text in cell A1 is still Z100.
That all said I don't understand how the R1C1 format doesn't work for you. You say you want B1 to reference A1 regardless of moving A1 BUT you want to copy B1 to B2 and have B2 now reference A2. This is exactly what =INDIRECT("RC[-1]",0) will do. If it isn't working how you want please explain because I'm really interested now.