Reference cell in other workbook using an address concatenated from other cells

[gtx_viper]

I have the following cells

A1: ="'\\TBGSCOTLAND\Projects\00 Master Files\_ECRs\ECR-G-"
A2: =".xlsx'!"
A6: 3002
C1: cell_ECR_Priority
C6: =$A$1&$A6&$B$1&C$1

C1 to AY contain their own cell names like above
A6 to A1000 contain their own numbers

It was done this way so that C6 could be dragged to put the formula into C6:AY1000

When C6 is evaluated it becomes...

'\\TBGSCOTLAND\Projects\00 Master Files\_ECRs\ECR-G-3002.xlsx'!cell_ECR_Priority

...but it does not link to the cell in that address.

whats the best way of doing this?

 

[Joe4]

Welcome to the Board!

You would use the INDIRECT function to do that.
See: http://www.contextures.com/xlFunctions05.html#RefWkbk

 

[gtx_viper]

Welcome to the Board!

You would use the INDIRECT function to do that.
See: http://www.contextures.com/xlFunctions05.html#RefWkbk

Hi Joe4,

Tried using INDIRECT but all I get is...

#REF!

...due to the referenced workbook being closed.

When you use the direct link in the cell...

='\\TBGSCOTLAND\Projects\00 Master Files\_ECRs\ECR-G-3002.xlsx'!cell_NCR_REF

...the workbook remain closed.

Unless Ive completely missed something with the indirect function.

 

[Joe4]

If the workbook is closed, I think you are going to need to use VBA.
See: How to referencing value in closed excel workbook by formula incl. variable sheetname? - Stack Overflow

 

[gtx_viper]

If the workbook is closed, I think you are going to need to use VBA.
See: How to referencing value in closed excel workbook by formula incl. variable sheetname? - Stack Overflow

Thanks for the help. Was hoping to stay away from putting in some code but your link gave me the idea for the following compromise...

I added a vba module to create a simple custom function which I can drag the way I want.

Function GetData_ECR(ECR_No, CellName)

GetData_ECR = Evaluate("'\\TBGSCOTLAND\Projects\00 Master Files\_ECRs\ECR-G- & ".xlsx'!" & CellName)

End Function

Thanks again

 

[Joe4]

You are welcome!
Glad it helped you come up with a working solution.