|
Creating a list in sequential order
Hello,
I have a workbook that I keep track of bids we have won and lost. All the infomation is in a worksheet called "Bids". I list the dollar value of the bid and have a column where I can put "Won" or "Lost". If we lose the bid I put the company name in the column next to the "Lost" column. I have another worksheet called "Lost Recap" that I keep track of the bids we lost. I list the name of the company that won the bid, how many bids they have won for the year, and the total dollar value for all the bids. My question is: is there a way to automatically insert the company name, and dollar value in the "Lost Recap" worksheet when I put the word "Lost" in column D of the "Bids" worksheet? Also, it possibe to automatically insert each company that won a bid to "Lost Recap" in sequential order? Any help is appreciated. Thanks! Mark |
Creating a list in sequential order
Not very sure, but here's one interp / play on what's wanted ..
Sample construct at: http://cjoint.com/?lerXD7l7zM MarkJackson_wks.xls In sheet: Bids, in C1:E6 is the sample source table Bid W / L Company 2700 Lost Comp1 3600 Won 4500 Won 1100 Lost Comp4 3600 Lost Comp5 Put in G2: =IF(D2="","",IF(D2="Lost",ROW(),"")) Copy down to say, G10, to cover the max expected data (Leave G1 empty) In sheet: Lost Recap ---------------- Headers in A1:B1 : Company, Bid Put in A2: =IF(ISERROR(SMALL(Bids!G:G,ROWS($A$1:A1))),"", INDEX(Bids!E:E,MATCH(SMALL(Bids!G:G,ROWS($A$1:A1)) ,Bids!G:G,0))) Put in B2: =IF(ISERROR(SMALL(Bids!G:G,ROWS($A$1:A1))),"", INDEX(Bids!C:C,MATCH(SMALL(Bids!G:G,ROWS($A$1:A1)) ,Bids!G:G,0))) Select A2:B2, copy down to B10 (cover the same extent as done in col G in "Bids") The above will yield the results neatly bunched at the top: Company Bid ------------------- Comp1 2700 Comp4 1100 Comp5 3600 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Mark Jackson" wrote in message ... Hello, I have a workbook that I keep track of bids we have won and lost. All the infomation is in a worksheet called "Bids". I list the dollar value of the bid and have a column where I can put "Won" or "Lost". If we lose the bid I put the company name in the column next to the "Lost" column. I have another worksheet called "Lost Recap" that I keep track of the bids we lost. I list the name of the company that won the bid, how many bids they have won for the year, and the total dollar value for all the bids. My question is: is there a way to automatically insert the company name, and dollar value in the "Lost Recap" worksheet when I put the word "Lost" in column D of the "Bids" worksheet? Also, it possibe to automatically insert each company that won a bid to "Lost Recap" in sequential order? Any help is appreciated. Thanks! Mark |
Creating a list in sequential order
Max,
Thank you for taking the time to look at my post. I like what you have done so far. My worksheet might be over 500 lines, would it be possible for each company that has won more than one time to be listed just once and add the total number of bids won and the total dollar value on one line? Thanks for your help. Sincerely, Mark "Max" wrote: Not very sure, but here's one interp / play on what's wanted .. Sample construct at: http://cjoint.com/?lerXD7l7zM MarkJackson_wks.xls In sheet: Bids, in C1:E6 is the sample source table Bid W / L Company 2700 Lost Comp1 3600 Won 4500 Won 1100 Lost Comp4 3600 Lost Comp5 Put in G2: =IF(D2="","",IF(D2="Lost",ROW(),"")) Copy down to say, G10, to cover the max expected data (Leave G1 empty) In sheet: Lost Recap ---------------- Headers in A1:B1 : Company, Bid Put in A2: =IF(ISERROR(SMALL(Bids!G:G,ROWS($A$1:A1))),"", INDEX(Bids!E:E,MATCH(SMALL(Bids!G:G,ROWS($A$1:A1)) ,Bids!G:G,0))) Put in B2: =IF(ISERROR(SMALL(Bids!G:G,ROWS($A$1:A1))),"", INDEX(Bids!C:C,MATCH(SMALL(Bids!G:G,ROWS($A$1:A1)) ,Bids!G:G,0))) Select A2:B2, copy down to B10 (cover the same extent as done in col G in "Bids") The above will yield the results neatly bunched at the top: Company Bid ------------------- Comp1 2700 Comp4 1100 Comp5 3600 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Mark Jackson" wrote in message ... Hello, I have a workbook that I keep track of bids we have won and lost. All the infomation is in a worksheet called "Bids". I list the dollar value of the bid and have a column where I can put "Won" or "Lost". If we lose the bid I put the company name in the column next to the "Lost" column. I have another worksheet called "Lost Recap" that I keep track of the bids we lost. I list the name of the company that won the bid, how many bids they have won for the year, and the total dollar value for all the bids. My question is: is there a way to automatically insert the company name, and dollar value in the "Lost Recap" worksheet when I put the word "Lost" in column D of the "Bids" worksheet? Also, it possibe to automatically insert each company that won a bid to "Lost Recap" in sequential order? Any help is appreciated. Thanks! Mark |
Creating a list in sequential order
Extending the earlier set-up ..
Revised sample construct at: http://www.savefile.com/files/8330144 Creating a list in sequential order_MarkJackson_wks In sheet: Lost Recap ---------------------- Put in C2, copy down: =IF(A2="","",IF(COUNTIF($A$2:A2,A2)1,"",ROW())) (Leave C1 empty) In a new sheet: Summ ---------------------- Put in A2: =IF(ISERROR(SMALL('Lost Recap'!C:C,ROWS($A$1:A1))),"", INDEX('Lost Recap'!A:A, MATCH(SMALL('Lost Recap'!C:C,ROWS($A$1:A1)),'Lost Recap'!C:C,0))) Copy down to A20 (cover the same range as was done in col C in "Lost Recap") Put in B2, copy down: =IF(A2="","",SUMIF('Lost Recap'!A:A,A2,'Lost Recap'!B:B)) Summ will return the final "summarized" results that's wanted Adapt to suit .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Mark Jackson" wrote in message ... Max, Thank you for taking the time to look at my post. I like what you have done so far. My worksheet might be over 500 lines, would it be possible for each company that has won more than one time to be listed just once and add the total number of bids won and the total dollar value on one line? Thanks for your help. Sincerely, Mark |
Creating a list in sequential order
total number of bids won
Missed out the above requirement earlier, sorry In sheet: Summ ---------------------- Put in C2, and copy down: =IF(A2="","",COUNTIF('Lost Recap'!A:A,Summ!A2)) Col C will return the total # of bids won for the companies extracted in col A The previous sample has been amended: http://savefile.com/files/8330144 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Creating a list in sequential order
Thanks Max!
It works perfect. I appreciate your time and effort. Sincerely, Mark "Max" wrote: total number of bids won Missed out the above requirement earlier, sorry In sheet: Summ ---------------------- Put in C2, and copy down: =IF(A2="","",COUNTIF('Lost Recap'!A:A,Summ!A2)) Col C will return the total # of bids won for the companies extracted in col A The previous sample has been amended: http://savefile.com/files/8330144 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Creating a list in sequential order
Glad it helped, Mark !
Thanks for the feedback -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Mark Jackson" wrote in message ... Thanks Max! It works perfect. I appreciate your time and effort. Sincerely, Mark |
| All times are GMT +1. The time now is 09:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com