refer to cell within a formula

[nparsons75]

Hi,

I use this formula to create a hyperlink to a file stored in the location stated within the formula.

='\\file\projects\BUSINESS IMPROVEMENT\BUSINESS IMPROVEMENT STORAGE\32\PROJECT CONTROL\[32.xlsm]CONTROL REGISTER'!$B$13

The number 32 in this case (in red) has to be added manually. However the number exists in a cell adjacent (in column A) to the cell that contains the formula. Is it possible to refer automatically to this number within the formula so links are created automatically?

 

[etaf]

assuming in cell A2
try
indirect("'\\file\projects\BUSINESS IMPROVEMENT\BUSINESS IMPROVEMENT STORAGE"&A2&"\PROJECT CONTROL\["&A2&".xlsm]CONTROL REGISTER'!$B$13")

 

[nparsons75]

Hi Wayne, good to hear from you. I just tried it as you suggested but get #REF ?

My table consists of a lot of rows, each row in column A has a reference number, this is the number I need to link to.

REF NO	DATE	PROJECT TITLE	STATUS	PROGRESS
1	17/9/17	PROJECT 1	ACTIVE	55%
2	18/9/17	PROJECT 2	COMPLETE	100%
3	19/9/17	PROJECT 3	ON HOLD	0%
4	20/9/17	PROJECT 4	ACTIVE	85%

<tbody>
</tbody>

For each of the rows I have an excel file that I name the same as the ref number. (1.xlms) for example. I need each reference number to link to each file automatically. This will mean a user can click on the ref no to open up the file. Each time I add a new row I need the ref no to create a link automatically.

I need the same also for progress. The value for progress is taken from a value located on the excel sheet named (1.xlms). Therefore, I need the column PROGRESS to look at the sheet (1.xmls) and return the value into the progress box.

Really hope that makes some kind of sense....

 

[etaf]

are these other files closed
if so may need vba - not sure if indirect.ext (more function , still works )

this article explains the indirect formula
http://www.contextures.com/xlFunctions05.html#RefWkbk
and may help
on closed workbooks - i have used the indirect.ext
http://www.ashishmathur.com/tag/indirect-ext/
i used on 2013 successfully upto about a year ago , when i had to buy a new PC and now use office 365 excel 2016
and i have not tried on 2016 , so not sure if it works OK

 

[wideboydixon]

As suggested above, INDIRECT() doesn't work for closed workbooks. I think you have two options, both of which will require VBA:

Option 1: Use VBA to set the correct formula in column E based on the value in column A.
Option 2: Use a UDF in VBA to go and get the value from the closed workbook and put it in column E.

WBD

 

[nparsons75]

Hi, yes the files are closed. thank you for the links.