software

I have inherited a problem. I work with two multiple worksheet workbooks.
The first workbook is the quot;masterquot; into which data from the second is pulled.

In the formula pasted below, the phrase quot;Small Growthquot; is one of forty
distinct worksheet names in the second workbook quot;expense stats.xls.quot; Each
worksheet is built identically to the other.

=VLOOKUP($A$3,'C:\My Documents\[expense stats.xls]Small
Growth'!$C$2:$G$2000,5,FALSE).

The formula works but the quot;Small Growthquot; expression must be manually
changed. I need a way to automate this. The names originate as values in
$T4 of the INPUT worksheet in workbook one (the master).Check out the INDIRECT formula. It would allow you to construct the text
inside the formula dynamically, so that you could pull the names of the
different worksheets from an array where you'd keep them, for example.

quot;JackieWquot; wrote:

gt; I have inherited a problem. I work with two multiple worksheet workbooks.
gt; The first workbook is the quot;masterquot; into which data from the second is pulled.
gt;
gt; In the formula pasted below, the phrase quot;Small Growthquot; is one of forty
gt; distinct worksheet names in the second workbook quot;expense stats.xls.quot; Each
gt; worksheet is built identically to the other.
gt;
gt; =VLOOKUP($A$3,'C:\My Documents\[expense stats.xls]Small
gt; Growth'!$C$2:$G$2000,5,FALSE).
gt;
gt; The formula works but the quot;Small Growthquot; expression must be manually
gt; changed. I need a way to automate this. The names originate as values in
gt; $T4 of the INPUT worksheet in workbook one (the master).
gt;

You coul use an INDIRECT referece in your VLOOKUP formula if quot;expense
stats.xlsquot; is also opened.
If not opened, you get a #REF error

HTH
--
AP

quot;JackieWquot; gt; a écrit dans le message de
...
gt; I have inherited a problem. I work with two multiple worksheet workbooks.
gt; The first workbook is the quot;masterquot; into which data from the second is
pulled.
gt;
gt; In the formula pasted below, the phrase quot;Small Growthquot; is one of forty
gt; distinct worksheet names in the second workbook quot;expense stats.xls.quot; Each
gt; worksheet is built identically to the other.
gt;
gt; =VLOOKUP($A$3,'C:\My Documents\[expense stats.xls]Small
gt; Growth'!$C$2:$G$2000,5,FALSE).
gt;
gt; The formula works but the quot;Small Growthquot; expression must be manually
gt; changed. I need a way to automate this. The names originate as values in
gt; $T4 of the INPUT worksheet in workbook one (the master).
gt;