Dynamically referencing file in concatenate

**coryg** · 10-05-2010, 11:57 AM

I am trying to use a concatenate statement that is currently working fine, but i would like to have the capability for dynamic changes in columns

=CONCATENATE('http://internet.com/workload/Spreadsheets/Team/[John Smith.xls]John Smith!G109," : ",'http://internet.com/

Instead of hard coding the name into the statement, i would like it to read whatever name i place in the cell

=CONCATENATE('http://internet.com/workload/
Spreadsheets/Team/[(C2).xls] ," : ",'http://internet.com/

That way we dont have to replace the statement every time, we can just add a column with a name whenever we add the corresponding spreadsheet.

Any Ideas?

**NBVC** · 10-05-2010, 12:22 PM

To do that you need to use the INDIRECT function.. but the INDIRECT function, unfortunately only works with Opened Source workbooks.

=CONCATENATE(INDIRECT("'http://internet.com/workload/Spreadsheets/Team/["&C2&".xls]"&C2&"'!G109")," : ","'http://internet.com/")

There is an addin available that allows use of closed workbooks. MOREFUNC.XLL

=CONCATENATE(INDIRECT.EXT("'http://internet.com/workload/Spreadsheets/Team/["&C2&".xls]"&C2&"'!G109")," : ","'http://internet.com/")

**coryg** · 10-05-2010, 02:12 PM

Thanks, that was very helpful. Unfortunately we have many people updating local sources and uploading them to sharepoint. We can't have everyone install the add-on. I would ask if there was another way around it, but due to the sharepoint setup, I am guessing, no

Thanks for your help