Cell value/filename question...

[AlexJ]

I'm using forumulas like these to extract data from several differen
files: =SUM('Coins\[coin value.xls]Group Costs'!$A$3). I have a colum
that has all the filenames listed, but it's a huge hassle to go throug
and change the formula each time I start a new row (keyword). What
need is something like this: =SUM('Coins\[A3.xls]Group Costs'!$A$3).
As you can see, I'm trying to get Excel to think that A3 really equal
'coin value'. I know this is possible with concatenation (it would tak
a few extra steps) but I need all the data right in front of me as I'
working.

Does anyone know of any solution to my dilemma?

Thank you

 

[Dave Peterson]

If you put the workbook name into another cell, you could use =indirect() to
build the reference to the other workbook.

The bad news is that =indirect() will return an error if that other workbook
isn't open.

Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

I'm using forumulas like these to extract data from several different
files: =SUM('Coins\[coin value.xls]Group Costs'!$A$3). I have a column
that has all the filenames listed, but it's a huge hassle to go through
and change the formula each time I start a new row (keyword). What I
need is something like this: =SUM('Coins\[A3.xls]Group Costs'!$A$3). -
As you can see, I'm trying to get Excel to think that A3 really equals
'coin value'. I know this is possible with concatenation (it would take
a few extra steps) but I need all the data right in front of me as I'm
working.

Does anyone know of any solution to my dilemma?

Thank you.

 

[AlexJ]

Thanks for the reply, David.

I just found out about the indirect.ext addin (which I already hav
installed, coincidentally enough). However, I can't seem to get it t
work right. Let's say I have the name of a worksheet in A1, we'll cal
it "coin value.xls." Here's what I'm using to pull the data from tha
worksheet:

'Keyword Groups\Coins\[coin value.xls]Group Costs'!$A$3

What I'm looking for is something like this:

'Keyword Groups\Coins\[A1]Group Costs'!$A$3

This will save me an incredible amount of time . Any suggestions?

Thanks for your help

 

[Dave Peterson]

I've never used that addin.

Maybe someone else can jump in.

Thanks for the reply, David.

I just found out about the indirect.ext addin (which I already have
installed, coincidentally enough). However, I can't seem to get it to
work right. Let's say I have the name of a worksheet in A1, we'll call
it "coin value.xls." Here's what I'm using to pull the data from that
worksheet:

'Keyword Groups\Coins\[coin value.xls]Group Costs'!$A$3

What I'm looking for is something like this:

'Keyword Groups\Coins\[A1]Group Costs'!$A$3

This will save me an incredible amount of time . Any suggestions?

Thanks for your help!