Assigning a Worksheet Name to a Cell

[bcw_now]

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?

 

[Ronald R. Dodge, Jr.]

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]

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.]

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

 

[Ronald R. Dodge, Jr.]

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