| Home |
| Search |
| Today's Posts |
|
|
|
#1
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 | |||
Hybrid Mode
