+ Reply to Thread
Results 1 to 6 of 6

Using indirect to calculate intersection of non-consecutive named ranges

  1. #1
    Registered User
    Join Date
    07-22-2016
    Location
    Flagstaff, Arizona
    MS-Off Ver
    2010
    Posts
    9

    Using indirect to calculate intersection of non-consecutive named ranges

    Hi all,

    I'm new to this forum, hopefully I can help answer some questions and also get some help.

    Right now I'm building a worksheet that essentially has a data input sheet that I want to use to store all the data for the rest of the workbook in, and then name all the ranges in that data input sheet so that I can reference them more easily. This sheet has columns that are broken down by year (row 1), month (row 2), budget or actual (row 3), and then revenue, expenses, orders and visits (row 4). The rows are broken down into brands (column A) and revenue channel (column B). The years (just 2016 for now), months, and brands are consecutive, but the actual vs budget, revenue expense orders visits, and revenue channels are not. So for example on the named columns, there are 8 consecutive columns for January (January is listed 8 times from cell C2:J2), and no where else in the sheet does January appear. However, in row 3, budget is listed in groups of 4 (from cell C3:F3, then K3:N3), while actual is listed in groups of four as well (G3:J3, then O3:R3). Therefore, these columns alternate in groups of four, and have named ranges accordingly; this is what I mean by non-consecutive named range. The groups of revenue, expenses, orders, and visits repeat every four columns as well, example: the named range for revenue is column C, G, K, ... EE, while the named range for expenses are D, H, L, ... ED.

    With all that said, I can very easily create a formula using each named range with the space operator separating each, and it will return the cell at the intersection. If all of these named ranges were continuous, I could also hard-code the name of each named range into separate cells (let's say from C2:C7), then use the formula:

    =INDIRECT(C2) INDIRECT(C3) INDIRECT(C4) INDIRECT(C5) INDIRECT(C6) INDIRECT(C7)

    However, when introducing the nonconsecutive named range into this formula, the formula evaluates to a #REF error. I've done lot's of searching on this topic, and haven't found any definitive answers as to whether it's possible or not.

    Thoughts?

    Thanks,
    Nick

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Using indirect to calculate intersection of non-consecutive named ranges

    Hi, welcome to the forum

    With something this complex, I suggest you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    07-22-2016
    Location
    Flagstaff, Arizona
    MS-Off Ver
    2010
    Posts
    9

    Re: Using indirect to calculate intersection of non-consecutive named ranges

    Ya, Should Have uploaded something to begin with. Here is an example workbook of my description above.
    Attached Files Attached Files

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Using indirect to calculate intersection of non-consecutive named ranges

    Try this array formula**:

    =INDEX(Data!C5:Z21,MATCH(1,(Data!A5:A21=C2)*(Data!B5:B21=C3),0),MATCH(1,(Data!C1:Z1=C4)*(Data!C2:Z2=C5)*(Data!C3:Z3=C6)*(Data!C4:Z4=C7),0))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    07-22-2016
    Location
    Flagstaff, Arizona
    MS-Off Ver
    2010
    Posts
    9

    Re: Using indirect to calculate intersection of non-consecutive named ranges

    Wow, this is fantastic. I don't know enough about the index function or arrays in Excel, seems like they are pretty powerful.

    But still, any idea if there is a way to do this only using named ranges like in my original post?

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Using indirect to calculate intersection of non-consecutive named ranges

    Quote Originally Posted by snick45 View Post
    But still, any idea if there is a way to do this only using named ranges like in my original post?
    Not that I'm aware of.

    In older versions of Excel you could use the row/column headers as lookup criteria. For example:

    =Tue Sue

    Where Tue is a row header and Sue is a column header.

    This feature was deprecated from newer versions of Excel for whatever reason.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 06-08-2016, 03:34 AM
  2. Offset and Indirect with Dynamic Named Ranges
    By nickmangan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-07-2014, 10:42 PM
  3. Using INDIRECT with named ranges
    By wunderfisch in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-17-2013, 01:10 PM
  4. Returning cel name in an intersection of named ranges
    By Herrie in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-28-2012, 07:10 AM
  5. INDIRECT and named ranges
    By Excel_Learner42 in forum Excel General
    Replies: 3
    Last Post: 01-02-2009, 01:18 PM
  6. VBA for intersection of two named ranges
    By brucemc in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-03-2006, 09:16 AM
  7. intersection of named ranges
    By Dave B in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-15-2005, 07:45 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1