sum of mutiple entries on another workbook

[jqchuy66]

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?

 

[Bernard Liengme]

I will assume that row 1 of Workbook1 has the headers (equip#, owner,etc)
In A1 of Workbook2 enter =[Workbook1.xlsx]Sheet1!A1
Copy this across to column D
Copy A11 down to row 3
Modify D3 to read
=IF([Workbook1.xlsx]Sheet1!A3="",A2,[Workbook1.xlsx]Sheet1!A3)
Copy row 3 down as far as needed - it does not matter if you go down to row
500 and there are only 300 rows used in Workbook1

In F1 enter 555, in F2 enter 556, etc
In G1 enter =SUMIF(A:A,F1,D)
Copy down the column to get the required results

a) I am using Excel 2007, so my file extension is XLSX, in earlier versions
it will be XLS
b) If the work book name has spaces in it you need single quotes:
='[Workbook One.xlsx]Sheet1'!A2
c) If the list of Equip# is long, use a filter on Workbook1 (temporarily) to
get a list of unquiet numbers to copy to workbook2 - come back if more info
needed on this topic
best wishes

 

[Arvi Laanemets]

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 Data>GetExternal Data>New 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 where: 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 Records>Edit 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]

Thank you!

I have added your suggestions and understand how they should work however,
(Workbook #1)column B has either the owners name or date of entry and
(Workbook #2) column A now has a zero if the value of workbook #1 was blank.
Hence, the =SUMIF(A:A,O1,M:M) formula in column G does not recognize the
values in column A that = 0?

If I run a filter I only omit those rows which are false...

Any ideas?

Using version 2003

--
Thanks,
Jesse

I will assume that row 1 of Workbook1 has the headers (equip#, owner,etc)
In A1 of Workbook2 enter =[Workbook1.xlsx]Sheet1!A1
Copy this across to column D
Copy A11 down to row 3
Modify D3 to read
=IF([Workbook1.xlsx]Sheet1!A3="",A2,[Workbook1.xlsx]Sheet1!A3)
Copy row 3 down as far as needed - it does not matter if you go down to row
500 and there are only 300 rows used in Workbook1

In F1 enter 555, in F2 enter 556, etc
In G1 enter =SUMIF(A:A,F1,D)
Copy down the column to get the required results

a) I am using Excel 2007, so my file extension is XLSX, in earlier versions
it will be XLS
b) If the work book name has spaces in it you need single quotes:
='[Workbook One.xlsx]Sheet1'!A2
c) If the list of Equip# is long, use a filter on Workbook1 (temporarily) to
get a list of unquiet numbers to copy to workbook2 - come back if more info
needed on this topic
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

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?

.

 

[jqchuy66]

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?

 

[Arvi Laanemets]

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]

I appreciate your time and knowlege... I'll see what I can do.