Discussion Re: Change the absolute reference into new one whenever copy to new location in Excel
https://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482186#M29614
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!Wed, 24 Apr 2019 18:12:34 GMTTwifoo2019-04-24T18:12:34ZChange the absolute reference into new one whenever copy to new location
https://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482113#M29605
<P>Hello,</P><P>Is there any way to change the absolute reference value into a new one automatically whenever copy into a new location.</P><P>e.g. There is a series of number from A1:A5 and I want to find out highest value on adjacent to the cell like =IF(((MAX($A$1:$A$5)=A1),A1,0) and drag down so this will either give me only highest value and 0 in B1 to B5.</P><P>Now my question is if I want to use the same type of formula in another location then I have to keep changing the particular value of MAX from the equation.</P><P>Is there any way to change that absolute reference value with new absolute value.</P><P>Thank you</P>Wed, 24 Apr 2019 16:45:52 GMThttps://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482113#M29605chavanpr122019-04-24T16:45:52ZRe: Change the absolute reference into new one whenever copy to new location
https://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482135#M29608
Use mixed reference like this in B1, copied down to B5:<BR />=(MAX(A$1:A$5)=A1)*A1Wed, 24 Apr 2019 17:12:57 GMThttps://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482135#M29608Twifoo2019-04-24T17:12:57ZRe: Change the absolute reference into new one whenever copy to new location
https://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482169#M29612
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.<BR />I hope I get help with that if possibleWed, 24 Apr 2019 17:56:03 GMThttps://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482169#M29612chavanpr122019-04-24T17:56:03ZRe: Change the absolute reference into new one whenever copy to new location
https://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482186#M29614
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!Wed, 24 Apr 2019 18:12:34 GMThttps://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482186#M29614Twifoo2019-04-24T18:12:34ZRe: Change the absolute reference into new one whenever copy to new location
https://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482229#M29618
<P><LI-USER uid="280482"></LI-USER> 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.</P><P>Thank you</P>Wed, 24 Apr 2019 18:43:04 GMThttps://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482229#M29618chavanpr122019-04-24T18:43:04ZRe: Change the absolute reference into new one whenever copy to new location
https://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482292#M29625
<P><LI-USER uid="326496"></LI-USER> ,</P>
<P> </P>
<P>Straigthforward solution could be</P>
<PRE>=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)</PRE>
<P>CSE formula</P>Wed, 24 Apr 2019 20:47:47 GMThttps://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482292#M29625Sergei Baklan2019-04-24T20:47:47ZRe: Change the absolute reference into new one whenever copy to new location
https://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482443#M29638
<P>In the attached file, the formula in B1, copied down rows, is: </P><P>=(MAX(INDEX(A$1:A2,<BR />IFERROR(AGGREGATE(14,6,1/(A$1:A1="")*ROW(A$1:A1),1)+1,1)):<BR />INDEX(A1:<STRONG>A10</STRONG>,<BR />MATCH(1,INDEX(--(A1:<STRONG>A10</STRONG>=""),0),0)-1))=A1)*A1</P><P>Note that the foregoing formula follows a logic similar to that of <LI-USER uid="521"></LI-USER> . Nonetheless, it is a non-array formula and assumes that the maximum number of cells to be evaluated at any given instance is <STRONG>9</STRONG>. If, for example, such maximum is <STRONG>99</STRONG>, <STRONG>A10</STRONG> will have to be modified to <STRONG>A100</STRONG>, as follows: </P><P>=(MAX(INDEX(A$1:A2,<BR />IFERROR(AGGREGATE(14,6,1/(A$1:A1="")*ROW(A$1:A1),1)+1,1)):<BR />INDEX(A1:<STRONG>A100</STRONG>,<BR />MATCH(1,INDEX(--(A1:<STRONG>A100</STRONG>=""),0),0)-1))=A1)*A1</P><P>By the way, Sergei's formula assumes such maximum to be <STRONG>999</STRONG>. </P>Thu, 25 Apr 2019 05:36:01 GMThttps://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482443#M29638Twifoo2019-04-25T05:36:01ZRe: Change the absolute reference into new one whenever copy to new location
https://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482584#M29653
<P><LI-USER uid="521"></LI-USER> ,</P>
<P> </P>
<P>Reading <LI-USER uid="280482"></LI-USER> post I corrected a bit the formula to drop limitation</P>
<P>1) Actually all formulas are regular (not array ones), includes initial</P>
<P>2) A1:A1000 could be changed on A1:A$1048576, but that's performance</P>
<PRE>=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)</PRE>
<P> </P>Thu, 25 Apr 2019 11:18:35 GMThttps://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482584#M29653Sergei Baklan2019-04-25T11:18:35ZRe: Change the absolute reference into new one whenever copy to new location
https://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482591#M29655
@sergei_Baklan All this is difficult formula and hard to comprehend so is it possible to explain each term and their effect so I can edit according in my actual file.<BR />Thank you.Thu, 25 Apr 2019 11:28:07 GMThttps://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482591#M29655chavanpr122019-04-25T11:28:07ZRe: Change the absolute reference into new one whenever copy to new location
https://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482619#M29659
<P><LI-USER uid="326496"></LI-USER> , if format a bit and take the formula for the first cell of the second range</P>
<PRE>=IF((MAX(
INDEX(A:A,
IFERROR(AGGREGATE(14,6,1/($A$1:A10="")*ROW($A$1:A10),1)+1,1)):
INDEX(A10:A$1048576,
MATCH(1,INDEX(--(A10:A$1048576=""),0),0)-1))
=A10),A10,0)
</PRE>
<P>AGGREGATE returns the first (4th parameter 1) largest (first parameter 14) value in the array (3rd parameter) ignoring all errors (second parameter 6).</P>
<P>Our array here is multiplication of criteria 1/($A$1:$A10="") on rows numbers from $A$1 to current one. Criteria returns an error for all blank cells (which will be ignored) and 1 (=1/TRUE) for all non-blank cells. We multiply on cell's row numbers and return the largest one, in our case 10. IFERROR is needed for the beginning of the first range which starts from A1.</P>
<P> </P>
<P>Now as result we have INDEX(A:A,10), or 10th cell in the column A, or A10.</P>
<P> </P>
<P>Within MATCH the INDEX(--(A10:A$1048576=""),0) returns an array with TRUE for each blank cell in the range starting from A10 till end of the column and FALSE otherwise, double dash converts them to 1 and 0. MATCH find position of the first number 1 (1st parameter) in this array, other words position of the first blank cell, in our case that will be 6. Minus 1 gives position of last non-blank cell (5).</P>
<P> </P>
<P>Thus second INDEX will be INDEX(A10:A$1048576,5) which returns 5th cell in above range, or A14.</P>
<P> </P>
<P>As result INDEX(...):INDEX() will be equivalent of A10:A14 and we apply MAX to that range.</P>Thu, 25 Apr 2019 12:09:43 GMThttps://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482619#M29659Sergei Baklan2019-04-25T12:09:43ZRe: Change the absolute reference into new one whenever copy to new location
https://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482623#M29662
Nice explanation!Thu, 25 Apr 2019 12:14:36 GMThttps://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482623#M29662Twifoo2019-04-25T12:14:36ZRe: Change the absolute reference into new one whenever copy to new location
https://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482652#M29667
<P><LI-USER uid="280482"></LI-USER> , thank you</P>Thu, 25 Apr 2019 12:53:04 GMThttps://techcommunity.microsoft.com/t5/excel/change-the-absolute-reference-into-new-one-whenever-copy-to-new/m-p/482652#M29667Sergei Baklan2019-04-25T12:53:04Z