Extracting information using a cell reference in a file reference

0
votes

I am using Microsoft Excel to draw data from multiple files on a server, however to get this data they are located on different sub files which is determined by a cell on my excel spreadsheet.

For example I have in my formula:

='http://website.com/Subfolder/C9/[Contents.xlsm]Contents'!$Y$123

I cant get the formula to recognise the cell reference in this case C9. This is located within the active sheet. If I replace this with the file name manually this works fine.

I have tried using Concatenate and using a macro but couldn't get it working.

Many thanks

1
excelvbaexcel-formula
Does linking a formula to a web URL http://website.com/Subfolder even work? I think this doesn't work as the file needs to be open when writing the formula. And since it cannot be opened in an URL it needs to be downloaded first (even if it is only temporarily) and then the formula would link to that temporary file and not to the web URL. - Pᴇʜ
Yes it does without the cell reference and the address put in manually - charlie19981998

1 Answers

-1
votes

You can build a complete string from parts using & operator

A1: 100
B1: ="prefix" & A1 & "postfix"

B1 result: prefix100postfix