Solved: Update different workbook from active worksheet

[Redness86]

Hello all,

I have two workbooks in Excel 2000. Source.xls has multiple sheets and stores price lists for products/services provided (no duplicate data). Target.xls has multiple sheets and is where we create cost estimates for numerous job sites for a single client (potentially a lot of duplicate data). As I update the cost of an item in Source.xls, I'd like that change to be reflected anywhere the item is found in Target.xls.

The sheets in both workbooks have the following setup:

ColA = Qty
ColB = PartNumber
ColC = PartDescription
ColD = UnitMeasurement
ColE = UnitMaterialCost
ColF = UnitLaborCost
ColG = UnitEquipCost
ColH = ExtMaterialCost
ColI = ExtLabor Cost
ColJ = ExtEquipCost

The criteria used to find matching items will be in columns B and C. Updates will be made in columns E-G, and Target.xls will be open.

I thought I'd found the answer in Silver Fox's posting, Updating multiple worksheets at once; four source, three target, but I couldn't get it to update the separate workbook. Any help would be greatly appreciated, thank you.

And thanks to everyone for all the other solutions I've found in this forum!
...

[mdmackillop]

Can you post a small sample of both workbooks? Use Manage Attachments in the Go Advanced section.

[Redness86]

Yes, thanks. Here is the source file, with some sample data.

[Redness86]

Here's the target file.

[mdmackillop]

Hi Redness.
The simplest solution I can see is to add the row number into your source sheet and data rows eg "DM/3" and add a formula into the Target sheet (I've assumed item 8 for example purposes only)
[vba]
.Cells(vRow, 6).Formula = "=[Source.xls]" & Split(Me.cboxDesc.List(vItem, 8),"/")(0) _
& "E" & Split(Me.cboxDesc.List(vItem, 8),"/")(1)
[/vba]

[Redness86]

Thanks MD. I'll give that a try.