Issues with INDIRECT

[Cruzo23]

Hi,

I am currently working on making a formula to have a dynamic ranges. The original (formula seen below) works fine

=IFERROR(INDEX(INDIRECT("'"&E40&"'!"&"A1"&":"&"NY35"),SMALL(INDEX(NOT(ISBLANK(Holidays2018!A$3:NY$35))*ROW(Holidays2018!A$3:NY$35),0,C1),COUNTBLANK(INDIRECT("'"&E40&"'!"&D1&"3"&":"&D1&"35"))+ROW(A1)),0),"")

When trying to covert the ranges using INDIRECT, the result returns the data from CELL A1 from the 'Holidays2018!' sheet.

=IFERROR(INDEX(INDIRECT("'"&E40&"'!"&"A1"&":"&"NY35"),SMALL(INDEX(NOT(ISBLANK(INDIRECT("'"&E40&"'!"&"A1"&":"&"NY35")))*ROW(INDIRECT("'"&E40&"'!"&"A1"&":"&"NY35")),0,C1),COUNTBLANK(INDIRECT("'"&E40&"'!"&D1&"3"&":"&D1&"35"))+ROW(A1)),0),"")

I'm new to advanced excel and finding my way through the formulas.

Thanks in advance.

 

[Cruzo23]

SOLVED: hadn't locked relevant cells which caused errors.

Not sure how to delete this post