Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell lookup
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell lookup
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell lookup
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell lookup
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell lookup
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
How can I lookup a cell that's to the left? | Excel Discussion (Misc queries) | |||
VLOOKUP CONDITION TO RETURN BLANK CELL WHERE LOOKUP VALUE IS NOT IN TABLE ARRAY | Excel Worksheet Functions | |||
Return cell reference of lookup value | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |