| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro need to update the workbook
Hi, All
Basically, I have two workbooks and each of them contains a single worksheet. The first worksheet namely Updated has 8 columns, while the other worksheet Live has 16 columns (8 of them are same with Update, rest are different). However, both of them have a same indicate column Equipment ID. What I need is to use the Updated sheet to update the Live one. That is whenever, theres a change in the Equipment ID of Update I need the changes .including all other 8 columns, to be reflected on the Live sheet, no matter its an addition, deletion or just change of value. Did I make myself clear? Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro need to update the workbook
You've made yourself clear, but excel isn't really good at doing this kind of
thing. You may want to consider putting the data in one worksheet, add a indicator column (Live or not-Live), then use data|filter|autofilter to show the data you want. x6v87qe wrote: Hi, All Basically, I have two workbooks and each of them contains a single worksheet. The first worksheet namely Updated has 8 columns, while the other worksheet Live has 16 columns (8 of them are same with Update, rest are different). However, both of them have a same indicate column Equipment ID. What I need is to use the Updated sheet to update the Live one. That is whenever, theres a change in the Equipment ID of Update I need the changes .including all other 8 columns, to be reflected on the Live sheet, no matter its an addition, deletion or just change of value. Did I make myself clear? Thanks -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro need to update the workbook
The best approach is to simply don't use your "Updated" workbook: make any changes, additions, or
deletions directly in the "Live" workbook. Having data reside in two places is a recipe for corruption. HTH, Bernie MS Excel MVP "x6v87qe" wrote in message ... Hi, All Basically, I have two workbooks and each of them contains a single worksheet. The first worksheet namely "Updated" has 8 columns, while the other worksheet "Live" has 16 columns (8 of them are same with "Update", rest are different). However, both of them have a same indicate column "Equipment ID". What I need is to use the "Updated" sheet to update the "Live" one. That is whenever, there's a change in the "Equipment ID" of "Update" I need the changes .including all other 8 columns, to be reflected on the "Live" sheet, no matter it's an addition, deletion or just change of value. Did I make myself clear? Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro need to update the workbook
Thanks Dave~!
Actually, the "updated" file is an export from an access query, which holds the latest equipments status info. The "Live" file is just another worksheet in regards those equipments. what I thought is: equipments status query---"updated" xls---"Live" xls Maybe I can just use the query to update the equipment info, Can I ? any better options? Cheers "Dave Peterson" wrote: You've made yourself clear, but excel isn't really good at doing this kind of thing. You may want to consider putting the data in one worksheet, add a indicator column (Live or not-Live), then use data|filter|autofilter to show the data you want. x6v87qe wrote: Hi, All Basically, I have two workbooks and each of them contains a single worksheet. The first worksheet namely âœUpdatedâ has 8 columns, while the other worksheet âœLiveâ has 16 columns (8 of them are same with âœUpdateâ, rest are different). However, both of them have a same indicate column âœEquipment IDâ. What I need is to use the âœUpdatedâ sheet to update the âœLiveâ one. That is whenever, thereâs a change in the âœEquipment IDâ of âœUpdateâ I need the changes .including all other 8 columns, to be reflected on the âœLiveâ sheet, no matter itâs an addition, deletion or just change of value. Did I make myself clear? Thanks -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro need to update the workbook
Thanks Bernie~!
Actually, the "updated" file is an export from an access query, which holds the latest equipments status info. The "Live" file is just another worksheet in regards those equipments. what I thought is: equipments status query---"updated" xls---"Live" xls Maybe I can just use the query to update the equipment info, Can I ? any better options? Cheers "Bernie Deitrick" wrote: The best approach is to simply don't use your "Updated" workbook: make any changes, additions, or deletions directly in the "Live" workbook. Having data reside in two places is a recipe for corruption. HTH, Bernie MS Excel MVP "x6v87qe" wrote in message ... Hi, All Basically, I have two workbooks and each of them contains a single worksheet. The first worksheet namely "Updated" has 8 columns, while the other worksheet "Live" has 16 columns (8 of them are same with "Update", rest are different). However, both of them have a same indicate column "Equipment ID". What I need is to use the "Updated" sheet to update the "Live" one. That is whenever, there's a change in the "Equipment ID" of "Update" I need the changes .including all other 8 columns, to be reflected on the "Live" sheet, no matter it's an addition, deletion or just change of value. Did I make myself clear? Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro need to update the workbook
Well, then the question that must be answered first is this: Is "Equipment ID" a key number that can
never be changed? If it can be changed, then it is impossible to tell a deletion from a change or a change from an addition. If it is never changed, then with the two workbooks open, just use linking formulas based on the key number. Enter a formula like this in cell B2 of the Live sheet to pull the associated value from the column B of the update workbook. =VLOOKUP($A2,'[Updated.xls]Update Sheet'!$A$1:$H$1000,COLUMN(),FALSE) The use of COLUMN assumes that the data columns are in the same order - which simplifies the extraction. IF there is a deletion or addition, you could manually delete the row from the Live sheet - the VLOOKUP would return errors, which would indicate which row values are no longer available in the Update sheet. For additions, you could link to the ID column to extract all the values: ='[Updated.xls]Update Sheet'!$A1 copied down, then use formulas like =IF(ISERROR(MATCH(cell with above formula,$A:$A,False)),"NEW!","") Copied down to match your link formulas - then filter to find the NEW values... Or you could use a macro.... but it depends on the ID.... HTH, Bernie MS Excel MVP "x6v87qe" wrote in message ... Thanks Bernie~! Actually, the "updated" file is an export from an access query, which holds the latest equipments status info. The "Live" file is just another worksheet in regards those equipments. what I thought is: equipments status query---"updated" xls---"Live" xls Maybe I can just use the query to update the equipment info, Can I ? any better options? Cheers "Bernie Deitrick" wrote: The best approach is to simply don't use your "Updated" workbook: make any changes, additions, or deletions directly in the "Live" workbook. Having data reside in two places is a recipe for corruption. HTH, Bernie MS Excel MVP "x6v87qe" wrote in message ... Hi, All Basically, I have two workbooks and each of them contains a single worksheet. The first worksheet namely "Updated" has 8 columns, while the other worksheet "Live" has 16 columns (8 of them are same with "Update", rest are different). However, both of them have a same indicate column "Equipment ID". What I need is to use the "Updated" sheet to update the "Live" one. That is whenever, there's a change in the "Equipment ID" of "Update" I need the changes .including all other 8 columns, to be reflected on the "Live" sheet, no matter it's an addition, deletion or just change of value. Did I make myself clear? Thanks |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro need to update the workbook
Thanks, Bernie
I've figured it out. "Bernie Deitrick" wrote: Well, then the question that must be answered first is this: Is "Equipment ID" a key number that can never be changed? If it can be changed, then it is impossible to tell a deletion from a change or a change from an addition. If it is never changed, then with the two workbooks open, just use linking formulas based on the key number. Enter a formula like this in cell B2 of the Live sheet to pull the associated value from the column B of the update workbook. =VLOOKUP($A2,'[Updated.xls]Update Sheet'!$A$1:$H$1000,COLUMN(),FALSE) The use of COLUMN assumes that the data columns are in the same order - which simplifies the extraction. IF there is a deletion or addition, you could manually delete the row from the Live sheet - the VLOOKUP would return errors, which would indicate which row values are no longer available in the Update sheet. For additions, you could link to the ID column to extract all the values: ='[Updated.xls]Update Sheet'!$A1 copied down, then use formulas like =IF(ISERROR(MATCH(cell with above formula,$A:$A,False)),"NEW!","") Copied down to match your link formulas - then filter to find the NEW values... Or you could use a macro.... but it depends on the ID.... HTH, Bernie MS Excel MVP "x6v87qe" wrote in message ... Thanks Bernie~! Actually, the "updated" file is an export from an access query, which holds the latest equipments status info. The "Live" file is just another worksheet in regards those equipments. what I thought is: equipments status query---"updated" xls---"Live" xls Maybe I can just use the query to update the equipment info, Can I ? any better options? Cheers "Bernie Deitrick" wrote: The best approach is to simply don't use your "Updated" workbook: make any changes, additions, or deletions directly in the "Live" workbook. Having data reside in two places is a recipe for corruption. HTH, Bernie MS Excel MVP "x6v87qe" wrote in message ... Hi, All Basically, I have two workbooks and each of them contains a single worksheet. The first worksheet namely "Updated" has 8 columns, while the other worksheet "Live" has 16 columns (8 of them are same with "Update", rest are different). However, both of them have a same indicate column "Equipment ID". What I need is to use the "Updated" sheet to update the "Live" one. That is whenever, there's a change in the "Equipment ID" of "Update" I need the changes .including all other 8 columns, to be reflected on the "Live" sheet, no matter it's an addition, deletion or just change of value. Did I make myself clear? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| not allow update for workbook w/ Links | Excel Discussion (Misc queries) | |||
| Update from another sheet in same workbook | Excel Discussion (Misc queries) | |||
| Update a workbook from a script | Excel Discussion (Misc queries) | |||
| How to Update a link within a workbook | Excel Worksheet Functions | |||
| Update linked cells within a workbook??? | Links and Linking in Excel | |||
Linear Mode
