Assigning a Worksheet Name to a Cell
 

Example, I want to add a new monthly data worksheet to by workbook and pull
all the present and past monthly data for a specific client. Currently the
data is pulled through multiple vlookup functions and everytime I add a new
sheet, I continuously need to manually change the functions worksheet
reference. Is it possible to assign a cell with the new worksheet name and
link that assigned cell to that function without manually changing each
vlookup function?

Assigning a Worksheet Name to a Cell
 

Have you ever used the MATCH, ISERROR, ADDRESS and INDIRECT functions?

If need be, you can setup a particular cell as the cell to contain the name
of the worksheet, which would then allow you to change it in just one place
rather than in multiple places. There are of course cautions to take place,
but I presume you know enough about some of the precautions like when you
get to changing things in various places, you can accidentally overlook some
location.

The formula can look something like:

=IF(ISERROR(MATCH(C134,INDIRECT(T1&"!A:A"),0)),"", INDIRECT(ADDRESS(MATCH(C134,INDIRECT(T1&"!A:A"),0) ,2,,,T1)))

Where T1 contains the name of the worksheet that you have added to the
workbook.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"bcw_now" wrote in message
...
Example, I want to add a new monthly data worksheet to by workbook and
pull
all the present and past monthly data for a specific client. Currently
the
data is pulled through multiple vlookup functions and everytime I add a
new
sheet, I continuously need to manually change the functions worksheet
reference. Is it possible to assign a cell with the new worksheet name
and
link that assigned cell to that function without manually changing each
vlookup function?

Assigning a Worksheet Name to a Cell
 

Thanks Ronald. Another question, same type of premise. Is it possible to
assign a cell to a separate workbook. So for example some times workbooks are
moved to other drives or folders and once that happens, the workbook with the
functions that I'm working on does not change the path to the moved workbook.
So if I could assign a cell to a workbook or path it would be much easier.

"Ronald R. Dodge, Jr." wrote:

Have you ever used the MATCH, ISERROR, ADDRESS and INDIRECT functions?

If need be, you can setup a particular cell as the cell to contain the name
of the worksheet, which would then allow you to change it in just one place
rather than in multiple places. There are of course cautions to take place,
but I presume you know enough about some of the precautions like when you
get to changing things in various places, you can accidentally overlook some
location.

The formula can look something like:

=IF(ISERROR(MATCH(C134,INDIRECT(T1&"!A:A"),0)),"", INDIRECT(ADDRESS(MATCH(C134,INDIRECT(T1&"!A:A"),0) ,2,,,T1)))

Where T1 contains the name of the worksheet that you have added to the
workbook.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"bcw_now" wrote in message
...
Example, I want to add a new monthly data worksheet to by workbook and
pull
all the present and past monthly data for a specific client. Currently
the
data is pulled through multiple vlookup functions and everytime I add a
new
sheet, I continuously need to manually change the functions worksheet
reference. Is it possible to assign a cell with the new worksheet name
and
link that assigned cell to that function without manually changing each
vlookup function?

Assigning a Worksheet Name to a Cell
 

You can do that, but since such moves are often times done external to
Excel, such adjustments are not done automatically. The only time when
adjustments are done automatically are when all such workbooks linked to
each other are opened at the same time as the source workbook is saved to a
different location. Also note, while in the past, such calculations of
linked workbooks were suppose to happen regardless if the source workbooks
are open or not, the calculations in the target workbooks linked to the
source workbooks only take place when both the target and source workbooks
are opened on the same system.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"bcw_now" wrote in message
...
Thanks Ronald. Another question, same type of premise. Is it possible to
assign a cell to a separate workbook. So for example some times workbooks
are
moved to other drives or folders and once that happens, the workbook with
the
functions that I'm working on does not change the path to the moved
workbook.
So if I could assign a cell to a workbook or path it would be much easier.

"Ronald R. Dodge, Jr." wrote:

Have you ever used the MATCH, ISERROR, ADDRESS and INDIRECT functions?

If need be, you can setup a particular cell as the cell to contain the
name
of the worksheet, which would then allow you to change it in just one
place
rather than in multiple places. There are of course cautions to take
place,
but I presume you know enough about some of the precautions like when you
get to changing things in various places, you can accidentally overlook
some
location.

The formula can look something like:

=IF(ISERROR(MATCH(C134,INDIRECT(T1&"!A:A"),0)),"", INDIRECT(ADDRESS(MATCH(C134,INDIRECT(T1&"!A:A"),0) ,2,,,T1)))

Where T1 contains the name of the worksheet that you have added to the
workbook.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"bcw_now" wrote in message
...
Example, I want to add a new monthly data worksheet to by workbook and
pull
all the present and past monthly data for a specific client. Currently
the
data is pulled through multiple vlookup functions and everytime I add a
new
sheet, I continuously need to manually change the functions worksheet
reference. Is it possible to assign a cell with the new worksheet name
and
link that assigned cell to that function without manually changing each
vlookup function?