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

- 469K Members
- 4,846 Online
- 568K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- How to make changes ranges in an array/formula without manually modifying it each time

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

- Home
- :
- Excel
- :
- General Discussion
- :
- How to make changes ranges in an array/formula without manually modifying it each time

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

03-15-2019 06:34 AM - edited 03-15-2019 07:24 AM

When I copy/paste data into a spreadsheet, the array below looks at ranges in columns O and B. However, the next time I copy/paste data in the columns may be different.....column O and A, or H and L. Is there a way to be able to make this change without manually modifying the array each time? Perhaps inputting a letter ("H") into a cell and it will make the change from O2:O245 to H2:H245 for example? Or a drop down menu that has all the possible options available (A2:A245, B2:B245, C2:C245, etc) and that gets inputted into the array?

{=IFERROR(INDEX(O2:O245, SMALL(IF(U$1=B2:B244, ROW(O2:O244)-1,""), ROW()-1)),"")}

Let me know if you can help!

Thanks,

Greg

Labels:

9 Replies

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

03-15-2019 09:41 AM - edited 03-15-2019 09:42 AM

I believe you could use Indirect,

I am only going to update the first O reference in the below example. I am going to put the reference letter "H" in Cell A1 to change it to H or whatever letter you put in Cell A1. I have never done this (used indirect) within an array function before so I am crossing fingers.

Yours:

{=IFERROR(INDEX(**O2:O245**, SMALL(IF(U$1=B2:B244, ROW(O2:O244)-1,""), ROW()-1)),"")}

O2:O245 is replaced with INDIRECT(A1 & "2:"& A1 &"245") in mine.

{=IFERROR(INDEX(**INDIRECT(A1 & "2:"& A1 &"245")**, SMALL(IF(U$1=B2:B244, ROW(O2:O244)-1,""), ROW()-1)),"")}

If you use the Evaluate formula tool in the formulas tab and step through this you will see INDIRECT(A1 & "2:"& A1 &"245") get replaced with H2:H245.

You could also build the text in a separate cell and then reference it, for example keeping A1 as "H" in Cell B1 you could have a formula = A1 & "2:" & A1 &"245" you would then see "H2:H245"

and then:

{=IFERROR(INDEX(**INDIRECT(B1)**, SMALL(IF(U$1=B2:B244, ROW(O2:O244)-1,""), ROW()-1)),"")}

Good luck

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

03-15-2019 10:05 AM

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

03-15-2019 10:14 AM

That's awesome, and interesting.

I've always hated array formula's because of the whole Ctrl+Enter thing. If this works you could technically build a whole formula in cell A1 (minus the curly brackets{}). and then make your array formula only the indirect i.e. ={INDIRECT(A1)} if that maintains the Ctrl enter, you could make changes without the worry of accidentally not hitting Ctrl+Enter.

Although that only works if you only have the formula in 1 cell and aren't filling down the formula.

Anyway glad your workbook is working.

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

03-22-2019 04:13 PM

@ILikePi ,

I your case you may use AGGREGATE to avoid array formula. And, as variant, if your ranges are always start from the same row (second one) and they are of same height, OFFSET could be used for the reference. If number of columns to offset from column A are parametrised in cells A1 and B1 that could be like

=IFERROR(INDEX(OFFSET($A$1,1,$B$1,250),AGGREGATE(15,6,1/($U$1=OFFSET($A$1,1,$A$1,250))*(ROW($C$2:$C$25)-1),(ROW()-1))),"")

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

03-23-2019 03:34 AM

Don't let the inadequacies of the user interface put you off the clarity of thought that comes with array formulas. Virtually no one uses array formulas so it is not surprising that the user interface is clunky and inconvenient (I once suggested that the defaults should be reversed and those who wish to use implicit intersection to destroy arrays should be forced to go through the inconvenience of CSE!)

For this question, I had waded through the sea of direct notation, if finished with

{= IFERROR( INDEX( ValueArray, SMALL( IF( TestArray=Criterion, k ), k ) ), "")}

How one defines ValueArray is a separate issue; it would normally be absolute but could be defined as column relative or movable through the use of OFFSET.

What is now possible with dynamic arrays is to reduce the whole formula to

= FILTER( ValueArray, TestArray=Criterion, "Null" )

My other observation would be that a formula defined as Named formula always has been protected against the ravages of implicit intersection. That provides an alternative strategy to hiding array calculations in one of the few functions that work properly with arrays (AGGREGATE, SUMPRODUCT, LOOKUP etc.)

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

03-23-2019 03:57 AM

@Peter Bartholomew , you may wrap array calculations by AGGREGATE, SUMPRODUCT,... without named ranges, isn't it?

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

03-23-2019 05:55 AM

I think the use of SUMPRODUCT as a wrapper for array formulas is reasonably well known, though some would deny that they are actually performing an array calculation simply because they didn't need CSE.

I find it less well-known that a formula placed into the 'refers to' box of a Name will also be evaluated as an array every time the Name is referenced. Thus

=SUM( myArrayFormulaByName )

does not need CSE even though it would if the parameter were an explicitly written array formula.

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

03-23-2019 06:27 AM

@Peter Bartholomew - yes, agree. Named ranges/formulas in general are not very popular, even tables which in some extent could be considered as dynamic named ranges.

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

03-23-2019 08:56 AM

I would consider tables as the ideal way of holding the initial input and reference data for a model. My aim when developing a solution is to capture as much structure as I can within the solution and, also, to link it as tightly as possible to the terminology and assumptions of the application domain.

That rather contrasts with the normal 'tips and tricks' genre aimed at achieving (bad?) solutions faster. Speed is good but it should not be the main objective.

Related Conversations

Stable version of Edge insider browser

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

7 Likes

35 Replies

How to Prevent Teams from Auto-Launch

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

8 Likes

30 Replies

flashing a white screen while open new tab

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

14 Likes

14 Replies

Security Community Webinars

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

12 Likes

13 Replies

Share

Popular

Learning Resources

Programs

Values

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