Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- 460K Members
- 12.6K Online
- 557K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Change the absolute reference into new one whenever copy to new location

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Change the absolute reference into new one whenever copy to new location

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-24-2019 09:45 AM

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

Labels:

11 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-24-2019 10:12 AM

Use mixed reference like this in B1, copied down to B5:

=(MAX(A$1:A$5)=A1)*A1

=(MAX(A$1:A$5)=A1)*A1

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-24-2019 10:56 AM

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

I hope I get help with that if possible

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-24-2019 11:12 AM

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-24-2019 11:43 AM

@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-24-2019 01:47 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-24-2019 10:36 PM

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 @Sergei Baklan . 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**.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-25-2019 04:18 AM

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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-25-2019 04:28 AM

@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.

Thank you.

Thank you.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-25-2019 05:09 AM

@chavanpr12 , if format a bit and take the formula for the first cell of the second range

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

AGGREGATE returns the first (4th parameter 1) largest (first parameter 14) value in the array (3rd parameter) ignoring all errors (second parameter 6).

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.

Now as result we have INDEX(A:A,10), or 10th cell in the column A, or A10.

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

Thus second INDEX will be INDEX(A10:A$1048576,5) which returns 5th cell in above range, or A14.

As result INDEX(...):INDEX() will be equivalent of A10:A14 and we apply MAX to that range.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-25-2019 05:14 AM

Nice explanation!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-25-2019 05:53 AM

@Twifoo , thank you

Related Conversations

flashing a white screen while open new tab

Deleted
in
Discussions
on
10-05-2019
30.8K
Views

14 Likes

14 Replies

The announcement regarding self-service purchase capabilities for Power Platform products??

Kelly E
in
Office 365
on
10-21-2019
10.5K
Views

19 Likes

64 Replies

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
34.9K
Views

7 Likes

35 Replies

Security Community Webinars

Valon_Kolica
in
Security, Privacy & Compliance
on
10-22-2019
16K
Views

12 Likes

13 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
178K
Views

8 Likes

29 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft