I am compiling data from several different, variable, worksheets. I am using
concatenate to add the name of each worksheet into a formula to pull the
appropriate data. However, it is returning the text of the formula instead of
the results of the formula. How can I make it calculate the result instead of
simply displaying the formula text?

Hi!

What's your formula look like?

An "educated guess" : You can't "build" a formula by concatenating a bunch
of expressions!

Biff

"Jonrey" wrote in message
...
I am compiling data from several different, variable, worksheets. I am
using
concatenate to add the name of each worksheet into a formula to pull the
appropriate data. However, it is returning the text of the formula instead
of
the results of the formula. How can I make it calculate the result instead
of
simply displaying the formula text?

You would need to use INDIRECT() to convert your text expression into a
formula, but it won't work if you are referencing a closed workbook.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------

"Jonrey" wrote in message
...
I am compiling data from several different, variable, worksheets. I am
using
concatenate to add the name of each worksheet into a formula to pull the
appropriate data. However, it is returning the text of the formula instead
of
the results of the formula. How can I make it calculate the result instead
of
simply displaying the formula text?

Biff,
Your assumption was correct. We send out a survey and are trying to
automatically compile the results. All returned surveys are saved to the same
directory with different names. The formulas (with different external cell
references) concatenate like this:
=IF($B7="","",'I:\NKP\Surveys\NKP Survey Feed Back\[NKP Survey NPPA
61-0602-02852.xls]Tabulation'!$W$4)

Ken,
I have tried to do it with INDIRECT as you suggested, with the new survey
worksheet open but haven't been able to make it work.

Any assistance would be greatly appreciated.

Jonrey

"Ken Wright" wrote:

You would need to use INDIRECT() to convert your text expression into a
formula, but it won't work if you are referencing a closed workbook.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------

"Jonrey" wrote in message
...
I am compiling data from several different, variable, worksheets. I am
using
concatenate to add the name of each worksheet into a formula to pull the
appropriate data. However, it is returning the text of the formula instead
of
the results of the formula. How can I make it calculate the result instead
of
simply displaying the formula text?

Ok, so what exactly do you need?

My guess is that you have the filename in a cell and want to reference that
cell in the formula? Need details!

Side note: man, I hate long filenames and paths! <bg

Biff

"Jonrey" wrote in message
...
Biff,
Your assumption was correct. We send out a survey and are trying to
automatically compile the results. All returned surveys are saved to the
same
directory with different names. The formulas (with different external cell
references) concatenate like this:
=IF($B7="","",'I:\NKP\Surveys\NKP Survey Feed Back\[NKP Survey NPPA
61-0602-02852.xls]Tabulation'!$W$4)

Ken,
I have tried to do it with INDIRECT as you suggested, with the new survey
worksheet open but haven't been able to make it work.

Any assistance would be greatly appreciated.

Jonrey

"Ken Wright" wrote:

You would need to use INDIRECT() to convert your text expression into a
formula, but it won't work if you are referencing a closed workbook.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------

"Jonrey" wrote in message
...
I am compiling data from several different, variable, worksheets. I am
using
concatenate to add the name of each worksheet into a formula to pull
the
appropriate data. However, it is returning the text of the formula
instead
of
the results of the formula. How can I make it calculate the result
instead
of
simply displaying the formula text?

Yes, all of the file names are in the first column:
DIABET
CETC
NAOCE
NPPA
etc.

Columns B-AI each contain a response from the survey. The formula in each of
the response columns needs to refer to the file name in column A:

Column B:
=IF($B7="","",'I:\NKP\Surveys\NKP Survey Feed Back\[NKP Survey
NAOCE.xls]Tabulation'!$W$4)

=IF($B7="","",'I:\NKP\Surveys\NKP Survey Feed Back\[NKP Survey
NPPA.xls]Tabulation'!$W$4)

etc.

Column C:
=IF($B7="","",'I:\NKP\Surveys\NKP Survey Feed Back\[NKP Survey
NAOCE.xls]Tabulation'!$W$5)

=IF($B7="","",'I:\NKP\Surveys\NKP Survey Feed Back\[NKP Survey
NPPA.xls]Tabulation'!$W$5)

etc.

Make sense? The file name changes with each row and the survey response
reference changes with each column.

W4 W5 W6 W7

DIABET 17-0604-02130 1 1 1 1
CETC 22-0603-03869 11 1 11 10
NAOCE 22-0606-03784 11 11 11 11
NPPA 61-0602-02852 10 1 1 10


Jon

"Biff" wrote:

Ok, so what exactly do you need?

My guess is that you have the filename in a cell and want to reference that
cell in the formula? Need details!

Side note: man, I hate long filenames and paths! <bg

Biff

"Jonrey" wrote in message
...
Biff,
Your assumption was correct. We send out a survey and are trying to
automatically compile the results. All returned surveys are saved to the
same
directory with different names. The formulas (with different external cell
references) concatenate like this:
=IF($B7="","",'I:\NKP\Surveys\NKP Survey Feed Back\[NKP Survey NPPA
61-0602-02852.xls]Tabulation'!$W$4)

Ken,
I have tried to do it with INDIRECT as you suggested, with the new survey
worksheet open but haven't been able to make it work.

Any assistance would be greatly appreciated.

Jonrey

"Ken Wright" wrote:

You would need to use INDIRECT() to convert your text expression into a
formula, but it won't work if you are referencing a closed workbook.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------

"Jonrey" wrote in message
...
I am compiling data from several different, variable, worksheets. I am
using
concatenate to add the name of each worksheet into a formula to pull
the
appropriate data. However, it is returning the text of the formula
instead
of
the results of the formula. How can I make it calculate the result
instead
of
simply displaying the formula text?

Hi Jonrey,

Try this:

Add another column, or replace the filename in column A with:
''I:\NKP\Surveys\NKP Survey Feed Back\[NKP Survey
NAOCE.xls]Tabulation'!

Please note the additional apostrophe at the start (it is NOT a double
quote). The first apostrophe is used by Excel to identify it is a
label. The second apostrophe is part of the quote around the
path/filename/sheetname. This quote around this text is necessary
because there are spaces in your path (e.g. NKP Survey Feed Back). I'd
suggest always put these apostrophes in even if you don't see any
spaces. Please also note it ends with the exclamation mark.

Then place the cell reference in a different cell:
$W$4

Then build your formula using these two cells as per normal (using the
indirect formula).

This formula should then work when the source file is open.

Does this solve your problem?


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=527258

You should note that INDIRECT( ) only works with open files, and if the
file is open you don't need the drive and path information, i.e.

'I:\NKP\Surveys\NKP Survey Feed Back\[NKP Survey NAOCE.xls]Tabulation'!

can be simplified to:

[NKP Survey NAOCE.xls]'Tabulation'!

Dave Peterson reminded me of this a few weeks ago.

I don't think you need the apostrophe around the filename - just the
sheet name.

Hope this helps.

Pete

Pete_UK wrote...
....
can be simplified to:

[NKP Survey NAOCE.xls]'Tabulation'!

Dave Peterson reminded me of this a few weeks ago.

I don't think you need the apostrophe around the filename - just the
sheet name.

Don't 'think', test. The single quotes need to be around the workbook
and worksheet. Try it your way and see what results you get.

Thanks for the correction, Harlan.

Pete

Hi Pete - Why are you saying you don't need the drive and path
information? When the files are saved, closed and later reopened, how
do you know where the source files are, especially if in different
drives/directories?


Pete_UK Wrote:
You should note that INDIRECT( ) only works with open files, and if the
file is open you don't need the drive and path information, i.e.

'I:\NKP\Surveys\NKP Survey Feed Back\[NKP Survey
NAOCE.xls]Tabulation'!

can be simplified to:

[NKP Survey NAOCE.xls]'Tabulation'!

Dave Peterson reminded me of this a few weeks ago.

I don't think you need the apostrophe around the filename - just the
sheet name.

Hope this helps.

Pete


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=527258

He only said that it is not needed in the links, because the source files
will have to be open for the links wrapped with INDIRECT() to work, so the
path becomes irrelevant as far as the links are concerned. Knowing what it
is wrt knowing where the data comes from is a whole different subject.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"John James" wrote
in message ...

Hi Pete - Why are you saying you don't need the drive and path
information? When the files are saved, closed and later reopened, how
do you know where the source files are, especially if in different
drives/directories?


Pete_UK Wrote:
You should note that INDIRECT( ) only works with open files, and if the
file is open you don't need the drive and path information, i.e.

'I:\NKP\Surveys\NKP Survey Feed Back\[NKP Survey
NAOCE.xls]Tabulation'!

can be simplified to:

[NKP Survey NAOCE.xls]'Tabulation'!

Dave Peterson reminded me of this a few weeks ago.

I don't think you need the apostrophe around the filename - just the
sheet name.

Hope this helps.

Pete


--
John James
------------------------------------------------------------------------
John James's Profile:
http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=527258

John James wrote...
Hi Pete - Why are you saying you don't need the drive and path
information? When the files are saved, closed and later reopened, how
do you know where the source files are, especially if in different
drives/directories?
....

Excel won't allow you to open multiple workbooks with the same base
filename at the same time in the same Excel instance. If you had files
named c:\a\mine.xls and c:\b\mine.xls, you can't open them both at the
same time.

Why? Because Microsoft hasn't upgraded Excel's external reference
internals from the original version 1 functionality which only ran on
512K Macintoshes that had only one floppy disk drive that had a flat
file system. That meant there could only be one disk drive and no
subdirectories on that drive, so there could only ever be a single file
with any given filename that could be opened at any given time.

The presumably competent programmers on the Excel development team
haven't changed Excel to reflect that there are systems other than 22
year-old Mac 512Ks which may have multiple disk drives and hierarchical
file systems on any or all of those drives. That may be unfair to the
Excel developers; my own theory is that Microsoft's senior management
would much prefer separating their customers from their money while
having Microsoft employees do as little work as possible.

Anyway, Excel's external reference functionality maintains backwards
compatibility with hardware and OS that were obsolete once Mac SEs came
out in 1987. Since Excel hasn't been able to run on Mac 512Ks since,
what (i.e., I'm guessing), Excel 4 (early 1990s), for at least the last
13 years the external reference functionality of current versions of
Excel have been subject to the limitations of machines and OSs on which
those versions of Excel couldn't be run.

For comparison, Quattro Pro has allowed multiple files with the same
base filename to be open since version 1 back in 1990, and 123 has
allowed the same since Release 3.0 back in 1989. OpenOffice Calc and
Gnumeric also allow multiple files with the same base filename to be
open at the same time, but they're not MDI apps, so not entirely
comparable.

As far as this affects your problem, INDIRECT only works with *open*
files, only one file with a given base filename may be open at any
given time, so INDIRECT could only return values from that one open
file with the given base filename, so no purpose would be served
including the drive and directory in text references passed to
INDIRECT. If c:\a\mine.xls were open, INDIRECT could only return values
from it, not from c:\b\mine.xls even if you included the drive and
directory because when c:\a\mine.xls is open, c:\b\mine.xls can't be
open, and if it's not open, INDIRECT can't return anything from it.
Therefore, when using INDIRECT, there's no point including drive and
directory in the workbook reference. If the file you want is already
open, you can refer to it without using the drive and directory path.
If it's not open, you can't refer to it using INDIRECT.

Thanks, Harlan - that's more or less what I would have said <bg

Pete

What I meant was that whilst you can omit the full path, including it
can serve a very useful purpose, unless I'm mistaken. Interesting
discussion though.


John James Wrote:
Hi Pete - Why are you saying you don't need the drive and path
information? When the files are saved, closed and later reopened, how
do you know where the source files are, especially if in different
drives/directories?


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=527258

Hi Jonrey,
Have you found the answer to your question? I came to the board to pose the
exact same question.

All,
My version of what I believe is the same question:
I request co-workers to provide responses to questions I've placed in an
Excel file. Each co-worker saves their response in the same directory/folder
as my orginal file, adding their name to the end of the my filename. This
results in a new file for each co-worker.

I want to be able to retreive and consolidate reponses from each co-workers
into one spreadsheet (preferably without having to open each co-workers file.)

A1 of my consolidating file contains path and filename information:
H:\Survey\[Feb 2006 Survey

G5 contains a responders name: Bernard

Bernard has typed his response in cell H5 of the file I gave him, then saved
as a new file at H:\Survey\[Feb 2006 Survey-Bernard.xls]

I want to be able to populate cell H5 of my consolidating file with
Bernard's cell H5.

In cell A5 I've used this formula to produce the filename and cell I want to
retreive from:
=CONCATENATE("'",A1,"-",G5,".xls]Tab Name'!H5")
Results: H:\Survey\[Feb 2006 Survey-Bernard.xls]Tab Name'!H5

Question is: How do the results from this formula to then pull in the data
found at that location?

If in I place the formula =A5 in my cell H5, I only get 'H:\Survey\[Feb 2006
Survey-Bernard.xls]Tab Name'!H5 , not the information that Bernard provided
in H5 of his file.


Thanks in advance everyone!


"Jonrey" wrote:

I am compiling data from several different, variable, worksheets. I am using
concatenate to add the name of each worksheet into a formula to pull the
appropriate data. However, it is returning the text of the formula instead of
the results of the formula. How can I make it calculate the result instead of
simply displaying the formula text?

Hi AlyKate1960,

What you're expecting to be able to do is entirely logical and
reasonable. To my knowledge, however, indirect links can't be
recalculated without opening the other files. Can someone please prove
me wrong?

Better solution is use of direct links, because they update pretty
cleanly. Otherwise it looks like you'll need to open the source files
to either update the indirect links, or to transfer data into your
consolidation file. Equally as bad, after the files close, recalc will
cause errors to appear in the external indirect links in your
consolidation file.

:-(

AlyKate1960 Wrote:
Hi Jonrey,
... My version of what I believe is the same question:
I request co-workers to provide responses to questions I've placed in
an
Excel file. Each co-worker saves their response in the same
directory/folder
as my orginal file, adding their name to the end of the my filename.
This
results in a new file for each co-worker.

I want to be able to retreive and consolidate reponses from each
co-workers
into one spreadsheet (preferably without having to open each co-workers
file.) ...


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=527258

John James wrote...
What you're expecting to be able to do is entirely logical and
reasonable. To my knowledge, however, indirect links can't be
recalculated without opening the other files. Can someone please prove
me wrong?
....

You're not wrong. INDIRECT just can't work with text references to
closed files.

The workarounds are described in

http://groups.google.com/group/micro...443753560f0075

(or http://makeashorterlink.com/?B34B15DCC ).

Hey Jonrey & Alykate1960,


Think I've stumbled across what you both may be looking for:

http://www.j-walk.com/ss/excel/tips/tip82.htm

Good luck


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=527258

John James wrote...
Hey Jonrey & Alykate1960,

Think I've stumbled across what you both may be looking for:

http://www.j-walk.com/ss/excel/tips/tip82.htm

Evidently you missed the following caveat near the top of the document.

Note:
You cannot use this function in a worksheet formula.

Maybe I'm wrong, but it does seem the other people asking how to do
this want to do it from worksheet formulas.