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.


[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]REF NO[/TD]
[TD]DATE[/TD]
[TD]PROJECT TITLE[/TD]
[TD]STATUS[/TD]
[TD]PROGRESS[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]17/9/17[/TD]
[TD]PROJECT 1[/TD]
[TD]ACTIVE[/TD]
[TD]55%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]18/9/17[/TD]
[TD]PROJECT 2[/TD]
[TD]COMPLETE[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]19/9/17[/TD]
[TD]PROJECT 3[/TD]
[TD]ON HOLD[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]20/9/17[/TD]
[TD]PROJECT 4[/TD]
[TD]ACTIVE[/TD]
[TD]85%[/TD]
[/TR]
</tbody>[/TABLE]


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.