Cell value/filename question...
 

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
------------------------------------------------------------------------
AlexJ's Profile: http://www.excelforum.com/member.php...o&userid=27247
View this thread: http://www.excelforum.com/showthread...hreadid=467559

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

AlexJ wrote:

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
------------------------------------------------------------------------
AlexJ's Profile: http://www.excelforum.com/member.php...o&userid=27247
View this thread: http://www.excelforum.com/showthread...hreadid=467559

--

Dave Peterson

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!


--
AlexJ
------------------------------------------------------------------------
AlexJ's Profile: http://www.excelforum.com/member.php...o&userid=27247
View this thread: http://www.excelforum.com/showthread...hreadid=467559

I've never used that addin.

Maybe someone else can jump in.

AlexJ wrote:

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!

--
AlexJ
------------------------------------------------------------------------
AlexJ's Profile: http://www.excelforum.com/member.php...o&userid=27247
View this thread: http://www.excelforum.com/showthread...hreadid=467559

--

Dave Peterson