Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting like items on one sheet, showing totals on another sheet
Sorry if this has been answered, I looked at 342 different questions, did not
find one like mine. Within a workbook, I have 6 sheets. I have an inventory sheet listing all product by item number and description, ex: "80036" in a1, "36 inch box" in b1, "80046" in a2, "46 inch box" in b2, etc.. A total of 1200 lines of different items and descriptions. Then I have 4 quote sheets. Some rows for each sheet use "VLOOKUP", so if the item number from the inventory sheet is typed in appropriate col., description from inventory sheet is returned in next col.. OK, next I have a sheet that is my picking ticket. On this sheet I want a total by row of each item listed on the 4 quote sheets, so it shows how many of the same item were listed on the 4 quote sheets. So if "80036" is on quote sheet one with a value of 4, and on quote sheet two with a value of 3, my picking tix would show that item number and description with a value of 7 in the appropriate col.. Not all items will always be listed on quote sheets. How do I set up the rows on picking tix to look for an item number on the four quote sheets,and if not found, go to the next item number, once an item number is found, sum the total number listed for the found item number, then on the next row, do the same thing without duplicating what has already been found, until all 1200 items have been searched for? I think I am asking too much..........TIA for any and all help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting like items on one sheet, showing totals on another sheet
Assuming you have all 1200 items listed on the PICKING Ticket sheet with the
item numbers in column A then (assume quote sheets have item number in column B and quantities in D with sheet names qsheet1, qsheet2, qsheet3, qsheet4: In F2 of the picking ticket for example. =countif(Qsheet1!B:B,A2,Qsheet1!D:D)+countif(Qshee t2!B:B,A2,Qsheet2!D:D)+countif(Qsheet3!B:B,A2,Qshe et3!D:D)+countif(Qsheet4!B:B,A2,Qsheet4!D:D) then drag fill down the column. when done and the numbers are returned, if you want to make them permanent, select column F, do edit=Copy, then Edit=Paste special and select values to overwrite the formulas with the values they display. -- Regards, Tom Ogilvy "RPW" wrote: Sorry if this has been answered, I looked at 342 different questions, did not find one like mine. Within a workbook, I have 6 sheets. I have an inventory sheet listing all product by item number and description, ex: "80036" in a1, "36 inch box" in b1, "80046" in a2, "46 inch box" in b2, etc.. A total of 1200 lines of different items and descriptions. Then I have 4 quote sheets. Some rows for each sheet use "VLOOKUP", so if the item number from the inventory sheet is typed in appropriate col., description from inventory sheet is returned in next col.. OK, next I have a sheet that is my picking ticket. On this sheet I want a total by row of each item listed on the 4 quote sheets, so it shows how many of the same item were listed on the 4 quote sheets. So if "80036" is on quote sheet one with a value of 4, and on quote sheet two with a value of 3, my picking tix would show that item number and description with a value of 7 in the appropriate col.. Not all items will always be listed on quote sheets. How do I set up the rows on picking tix to look for an item number on the four quote sheets,and if not found, go to the next item number, once an item number is found, sum the total number listed for the found item number, then on the next row, do the same thing without duplicating what has already been found, until all 1200 items have been searched for? I think I am asking too much..........TIA for any and all help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting like items on one sheet, showing totals on another sh
Ok, but is that going to give me a sum total for an item, or just a total
count of how many times that item was listed on the quote sheets? Thank you for your help. "Tom Ogilvy" wrote: Assuming you have all 1200 items listed on the PICKING Ticket sheet with the item numbers in column A then (assume quote sheets have item number in column B and quantities in D with sheet names qsheet1, qsheet2, qsheet3, qsheet4: In F2 of the picking ticket for example. =countif(Qsheet1!B:B,A2,Qsheet1!D:D)+countif(Qshee t2!B:B,A2,Qsheet2!D:D)+countif(Qsheet3!B:B,A2,Qshe et3!D:D)+countif(Qsheet4!B:B,A2,Qsheet4!D:D) then drag fill down the column. when done and the numbers are returned, if you want to make them permanent, select column F, do edit=Copy, then Edit=Paste special and select values to overwrite the formulas with the values they display. -- Regards, Tom Ogilvy "RPW" wrote: Sorry if this has been answered, I looked at 342 different questions, did not find one like mine. Within a workbook, I have 6 sheets. I have an inventory sheet listing all product by item number and description, ex: "80036" in a1, "36 inch box" in b1, "80046" in a2, "46 inch box" in b2, etc.. A total of 1200 lines of different items and descriptions. Then I have 4 quote sheets. Some rows for each sheet use "VLOOKUP", so if the item number from the inventory sheet is typed in appropriate col., description from inventory sheet is returned in next col.. OK, next I have a sheet that is my picking ticket. On this sheet I want a total by row of each item listed on the 4 quote sheets, so it shows how many of the same item were listed on the 4 quote sheets. So if "80036" is on quote sheet one with a value of 4, and on quote sheet two with a value of 3, my picking tix would show that item number and description with a value of 7 in the appropriate col.. Not all items will always be listed on quote sheets. How do I set up the rows on picking tix to look for an item number on the four quote sheets,and if not found, go to the next item number, once an item number is found, sum the total number listed for the found item number, then on the next row, do the same thing without duplicating what has already been found, until all 1200 items have been searched for? I think I am asking too much..........TIA for any and all help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting like items on one sheet, showing totals on another sh
Also, if some of the totals (or rows) are zero, when I print out the picking
ticket report, how do I not print any 'zero total' items? Thanks. "RPW" wrote: Ok, but is that going to give me a sum total for an item, or just a total count of how many times that item was listed on the quote sheets? Thank you for your help. "Tom Ogilvy" wrote: Assuming you have all 1200 items listed on the PICKING Ticket sheet with the item numbers in column A then (assume quote sheets have item number in column B and quantities in D with sheet names qsheet1, qsheet2, qsheet3, qsheet4: In F2 of the picking ticket for example. =countif(Qsheet1!B:B,A2,Qsheet1!D:D)+countif(Qshee t2!B:B,A2,Qsheet2!D:D)+countif(Qsheet3!B:B,A2,Qshe et3!D:D)+countif(Qsheet4!B:B,A2,Qsheet4!D:D) then drag fill down the column. when done and the numbers are returned, if you want to make them permanent, select column F, do edit=Copy, then Edit=Paste special and select values to overwrite the formulas with the values they display. -- Regards, Tom Ogilvy "RPW" wrote: Sorry if this has been answered, I looked at 342 different questions, did not find one like mine. Within a workbook, I have 6 sheets. I have an inventory sheet listing all product by item number and description, ex: "80036" in a1, "36 inch box" in b1, "80046" in a2, "46 inch box" in b2, etc.. A total of 1200 lines of different items and descriptions. Then I have 4 quote sheets. Some rows for each sheet use "VLOOKUP", so if the item number from the inventory sheet is typed in appropriate col., description from inventory sheet is returned in next col.. OK, next I have a sheet that is my picking ticket. On this sheet I want a total by row of each item listed on the 4 quote sheets, so it shows how many of the same item were listed on the 4 quote sheets. So if "80036" is on quote sheet one with a value of 4, and on quote sheet two with a value of 3, my picking tix would show that item number and description with a value of 7 in the appropriate col.. Not all items will always be listed on quote sheets. How do I set up the rows on picking tix to look for an item number on the four quote sheets,and if not found, go to the next item number, once an item number is found, sum the total number listed for the found item number, then on the next row, do the same thing without duplicating what has already been found, until all 1200 items have been searched for? I think I am asking too much..........TIA for any and all help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting totals between multiple columns for like items | Excel Worksheet Functions | |||
How to record a sheet change showing row column sheet name and date? | Excel Programming | |||
How to record a sheet change showing row column sheet name and date? | Excel Programming | |||
How to record a sheet change showing row column sheet name and date? | Excel Programming | |||
How to record a sheet change showing row column sheet name and date? | Excel Programming |