Please help! Multifile search...

[inzzane]

First off, please forgive me if this is posted somewhere else. I looked
but could not quite get anything to work for my issue.

I have 2 .xls files.

The first file is an order list, and contains the item name in colum
c, and the quantity in column d. There are multiple listings of eac
item in this file.

EXAMPLE:

Column C Columd D
wireset 8
monitor 2
keyboard 1
wireset 4
CPU 1

What I meed to do is figure out how to do a search for "wireset" an
get a total of 12, as well as erase those 2 lines when it is added t
the new location in File 2


File 2

Column A Column B
Wireset (Script goes here)
Monitor (Script goes here)
Keyboard (Script goes here)
CPU (Script goes here)

when all scripts are run, the remaining orders will then be copied ove
to the new file.

This has been an issue for me for a week now. any help will b
appreciated.

Here is my last try, but would not total up.


=SUM((('[BOM.xls]MDF
IDF'!$A$2:$A$100)>=VALUE("wireset"))*(('[BOM.xls]MDF
IDF'!$C$2:$C$101))

 

[Biff]

Hi!

Try this if the other file is open:

Where A1 = wireset

=SUMIF([BOM.xls]MDF-IDF!A2:A100,A1,[BOM.xls]MDF-IDF!
C2:C100)

If the other file is closed you must include the entire
path to the file:

=SUMIF(C\...\...\[BOM]MDF-IDF!.....

Biff

 

[inzzane]

THANK YOU!!! THANK YOU!!! THANK YOU!!! (Repeat 1,000 times!)

It works perfect! and you have saved me hours of time at work eac
week!

WOOHOO!!! No more $%^$#* overtime!!! :

 

[Dave Peterson]

I think that =sumif() will break when the "sending" workbook is closed (well,
after the first recalculation).

But =sumproduct will work if the workbook is open or closed:

=SUMPRODUCT(--('[bom.xls]mdf-idf'!A2:A100=A1),'[bom.xls]mdf-idf'!C2:C100)

(I'd write the formula with the sending workbook open and then close the
workbook to see the version that includes the path for bom.xls.)

Hi!

Try this if the other file is open:

Where A1 = wireset

=SUMIF([BOM.xls]MDF-IDF!A2:A100,A1,[BOM.xls]MDF-IDF!
C2:C100)

If the other file is closed you must include the entire
path to the file:

=SUMIF(C\...\...\[BOM]MDF-IDF!.....

Biff

-----Original Message-----
First off, please forgive me if this is posted somewhere else. I looked,
but could not quite get anything to work for my issue.

I have 2 .xls files.

The first file is an order list, and contains the item name in column
c, and the quantity in column d. There are multiple listings of each
item in this file.

EXAMPLE:

Column C Columd D
wireset 8
monitor 2
keyboard 1
wireset 4
CPU 1

What I meed to do is figure out how to do a search for "wireset" and
get a total of 12, as well as erase those 2 lines when it is added to
the new location in File 2


File 2

Column A Column B
Wireset (Script goes here)
Monitor (Script goes here)
Keyboard (Script goes here)
CPU (Script goes here)

when all scripts are run, the remaining orders will then be copied over
to the new file.

This has been an issue for me for a week now. any help will be
appreciated.

Here is my last try, but would not total up.


=SUM((('[BOM.xls]MDF -
IDF'!$A$2:$A$100)>=VALUE("wireset"))*(('[BOM.xls]MDF -
IDF'!$C$2:$C$101)))