I appreciate your time and knowlege... I'll see what I can do.
--
Thanks,
Jesse


"Arvi Laanemets" wrote:

With current source table structure, the only real and usable way will be
some VBA script - p.e. Workbooks Open event. Whenever 2nd workbook is
opened, it clears old table, creates an array (equipment, amount) to store
data, reads the source table row-wise, adds amounts to according equipment
in array, and when the source table is processed entirely, writes contents
of array into new result table.

Arvi Laanemets



"jqchuy66" wrote in message
...
Thanks for your suggestion, and I agree the creator of the first workbook
could have designed it better. Unfortunately, there are too many others
what
extract information from this workbook and it can not be changed at my
level.
So, your fix will not work in this situation.

Any other ideas?

--
Thanks,
Jesse


"Arvi Laanemets" wrote:

Hi

For such situations, I use QDBC query.
I´ll give directions for Excel2000, as I use this version, but it works
with
later versions too (there are differences whe creating query in
Excel2007 -
menus are different, and Query window dsign is different too, but final
result is same).

You must have Analysis Toolpack add-in installed. (I found out hard way,
that otherwise you can query another workbook only then, when it is
opened.
I have no clue, what has Analysis Toolpack to do with ODBC query, but it
works this way)

It looks like your table design isn't very good. To use ODBC query, you
source table must be designed as database table. Like:
equipment owner date amount
555 John 12/1 40.00
555 John 12/3 20.00
556 Mark 12/2 35.00
(Your data can be in any order or unordered here)

Define your table in 1st workbook as non-dynamic named range. NB! You
must
have column headings in 1st row of named range! And the range must be
defined as simple range address, like =Sheet1!$A$1:$D$1000. My advice is
to
have a lot of empty rows in bottom of defined range - so users can add
rows
to source table later, and you get all of them into 2nd workbook without
redefining the range later. Save the 1st workbook (you can close it now).

In 2nd workbook, select from menu DataGetExternal DataNew Database
Query
In Choose Data Source window, select 'Excel files*'. OK
In Select Workbook window, find the location your 1st workbook is stored,
and select the 1st workbook as database. OK
In Query Wizard, select your named range as table, and follow
instructions
to determine columns (equipment, amount), filters (here you can filter
out
empty rows, like 'Only include rows whe Equipment Is Not Null'), and
order (equipment). Check 'View data or edit query...' in last panel.
Finish.
In Microsoft Query window, activate in result data panel amount column,
and
then select from menu RecordsEdit Column. In Total field, select Sum,
and
edit column heading. OK
Close Microsoft Query window. You are asked to where insert returned
data -
with currently active cell as default adress of upper left corner of
result
table. Change the address when needed. Then click properties button, and
check 'Refresh data on file open'. you may change some other properties
here
too. OK. OK

It's done. Whenever your open now 2nd workbook, fresh data form 1st
workbook
(last save) are read and summarized into result table.


Arvi Laanemets


"jqchuy66" wrote in message
...
Hello,

I have two workbooks, #1 I am not allowed to modify, information is
sorted
by equipment number and date of entry.

I need the sum to show on another workbook, #2, sorted by equipment
number,
example:

workbook #1
equip# owner date amount
555 John
12/1 40.00
12/3 20.00
556 Mark
12/2 35.00

There could be other rows added for future dates and amounts.

Workbook #2
equipment total
555 60.00
556 35.00

Any suggestions?

--
Thanks,
Jesse


.



.