How to convert a static reference into a dynamic reference

[Peterw_2506]

Hi All,

I am having a little problem turning a static reference into a dynamic reference.

This formula, =LOOKUP(2,1/('[_Credit Card Expense.xlsx]Aug. 20'!$E:$E<>""),'[_Credit Card Expense.xlsx]Aug. 20'!$E:$E) works properly as a static reference.

This formula, =INDIRECT("'["&K1&"]"&C73&"'!"&M64), works correctly also with K1 referring to the workbook and C73 referring to the correct worksheet.

However, M64 is still a static cell reference when I need to reference the last cell in Column E that contains a numeral.

Can somebody show me how to convert this functioning static address into a dynamic address please.

Thank you in advance.

 

[arthurbr]

Replace the static reference with INDEX(E:E,MATCH(9.99999999999999E+307,E:E))

 

[Peterw_2506]

Dear Arthur, thank you for your help. Can I ask you to write the entire formula out as I seem to be stuffing this up somewhere when pasting you answer in as it returns #ref! answer