Indirect Formula not working - Returning specific cell data with file located in touchpoint (aka sharepoint)

[Skinnybob]

Hi all,

I'm trying to pull data in a document that is stored in touchpoint (sharepoint) called "Monthly Cost Report Master Jun 2018.xlsx". This document is created quarterly so obviously Monthly Cost Report Master sep 2018.xlsx does not yet exist.

I use the following formula, and it works with no issues:
=IFERROR('LOCATION/team/2012852b/Documents/Distribution/Reports/Cost Reports/[Monthly Cost Report Master Jun 2018.xlsx]Billing Summary 2018 '!W$43,"")

In order to not get popup errors, I inserted an indirect formula for any future dated reports. i.e. Sep 2018.xls
=IFERROR(INDIRECT("'LOCATION/team/2012852b/Documents/Distribution/Reports/Cost Reports/[Monthly Cost Report Master Jun 2018.xlsx]Billing Summary 2018 '!AE$43"),"")

However the cells do not populate until I remove the indirect (even on the current June report).

Can anyone help? I'm stumped...

p.s. I removed the hyperlink details

Using Windows 10
Excel version 2016

 

[Aladin Akyurek]

INDIRECT does not work with closed books/files.

 

[Skinnybob]

thanks for your reply.

Any suggestions?

 

[Aladin Akyurek]

thanks for your reply.

Any suggestions?

Try to look for the PULL function of Harlan Grove.

 

[Skinnybob]

thanks very much

 

[Aladin Akyurek]

thanks very much

You are welcome.