Forum Discussion

ourdogsover20kg's avatar
ourdogsover20kg
Copper Contributor
Feb 02, 2025

INDIRECT with Quoted ADDRESS

B1 formula is =INDIRECT("A1").
When A1 value is xyz B1 value is xyz too.
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.

Now when B1 is copied to other cells, all targets naturally get the same =INDIRECT("A1").

"A1" with quotes can be obtained in B1 by =""""&ADDRESS(ROW(A1),COLUMN(A1),4)&"""", which yields "A2" with quotes when B1 is copied to B2.
But incurs #REF! once nested like =INDIRECT(""""&ADDRESS(ROW(A1),COLUMN(A1),4)&"""").

How should B1 be formulated so, when copied, B2 would become =INDIRECT("A2")?

Thanking you in advance.

excel
Formulas and Functions

6 Replies

Sort By
  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Feb 03, 2025

    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)))

     

    • ourdogsover20kg's avatar
      ourdogsover20kg
      Copper Contributor
      Feb 03, 2025

      "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_tarler's avatar
        m_tarler
        Bronze Contributor
        Feb 04, 2025

        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)

         

Resources