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

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

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

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

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
--

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
--

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