Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Referencing Question
So lets say I one file named January Sales.xls and I have a another file
named Yearly Sales.xls and I have formulas that reference cells in January Sales.xls and post them in Yearly Sales.xls. However once I start entering data for February sales I enter it into the January Sales.xls and just change the name to February Sales.xls. Is there a macro I can run that will change the name of one month and replace it with another based on what the current month is so that if it was Feb 3rd and I ran the macro it would change all formulas linking to January Sales.xls to linking February Sales.xls? And yes I know it would be easier to put this information in one workbook or at least different workbooks but this is not the exact situation just one that will simply my problem into easier understandable terms. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Referencing Question
If you have both files open and save the Jan file with a different name then
all the links in Yearly Sales will get updated to the new name. Of course you can write a macro to update all the references. "Chad" wrote: So lets say I one file named January Sales.xls and I have a another file named Yearly Sales.xls and I have formulas that reference cells in January Sales.xls and post them in Yearly Sales.xls. However once I start entering data for February sales I enter it into the January Sales.xls and just change the name to February Sales.xls. Is there a macro I can run that will change the name of one month and replace it with another based on what the current month is so that if it was Feb 3rd and I ran the macro it would change all formulas linking to January Sales.xls to linking February Sales.xls? And yes I know it would be easier to put this information in one workbook or at least different workbooks but this is not the exact situation just one that will simply my problem into easier understandable terms. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Referencing Question
Hi,
If you don't have both files open, then when you open the Yearly Sales.xls file, don't update. Choose Edit, Links, select the January Sales.xls file from the list and choose Change Source, pick the February Sales.xls file and all the formulas will adjust. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Chad" wrote: So lets say I one file named January Sales.xls and I have a another file named Yearly Sales.xls and I have formulas that reference cells in January Sales.xls and post them in Yearly Sales.xls. However once I start entering data for February sales I enter it into the January Sales.xls and just change the name to February Sales.xls. Is there a macro I can run that will change the name of one month and replace it with another based on what the current month is so that if it was Feb 3rd and I ran the macro it would change all formulas linking to January Sales.xls to linking February Sales.xls? And yes I know it would be easier to put this information in one workbook or at least different workbooks but this is not the exact situation just one that will simply my problem into easier understandable terms. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Referencing Question
"Sheeloo" wrote: If you have both files open and save the Jan file with a different name then all the links in Yearly Sales will get updated to the new name. Of course you can write a macro to update all the references. Thanks but the question is can I write a macro to auto update part of a file name to the current month. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Referencing Question
"Shane Devenshire" wrote: Hi, If you don't have both files open, then when you open the Yearly Sales.xls file, don't update. Choose Edit, Links, select the January Sales.xls file from the list and choose Change Source, pick the February Sales.xls file and all the formulas will adjust. Again Thanks for the input but I am looking for a MACRO and I repeat MACRO that would auto search for a month's name and then replace that with the current month's name even if it is part of a file name. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Referencing Question
Hi,
The manual command is Ctrl+H, ... You can turn on the recorder and record the steps and then look at the code. If you need help modifying the code that is recorded post it back here. The problem is code is very specific and you have given us very little detail. How are you spelling the month names Jan, J09, January, Jan-08... How do you want to trigger the macro, when the workbook opens, manually via a shortcut key, or a toolbar button. What happens when going from Dec -- Jan? Are there formulas with links on all sheets, do you have chart sheets and spreadsheet? Are there links in charts, in the defined names? What do you want to do if someone tries to run the macro twice, how will the code know if it is a new month or not. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Chad" wrote: "Shane Devenshire" wrote: Hi, If you don't have both files open, then when you open the Yearly Sales.xls file, don't update. Choose Edit, Links, select the January Sales.xls file from the list and choose Change Source, pick the February Sales.xls file and all the formulas will adjust. Again Thanks for the input but I am looking for a MACRO and I repeat MACRO that would auto search for a month's name and then replace that with the current month's name even if it is part of a file name. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Referencing Question
"Shane Devenshire" wrote: Hi, The manual command is Ctrl+H, ... You can turn on the recorder and record the steps and then look at the code. If you need help modifying the code that is recorded post it back here. The problem is code is very specific and you have given us very little detail. How are you spelling the month names Jan, J09, January, Jan-08... How do you want to trigger the macro, when the workbook opens, manually via a shortcut key, or a toolbar button. What happens when going from Dec -- Jan? Are there formulas with links on all sheets, do you have chart sheets and spreadsheet? Are there links in charts, in the defined names? What do you want to do if someone tries to run the macro twice, how will the code know if it is a new month or not. Cheers, Shane Devenshire The month is always spelled out as a part of the file name such as June Sale.xls. I would like it to do this when you open the worksheet. From Dec it should roll over to Jan since years are not listed. It is not charts just numbers. The code should check for a month in the formulas then check if that month is current month if so then nothing if not the current month then replace with current month. With that last part if the macro was ran twice nothing would happen. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced chart question | Charts and Charting in Excel | |||
Question about Advanced Filter | Excel Discussion (Misc queries) | |||
Advanced Question (that I really can't describe!) | Excel Worksheet Functions | |||
Advanced Filter Question | Excel Worksheet Functions | |||
Advanced filter question | Excel Discussion (Misc queries) |