Forum Discussion

chavanpr12's avatar
chavanpr12
Copper Contributor
Apr 24, 2019

Change the absolute reference into new one whenever copy to new location

Hello, Is there any way to change the absolute reference value into a new one automatically whenever copy into a new location. e.g. There is a series of number from A1:A5 and I want to find out hig...
excel
Formulas and Functions
chavanpr12's avatar
chavanpr12
Copper Contributor
Apr 24, 2019
Ya that one i got but if i copy that formula then the row is going to be fixed and it won't change the to other cell like A$10:A$15 automatically.
I hope I get help with that if possible
Twifoo's avatar
Twifoo
Silver Contributor
Apr 24, 2019
That’s just a sample structure of the formula I suggest you to use. For specifics, you need to attach your sample file. Remember, no one could possibly imagine the structure of your actual data, without a sample file. You need not think twice to believe me!
  • chavanpr12's avatar
    chavanpr12
    Copper Contributor
    Apr 24, 2019

    Twifoo Sorry this is the first time in community so I didn't had an idea about the procedure to solve the method. I have explained my problem in excel file itself. Please do check and help to solve the problem.

    Thank you

    • Twifoo's avatar
      Twifoo
      Silver Contributor
      Apr 25, 2019

      In the attached file, the formula in B1, copied down rows, is: 

      =(MAX(INDEX(A$1:A2,
      IFERROR(AGGREGATE(14,6,1/(A$1:A1="")*ROW(A$1:A1),1)+1,1)):
      INDEX(A1:A10,
      MATCH(1,INDEX(--(A1:A10=""),0),0)-1))=A1)*A1

      Note that the foregoing formula follows a logic similar to that of SergeiBaklan . Nonetheless, it is a non-array formula and assumes that the maximum number of cells to be evaluated at any given instance is 9. If, for example, such maximum is 99, A10 will have to be modified to A100, as follows: 

      =(MAX(INDEX(A$1:A2,
      IFERROR(AGGREGATE(14,6,1/(A$1:A1="")*ROW(A$1:A1),1)+1,1)):
      INDEX(A1:A100,
      MATCH(1,INDEX(--(A1:A100=""),0),0)-1))=A1)*A1

      By the way, Sergei's formula assumes such maximum to be 999. 

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 24, 2019

      chavanpr12 ,

       

      Straigthforward solution could be

      =IF((MAX(INDEX($A$1:A1000,IFERROR(AGGREGATE(14,6,1/($A$1:A1="")*ROW($A$1:A1),1)+1,1)):INDEX(A1:A1000,MATCH(1,INDEX(--(A1:A1000=""),0),0)-1))=A1),A1,0)

      CSE formula

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Apr 25, 2019

        SergeiBaklan ,

         

        Reading Twifoo  post I corrected a bit the formula to drop limitation

        1) Actually all formulas are regular (not array ones), includes initial

        2) A1:A1000 could be changed on A1:A$1048576, but that's performance

        =IF((MAX(INDEX(A:A,IFERROR(AGGREGATE(14,6,1/($A$1:A1="")*ROW($A$1:A1),1)+1,1)):INDEX(A1:A$1048576,MATCH(1,INDEX(--(A1:A$1048576=""),0),0)-1))=A1),A1,0)

         

Resources