INDIRECT function on an INDEX / AGGREGATE Formula

[RLONG98]

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 & ")))")

[RLONG98]

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 & ")))")

[JeteMc]

It is easier to help when we have a file to work with (see banner at the top of the page).
That said try:

Please Login or Register  to view this content.

Let us know if you have any questions.

[RLONG98]

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

[JeteMc]

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.