software

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 quot;educated guessquot; : You can't quot;buildquot; a formula by concatenating a bunch
of expressions!

Biff

quot;Jonreyquot; gt; wrote in message
...
gt;I am compiling data from several different, variable, worksheets. I am
gt;using
gt; concatenate to add the name of each worksheet into a formula to pull the
gt; appropriate data. However, it is returning the text of the formula instead
gt; of
gt; the results of the formula. How can I make it calculate the result instead
gt; of
gt; 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 :-)
------------------------------*------------------------------*----------------

quot;Jonreyquot; gt; wrote in message
...
gt;I am compiling data from several different, variable, worksheets. I am
gt;using
gt; concatenate to add the name of each worksheet into a formula to pull the
gt; appropriate data. However, it is returning the text of the formula instead
gt; of
gt; the results of the formula. How can I make it calculate the result instead
gt; of
gt; 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=quot;quot;,quot;quot;,'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

quot;Ken Wrightquot; wrote:

gt; You would need to use INDIRECT() to convert your text expression into a
gt; formula, but it won't work if you are referencing a closed workbook.
gt;
gt; --
gt; Regards
gt; Ken....................... Microsoft MVP - Excel
gt; Sys Spec - Win XP Pro / XL 97/00/02/03
gt;
gt; ------------------------------Â*------------------------------Â*----------------
gt; It's easier to beg forgiveness than ask permission :-)
gt; ------------------------------Â*------------------------------Â*----------------
gt;
gt; quot;Jonreyquot; gt; wrote in message
gt; ...
gt; gt;I am compiling data from several different, variable, worksheets. I am
gt; gt;using
gt; gt; concatenate to add the name of each worksheet into a formula to pull the
gt; gt; appropriate data. However, it is returning the text of the formula instead
gt; gt; of
gt; gt; the results of the formula. How can I make it calculate the result instead
gt; gt; of
gt; gt; simply displaying the formula text?
gt;
gt;
gt;

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! lt;bggt;

Biff

quot;Jonreyquot; gt; wrote in message
...
gt; Biff,
gt; Your assumption was correct. We send out a survey and are trying to
gt; automatically compile the results. All returned surveys are saved to the
gt; same
gt; directory with different names. The formulas (with different external cell
gt; references) concatenate like this:
gt; =IF($B7=quot;quot;,quot;quot;,'I:\NKP\Surveys\NKP Survey Feed Back\[NKP Survey NPPA
gt; 61-0602-02852.xls]Tabulation'!$W$4)
gt;
gt; Ken,
gt; I have tried to do it with INDIRECT as you suggested, with the new survey
gt; worksheet open but haven't been able to make it work.
gt;
gt; Any assistance would be greatly appreciated.
gt;
gt; Jonrey
gt;
gt; quot;Ken Wrightquot; wrote:
gt;
gt;gt; You would need to use INDIRECT() to convert your text expression into a
gt;gt; formula, but it won't work if you are referencing a closed workbook.
gt;gt;
gt;gt; --
gt;gt; Regards
gt;gt; Ken....................... Microsoft MVP - Excel
gt;gt; Sys Spec - Win XP Pro / XL 97/00/02/03
gt;gt;
gt;gt; ------------------------------*------------------------------*----------------
gt;gt; It's easier to beg forgiveness than ask permission :-)
gt;gt; ------------------------------*------------------------------*----------------
gt;gt;
gt;gt; quot;Jonreyquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I am compiling data from several different, variable, worksheets. I am
gt;gt; gt;using
gt;gt; gt; concatenate to add the name of each worksheet into a formula to pull
gt;gt; gt; the
gt;gt; gt; appropriate data. However, it is returning the text of the formula
gt;gt; gt; instead
gt;gt; gt; of
gt;gt; gt; the results of the formula. How can I make it calculate the result
gt;gt; gt; instead
gt;gt; gt; of
gt;gt; gt; simply displaying the formula text?
gt;gt;
gt;gt;
gt;gt;
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=quot;quot;,quot;quot;,'I:\NKP\Surveys\NKP Survey Feed Back\[NKP Survey
NAOCE.xls]Tabulation'!$W$4)

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

etc.

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

=IF($B7=quot;quot;,quot;quot;,'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-021301111
CETC 22-0603-0386911111 10
NAOCE 22-0606-0378411111111
NPPA 61-0602-028521011 10Jon

quot;Biffquot; wrote:

gt; Ok, so what exactly do you need?
gt;
gt; My guess is that you have the filename in a cell and want to reference that
gt; cell in the formula? Need details!
gt;
gt; Side note: man, I hate long filenames and paths! lt;bggt;
gt;
gt; Biff
gt;
gt; quot;Jonreyquot; gt; wrote in message
gt; ...
gt; gt; Biff,
gt; gt; Your assumption was correct. We send out a survey and are trying to
gt; gt; automatically compile the results. All returned surveys are saved to the
gt; gt; same
gt; gt; directory with different names. The formulas (with different external cell
gt; gt; references) concatenate like this:
gt; gt; =IF($B7=quot;quot;,quot;quot;,'I:\NKP\Surveys\NKP Survey Feed Back\[NKP Survey NPPA
gt; gt; 61-0602-02852.xls]Tabulation'!$W$4)
gt; gt;
gt; gt; Ken,
gt; gt; I have tried to do it with INDIRECT as you suggested, with the new survey
gt; gt; worksheet open but haven't been able to make it work.
gt; gt;
gt; gt; Any assistance would be greatly appreciated.
gt; gt;
gt; gt; Jonrey
gt; gt;
gt; gt; quot;Ken Wrightquot; wrote:
gt; gt;
gt; gt;gt; You would need to use INDIRECT() to convert your text expression into a
gt; gt;gt; formula, but it won't work if you are referencing a closed workbook.
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; Regards
gt; gt;gt; Ken....................... Microsoft MVP - Excel
gt; gt;gt; Sys Spec - Win XP Pro / XL 97/00/02/03
gt; gt;gt;
gt; gt;gt; ------------------------------Â*------------------------------Â*----------------
gt; gt;gt; It's easier to beg forgiveness than ask permission :-)
gt; gt;gt; ------------------------------Â*------------------------------Â*----------------
gt; gt;gt;
gt; gt;gt; quot;Jonreyquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt;I am compiling data from several different, variable, worksheets. I am
gt; gt;gt; gt;using
gt; gt;gt; gt; concatenate to add the name of each worksheet into a formula to pull
gt; gt;gt; gt; the
gt; gt;gt; gt; appropriate data. However, it is returning the text of the formula
gt; gt;gt; gt; instead
gt; gt;gt; gt; of
gt; gt;gt; gt; the results of the formula. How can I make it calculate the result
gt; gt;gt; gt; instead
gt; gt;gt; gt; of
gt; gt;gt; gt; simply displaying the formula text?
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;


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: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=527258You 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.

PetePete_UK wrote...
....
gt;can be simplified to:
gt;
gt;[NKP Survey NAOCE.xls]'Tabulation'!
gt;
gt;Dave Peterson reminded me of this a few weeks ago.
gt;
gt;I don't think you need the apostrophe around the filename - just the
gt;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