Forum Discussion
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 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.
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.
Is there any way to change that absolute reference value with new absolute value.
Thank you
11 Replies
- Use mixed reference like this in B1, copied down to B5:
=(MAX(A$1:A$5)=A1)*A1- 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- 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!
