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
Twifoo's avatar
Twifoo
Silver Contributor
Apr 24, 2019
Use mixed reference like this in B1, copied down to B5:
=(MAX(A$1:A$5)=A1)*A1
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. 

Resources