Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum of mutiple entries on another workbook
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum of mutiple entries on another workbook
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 A1:D1 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: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 "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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum of mutiple entries on another workbook
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum of mutiple entries on another workbook
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 "Bernard Liengme" wrote: 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 A1:D1 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: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 "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 . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum of mutiple entries on another workbook
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 . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum of mutiple entries on another workbook
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 . |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum of mutiple entries on another workbook
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running mutiple excel sheets on mutiple computers | Excel Discussion (Misc queries) | |||
How to share a workbook when entries are chronological? | Excel Discussion (Misc queries) | |||
HOW CAN I PREVENT DUPLICATE ENTRIES WITHIN A WORKBOOK, i.e. job # | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Mutiple Workbook Summary | Excel Worksheet Functions |