Bid Tracker Analysis Extractions

[mycon73]

Hi

This similar to recent inquiry: Vendor Bid Analysis - Lowest and Highest 3 Bids
https://www.excelforum.com/excel-gen...st-3-bids.html
Except, I want to put summary on another worksheet - See, 2920 Bid Sheet Analysis

** See Attached Example **

As you will see, in the 2920 Bid Sheet Analysis

I'm trying to extract a series of bid requests per Division Categories, such as TEMPORARY PERIMETER FENCING

Here, there are 8 Subcontractor bids

The bid amount value & ranking seems to work well
=IFERROR(AGGREGATE(15,6,(Bid_Amount+ROW($R$93:$R$167)*10^-6)/(Const_Phase=$B$17)/(Bid_Amount>0),@ROW(1:1)),"")


However, the other 2 are not!!!
-- Using named ranges in these functions to

For Bidding Subcontractors
=IFERROR(LOOKUP(2,1/(Bid_Amount+ROW(Bid_Amount)*10^-6=E18),Subcontractors),"")


Solutions Comments or Recommendations
=IFERROR(IF(LOOKUP(2,1/(Bid_Amount+ROW(Bid_Amount)*10^-6=$E18),Finishes_Comments)="","",LOOKUP(2,1/(Bid_Amount+ROW(Bid_Amount)*10^-6=$Q73),Finishes_Comments)),"")


With both of these, I should be seeing the subcontract that is associated with the $8,316.05 bid along with its comments in column F


Requesting assistance & preferably, less complex functions to get these to work

Primary objectives is to get 3 to 20 bids, rank bids from low to high & evaluate quotes to determine which subcontractors is giving best value for the bid amount

See Attached....


Thanks

[AliGW]

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[mycon73]

Hi Group

Requesting assistance

Uncertain why these 3 functions works well when extracting within same worksheet but only 1 of 3 works if extracting from another worksheet


See example....

Thanks

[bebo021999]

Formula in E28, with
...Bid_Amount+ROW($R$93:$R$167)*10^-6)...
in order to reconise duplicate value (if existing) like, 8,316.05 in row 1 (Sub1) and row 2 (Sub2), to be 8,316.05000093 and 8,316.05000094 (slightly very small amount), to separate the two Subcontractors
But in D28,
Bid_Amount+ROW(Bid_Amount)*10^-6=E28
it perform comparing statement quite different value between 8,316.05000001 = 8,316.05000093
it should be in E28:

=IFERROR(AGGREGATE(15,6,(Bid_Amount+ROW(Bid_Amount)*10^-6)/(Const_Phase=$B$27)/(Bid_Amount>0),ROWS($1:1)),"")

Enter only (if Ex2010 or later)

[mycon73]

Hi

Thanks for getting back to me here

In cell E28 or Bid Amount column, I have

=IFERROR(AGGREGATE(15,6,(Bid_Amount+ROW($R$103:$R$177)*10^-6)/(Const_Phase=$B$27)/(Bid_Amount>0),@ROW(1:1)),"")

This function works well for low to high bid amounts


It's the Bidding Subcontractors and Solutions Comments or Recommendations functions in these columns is not working well


Trying to use same function as given in previous example but for unknown reasons, not working for me here


Thanks