# INDIRECT function on an INDEX / AGGREGATE Formula

1. ## 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. ## 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. ## 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:

Let us know if you have any questions.

4. ## Re: INDIRECT function on an INDEX / AGGREGATE Formula

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

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