Cell value/filename question...

[AlexJ]

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.

[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

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

[AlexJ]

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!

[Dave Peterson]

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