Excel question

Grrrrr · 13 May 2009 at 11:44

I suspect a macro is the only way to do this but hopefully an enlightened OcUKer will know of a way to do it without marcos...

As you can see in the image i'm trying to pull the value from cell A1 in each of the spreadsheets for the respective countries.

Is there a way to modify the formula so instead of hardwiring the country names in, it pulls the country name from column A ?

Thanks in advance

dazzerd · 13 May 2009 at 13:58

Yes you can use the indirect command, however if you are using excel 2003 the books you are linking too will need to be open to pull back the target data, I assume this is the same if you are using 2007.

You need to nest the indirect statement in your formula:

So something like this:
=indirect("c:\"&A1&"\"&"["a1&"_Spreadsheet.xlsx]Sheet1'!$A$1")

You will have to refine it a little probably using indirect can sometimes have an odd effect with apostrophes. Note that the text within the speach marks is fixed.

As above however the indirect function wont pull the data from an unopened source so probably isnt worth the hassle. The best way would be to ty and move the target files within the main workbook.

Grrrrr · 13 May 2009 at 14:54

Thanks, that will be incredibly useful for future spreadsheets where i just need to pull values through on a one time basis. (I wish i'd asked this a long time ago to be honest)

In this instance though i need the formula to be able to update the values from the spreadsheet it is linking to. Using INDIRECT, as you said, requires the linked spreadsheet to be open otherwise it pulls through #ref! errors.

dazzerd · 13 May 2009 at 19:56

No problem saves me hours at work!

Dolph · 13 May 2009 at 20:40

Only way I can think to do it without using VBA is to define the string in another cell (using one of the string manipulation functions such as concatenate) and then pass the result to the linking field, but I'm not sure if it would work.

Eg in B1 have =concatenate("C:\"&A1&"\"&A1&"spreadsheet.xlsx") then in C1 have it pull the reference string from b1, but I'm not at all sure it will work. I'd do it in VBA.