Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing Data in Closed Workbooks
Hi -
I'm using Excel 2000, Win XP. A colleague has a workbook which accesses data from a secondary work book. Every so often, she has to update the data in the Main Workbook with data from a different secondary workbook. For example, the Main workbook is referencing data from "Workbook01.xls" this week. Next week, she wants to reference data from "Workbook02.xls". The only change in the workbook names is from 01 to 02. She will have about 50 workbooks throughout the year. Rather than changing each individual reference in the Main Workbook, she wants to be able to change just one cell from '01 to '02, and have the cells update with data from the 02 workbook. The data in each of the secondary workbooks is arranged similarly, so changing the workbook name will bring in the correct data. I could write VBA code to do this, but was wondering if this could be accomplished using formulas and no VBA. She has numerous workbooks with references to closed workbooks. I would not want to write VBA for each of her workbooks. Thanks in advance. Steve |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing Data in Closed Workbooks
Steve,
If I'm understanding your issue, I believe she can click on "Edit | Links | Change Source" and update the source file references throughout the workbook in one go. HTH, Jeff "Steve" wrote in message ... Hi - I'm using Excel 2000, Win XP. A colleague has a workbook which accesses data from a secondary work book. Every so often, she has to update the data in the Main Workbook with data from a different secondary workbook. For example, the Main workbook is referencing data from "Workbook01.xls" this week. Next week, she wants to reference data from "Workbook02.xls". The only change in the workbook names is from 01 to 02. She will have about 50 workbooks throughout the year. Rather than changing each individual reference in the Main Workbook, she wants to be able to change just one cell from '01 to '02, and have the cells update with data from the 02 workbook. The data in each of the secondary workbooks is arranged similarly, so changing the workbook name will bring in the correct data. I could write VBA code to do this, but was wondering if this could be accomplished using formulas and no VBA. She has numerous workbooks with references to closed workbooks. I would not want to write VBA for each of her workbooks. Thanks in advance. Steve |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing Data in Closed Workbooks
One way you could do it is with Edit | Replace:
Find What: 01.xls Replace with: 02.xls Replace All This would be quite easy to put in a macro if you stored the number you changed to in a cell, so that next time you would know to change from 02 to the next number. You could then assign the macro to a button, so that your colleague would only have to click the button to look at the most recent file. Hope this helps. Pete On Oct 25, 7:49 pm, "Steve" wrote: Hi - I'm using Excel 2000, Win XP. A colleague has a workbook which accesses data from a secondary work book. Every so often, she has to update the data in the Main Workbook with data from a different secondary workbook. For example, the Main workbook is referencing data from "Workbook01.xls" this week. Next week, she wants to reference data from "Workbook02.xls". The only change in the workbook names is from 01 to 02. She will have about 50 workbooks throughout the year. Rather than changing each individual reference in the Main Workbook, she wants to be able to change just one cell from '01 to '02, and have the cells update with data from the 02 workbook. The data in each of the secondary workbooks is arranged similarly, so changing the workbook name will bring in the correct data. I could write VBA code to do this, but was wondering if this could be accomplished using formulas and no VBA. She has numerous workbooks with references to closed workbooks. I would not want to write VBA for each of her workbooks. Thanks in advance. Steve |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing Data in Closed Workbooks
Thanks Jeff. This may work well for this application as she has several
different links in the workbook. Some will be changed one day, and others will not. She can pick and choose the file to link to, knowing that all links to that file in the main workbook will change, and links to other files in the main workbook will not. "Jeffrey W. Smith" wrote in message ... Steve, If I'm understanding your issue, I believe she can click on "Edit | Links | Change Source" and update the source file references throughout the workbook in one go. HTH, Jeff "Steve" wrote in message ... Hi - I'm using Excel 2000, Win XP. A colleague has a workbook which accesses data from a secondary work book. Every so often, she has to update the data in the Main Workbook with data from a different secondary workbook. For example, the Main workbook is referencing data from "Workbook01.xls" this week. Next week, she wants to reference data from "Workbook02.xls". The only change in the workbook names is from 01 to 02. She will have about 50 workbooks throughout the year. Rather than changing each individual reference in the Main Workbook, she wants to be able to change just one cell from '01 to '02, and have the cells update with data from the 02 workbook. The data in each of the secondary workbooks is arranged similarly, so changing the workbook name will bring in the correct data. I could write VBA code to do this, but was wondering if this could be accomplished using formulas and no VBA. She has numerous workbooks with references to closed workbooks. I would not want to write VBA for each of her workbooks. Thanks in advance. Steve |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing Data in Closed Workbooks
Thanks Pete. If it becomes necessary to use VBA, I already have very simple
code to Edit & Replace. I am trying to avoid this, as it may require a little support down the road. Thanks again. "Pete_UK" wrote in message oups.com... One way you could do it is with Edit | Replace: Find What: 01.xls Replace with: 02.xls Replace All This would be quite easy to put in a macro if you stored the number you changed to in a cell, so that next time you would know to change from 02 to the next number. You could then assign the macro to a button, so that your colleague would only have to click the button to look at the most recent file. Hope this helps. Pete On Oct 25, 7:49 pm, "Steve" wrote: Hi - I'm using Excel 2000, Win XP. A colleague has a workbook which accesses data from a secondary work book. Every so often, she has to update the data in the Main Workbook with data from a different secondary workbook. For example, the Main workbook is referencing data from "Workbook01.xls" this week. Next week, she wants to reference data from "Workbook02.xls". The only change in the workbook names is from 01 to 02. She will have about 50 workbooks throughout the year. Rather than changing each individual reference in the Main Workbook, she wants to be able to change just one cell from '01 to '02, and have the cells update with data from the 02 workbook. The data in each of the secondary workbooks is arranged similarly, so changing the workbook name will bring in the correct data. I could write VBA code to do this, but was wondering if this could be accomplished using formulas and no VBA. She has numerous workbooks with references to closed workbooks. I would not want to write VBA for each of her workbooks. Thanks in advance. Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cross referencing data across different workbooks | Excel Discussion (Misc queries) | |||
Keeping data once referenced workbooks are closed. | Excel Discussion (Misc queries) | |||
SAVING DATA TO CLOSED WORKBOOKS | Excel Worksheet Functions | |||
Referencing cells in closed workbooks | Excel Discussion (Misc queries) | |||
Data from closed workbooks (pull func, indirect.ext, etc ....) | Excel Worksheet Functions |