Change Reference

JoeExcelHelp
5StarLounger
Posts: 1177
Joined: 22 Jul 2013, 18:29

Change Reference

Post by JoeExcelHelp »

I have this link from one workbook to another and I'm trying to change the last portion of the link (ABQ'!$S$77) to reference a 2 cells in WB (2015 Station Summary Report winter trough V2).. Basically, A1 would change tab names like ABQ, CLT.. and A2 would change a cell reference within that tab

Code: Select all

='H:\2014 SSR\Test R18\[2015 STATION SUMMARY REPORT Winter Trough V2.xlsm]ABQ'!$S$77

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Change Reference

Post by HansV »

The INDIRECT function can be used to build a cell reference from text values, but it only works with references to another workbook if that other workbook is open. If the other workbook is closed, the function will return #REF!

If 2015 STATION SUMMARY REPORT Winter Trough V2.xlsm is open, you can use

=INDIRECT("'[2015 STATION SUMMARY REPORT Winter Trough V2.xlsm]" & A1 & "'!" & A2)
Best wishes,
Hans

JoeExcelHelp
5StarLounger
Posts: 1177
Joined: 22 Jul 2013, 18:29

Re: Change Reference

Post by JoeExcelHelp »

Thank you.. Anything i could do to have the values remain published while the other workbook is closed

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Change Reference

Post by HansV »

Not really...
Best wishes,
Hans