+ Reply to Thread
Results 1 to 5 of 5

INDIRECT function on an INDEX / AGGREGATE Formula

  1. #1
    Registered User
    Join Date
    07-01-2019
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    18

    Lightbulb INDIRECT function on an INDEX / AGGREGATE Formula

    I have 3 tables in separate worksheets name:
    2018
    2019
    2020

    All have the same exactly headers

    On another page i'm creating scrolling quick view page the client wants to show his colleges, and not sift through a data table.

    HERE is where it gets me. I've used INDIRECT well but not for an INDEX / AGGREGATE

    =INDEX(Table2018[Date],AGGREGATE(15,6,(ROW(Table2018[Month_Rpt])-ROW('2018'!$W$2)+1)/(Table2018[Month_Rpt]=$F$6),ROWS(F$9:F9)))


    Here was my attempt: B6 = Year Selection
    So the goal is to be able to change the year in B6 and the Formula redirects to the right table.


    =INDIRECT("INDEX(Table" & $B$6 & "[Date],AGGREGATE(15,6,(ROW(Table" & $B$6 & "[Month_Rpt])-ROW('" & $B$6 & "'!$W$2)+1)/(Table" & $B$6 & "[Month_Rpt]=$F$6),ROWS(" & F$9:F9 & ")))")

  2. #2
    Registered User
    Join Date
    07-01-2019
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    18

    Re: INDIRECT function on an INDEX / AGGREGATE Formula

    Here is another try that failed. Without using B6 and keeping 2018 year in - it still returns #REF


    =INDIRECT("INDEX(Table" & "2018" & "[Date],AGGREGATE(15,6,(ROW(Table" & "2018" & "[Month_Rpt])-ROW('" & "2018" & "'!$W$2)+1)/(Table" & "2018" & "[Month_Rpt]=" & $F$6 & "),ROWS(" & F$9:F9 & ")))")

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,695

    Re: INDIRECT function on an INDEX / AGGREGATE Formula

    It is easier to help when we have a file to work with (see banner at the top of the page).
    That said try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  4. #4
    Registered User
    Join Date
    07-01-2019
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    18

    Thumbs up Re: INDIRECT function on an INDEX / AGGREGATE Formula

    ANSWER FOUND
    I needed to put INDIRECT around every specific address I need to manipulate


    =INDEX(INDIRECT("Table" & $B$6 & "[Date]"),AGGREGATE(15,6,(ROW(INDIRECT("Table" & $B$6 & "[Month_Rpt]"))-ROW(INDIRECT("'" & $B$6 & "'!$W$2"))+1)/(INDIRECT("Table" & $B$6 & "[Month_Rpt]")=$F$6),ROWS(F$9:F9)))

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,695

    Re: INDIRECT function on an INDEX / AGGREGATE Formula

    Glad that you were able to find an answer that works for you. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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. [SOLVED] Index/Aggregate formula calculates correct row but does not yield name from Index array
    By FivestarMac in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-17-2019, 05:07 PM
  2. Replies: 1
    Last Post: 08-17-2019, 01:11 PM
  3. [SOLVED] Aggregate/Indirect Formula Breaks Down After 3 or More Conditions
    By meseleto in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 05-30-2019, 12:49 PM
  4. Index/Aggregate Function for Lookup
    By VirenS in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-15-2017, 12:09 PM
  5. Index/Aggregate Function for Lookup
    By VirenS in forum Excel General
    Replies: 0
    Last Post: 06-16-2016, 04:28 AM
  6. INDIRECT, INDEX, AGGREGATE?? Help!
    By bbr in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-26-2013, 08:03 PM
  7. [SOLVED] Need assistance on nesting an indirect function in a index / match formula
    By ghosters in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-08-2012, 06:00 AM

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