Cell lookup

[dwoloszyk]

I create a monthly profit&loss file that after each month, I do a file SAVE
AS to create next months file copy, etc.
In the file, a Profit & loss sheet refers back to the same month of last
year. How do I create a reference so the when I create the SAVE AS file, it
looks back at the same month.
Right now, i have to go into each cell and change the file look back name...

i.e.='\\easfs\MonthendCorp\2005\[043005.xls]ConsYTD'!$B$6

How do I have the [043005.xls] change to [053105.xls] when I create the new
file?

[Miguel Zapico]

This is not an automated solution, but you can reduce the time of changing
cells one by one.
Go to menu Edit->Replace, click the options button to see more fields, and
select Workbook in the Within dropdown. Finally, in the "Find What:" put the
old workbook name ([043005.xls]), and in the "Replace With:" put the new
workbook name ([053105.xls]).
This should change all the references in the workbook to the new file.

Hope this helps,
Miguel.

"dwoloszyk" wrote:

> I create a monthly profit&loss file that after each month, I do a file SAVE
> AS to create next months file copy, etc.
> In the file, a Profit & loss sheet refers back to the same month of last
> year. How do I create a reference so the when I create the SAVE AS file, it
> looks back at the same month.
> Right now, i have to go into each cell and change the file look back name...
>
> i.e.='\\easfs\MonthendCorp\2005\[043005.xls]ConsYTD'!$B$6
>
> How do I have the [043005.xls] change to [053105.xls] when I create the new
> file?
>
>

[dwoloszyk]

Is it possible to refer to the "043005.xls as a defined name and put the
defined name in the formula? Then we could update the defined cell and the
formulas would then update...??
I tried to find the string that excel would accept and couldn't figure out...

"Miguel Zapico" wrote:

> This is not an automated solution, but you can reduce the time of changing
> cells one by one.
> Go to menu Edit->Replace, click the options button to see more fields, and
> select Workbook in the Within dropdown. Finally, in the "Find What:" put the
> old workbook name ([043005.xls]), and in the "Replace With:" put the new
> workbook name ([053105.xls]).
> This should change all the references in the workbook to the new file.
>
> Hope this helps,
> Miguel.
>
> "dwoloszyk" wrote:
>
> > I create a monthly profit&loss file that after each month, I do a file SAVE
> > AS to create next months file copy, etc.
> > In the file, a Profit & loss sheet refers back to the same month of last
> > year. How do I create a reference so the when I create the SAVE AS file, it
> > looks back at the same month.
> > Right now, i have to go into each cell and change the file look back name...
> >
> > i.e.='\\easfs\MonthendCorp\2005\[043005.xls]ConsYTD'!$B$6
> >
> > How do I have the [043005.xls] change to [053105.xls] when I create the new
> > file?
> >
> >

[Miguel Zapico]

It is possible, using the function INDIRECT, or if your workbooks will
usually be closed, INDIRECT.EXT. Look at the help for the INDIRECT function,
check if it you can use it in your workbook, and if so, you may want to
search google for the INDIRECT.EXT function, as it is available as a separate
download.

Miguel.

"dwoloszyk" wrote:

> Is it possible to refer to the "043005.xls as a defined name and put the
> defined name in the formula? Then we could update the defined cell and the
> formulas would then update...??
> I tried to find the string that excel would accept and couldn't figure out...
>
> "Miguel Zapico" wrote:
>
> > This is not an automated solution, but you can reduce the time of changing
> > cells one by one.
> > Go to menu Edit->Replace, click the options button to see more fields, and
> > select Workbook in the Within dropdown. Finally, in the "Find What:" put the
> > old workbook name ([043005.xls]), and in the "Replace With:" put the new
> > workbook name ([053105.xls]).
> > This should change all the references in the workbook to the new file.
> >
> > Hope this helps,
> > Miguel.
> >
> > "dwoloszyk" wrote:
> >
> > > I create a monthly profit&loss file that after each month, I do a file SAVE
> > > AS to create next months file copy, etc.
> > > In the file, a Profit & loss sheet refers back to the same month of last
> > > year. How do I create a reference so the when I create the SAVE AS file, it
> > > looks back at the same month.
> > > Right now, i have to go into each cell and change the file look back name...
> > >
> > > i.e.='\\easfs\MonthendCorp\2005\[043005.xls]ConsYTD'!$B$6
> > >
> > > How do I have the [043005.xls] change to [053105.xls] when I create the new
> > > file?
> > >
> > >

[Sanjeev]

Dear dwoloszyk,

You can use , Edit -> Links -->
Select the Name of file want to change and press Change source ,
Now select the new file.

It is very very simple & you not lose your control on formulae.

Thanks

"Miguel Zapico" wrote:

> It is possible, using the function INDIRECT, or if your workbooks will
> usually be closed, INDIRECT.EXT. Look at the help for the INDIRECT function,
> check if it you can use it in your workbook, and if so, you may want to
> search google for the INDIRECT.EXT function, as it is available as a separate
> download.
>
> Miguel.
>
> "dwoloszyk" wrote:
>
> > Is it possible to refer to the "043005.xls as a defined name and put the
> > defined name in the formula? Then we could update the defined cell and the
> > formulas would then update...??
> > I tried to find the string that excel would accept and couldn't figure out...
> >
> > "Miguel Zapico" wrote:
> >
> > > This is not an automated solution, but you can reduce the time of changing
> > > cells one by one.
> > > Go to menu Edit->Replace, click the options button to see more fields, and
> > > select Workbook in the Within dropdown. Finally, in the "Find What:" put the
> > > old workbook name ([043005.xls]), and in the "Replace With:" put the new
> > > workbook name ([053105.xls]).
> > > This should change all the references in the workbook to the new file.
> > >
> > > Hope this helps,
> > > Miguel.
> > >
> > > "dwoloszyk" wrote:
> > >
> > > > I create a monthly profit&loss file that after each month, I do a file SAVE
> > > > AS to create next months file copy, etc.
> > > > In the file, a Profit & loss sheet refers back to the same month of last
> > > > year. How do I create a reference so the when I create the SAVE AS file, it
> > > > looks back at the same month.
> > > > Right now, i have to go into each cell and change the file look back name...
> > > >
> > > > i.e.='\\easfs\MonthendCorp\2005\[043005.xls]ConsYTD'!$B$6
> > > >
> > > > How do I have the [043005.xls] change to [053105.xls] when I create the new
> > > > file?
> > > >
> > > >