#REF! Error Resulting From INDIRECT / ADDRESS Functions

Question

INDIRECT and ADDRESS functions are used to obtain data from an external workbook using the following formula:

“=INDIRECT(ADDRESS(CELL("row",B8),CELL("col",B8),,,"D:\CI_CORE\CI52-Systems Acceptance_TABS_UPS!!Group Resources\06-Acceptance Program Tracking[0000-00-00x-Acceptance Program Tracking Rev00.xlsx]Project Listing"))”

When external workbook titled “0000-00-00x-Acceptance Program Tracking Rev00.xlsx” is open, the data in worksheet “Project Listing” / cell “B8” is returned. However, when workbook titled “0000-00-00x-Acceptance Program Tracking Rev00.xlsx” is closed, error “#REF!” is returned.

The objective is of this approach is to change the path in the ADDRESS function with a lookup function based on another cell’s value.

The INDIRECT / ADDRESS functions are being used in lieu of a hard “=’D:\CI_CORE\CI52-Systems Acceptance_TABS_UPS!!Group Resources\06-Acceptance Program Tracking[0000-00-00x-Acceptance Program Tracking Rev00.xlsx]Project Listing’!$B$8” to accommodate the changed path. Note the hard equation retains data regardless of the external workbook being open or closed.

Question; is there a way to prevent the INDIRECT function from evaluating once external data is obtained and the external workbook is closed or is there another approach that will produce the data and not produce an “#REF!” error when the external workbook is closed.

Answer 1

Hi,

You may refer to my solution at this link.

Hope this helps.

Answer 2

Hi,

Welcome to Microsoft Community.

Your issue would be better addressed in the Excel IT Pro forums:

Click on the link below to post the query:

http://social.technet.microsoft.com/Forums/en/excel/threads

Thank you.