Excel 2003 - VBA to substitute value from cell in formula

**Belgarath75** · 01-08-2013, 09:09 AM

I am trying to work out if it is possible to implement the following to save me from performing a routine manually.

History

I have a spreadsheet that i use to perform somes checks against data held on Sharepoint. The item number is found using the following formula

='http://intranet/commercial/Product Vault/[item1_AS.XLS]Sheet1'!$C$12

My question is, is it possible to have vba substitute the 'item1' part of the formula for a value held in a particular cell. as an example, cell A1 contains the following : Testing so when the VBA runs, it finds the value that is contained within cell A1, and then in cell B1 inputs the following formula

='http://intranet/commercial/Product Vault/[Testing_AS.XLS]Sheet1'!$C$12

This would then allow me to amend the list in the column A and have the VBA return the values as required.

there are some other minor changes that need to be made to the formula depending on the information I need to return, but whilst trying to pick up VBA i am trying to keep my questions to a minimum, in order to learn more for myself.

The above seems to have me stumped though.

**TMS** · 01-08-2013, 09:14 AM

You don't really need VBA for that, you could use INDIRECT.

=INDIRECT("'http://intranet/commercial/Product Vault/[" & A1 & "_AS.XLS]Sheet1'!$C$12")

Regards, TMS

**Belgarath75** · 01-08-2013, 10:24 AM

Thanks for the quick response. I shall input the formula and hopefully all is well.

Am unfamiliar with the INDIRECT function so i have learnt something extra today

**Belgarath75** · 01-08-2013, 10:32 AM

Strange, it keeps throwing up a #ref error.

is there a formatting or punctuation mark i may need to add to get this to work

**TMS** · 01-08-2013, 11:10 AM

Put a working formula into cell B1. Then put whatever comes after the square brackets in cell A1. Replace whatever you put in cell A1 with " & A1 & ". Put =INDIRECT(" at the beginning and ") the end. With a bit of luck the working formula will still work. Then you can change the value in A1 with another valid location and it should still work.

Regards, TMS

**Belgarath75** · 01-23-2013, 06:51 AM

Just wondering if you were aware of any reasons that using the method described would throw an error if it was being asked to look at a file on SharePoint.

It keeps returning the error that it was unable to download the information. However, when i input the formula the old way, its returns the value required.

If not, i shall put it down as a 'cant be done task' and continue to input it manually.

However, thanks for bringing the INDIRECT function to my attention as it looks like a helpful function