Macro need to update the workbook

[Guest]

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]

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.

 

[Bernie Deitrick]

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

 

[Guest]

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

 

[Guest]

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]

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

 

[Guest]

Thanks, Bernie
I've figured it out.

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

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