Lrodriguez

New Member
Joined
Dec 16, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I have a difficult project i cant seem to figure out. My goal is to make an order form for people to fill out based on a bid i put out for vendors to bid on clothing. They bid on the price of the shirt or short and then they also gave the prices for a 1 color imprint, 2 color imprint....

My first goal was to find the lowest price for a piece of clothing based on the vendor's bids using this formula: =INDEX($C$1:$F$1,MATCH(G3,C3:F3,0)) that actually added the vendor's name in the cell for me.

Once i figured that out i realized some vendors were lower in their clothing item but might be higher in their imprint and vice versa. I still have not accomplished how to determine lowest bidder based on those two criteria. Base price + Imprint Cost

My current Goal is to make a Drop Down list for my end user to just pick the type of imprint.(1 Color, 2 Color, 1 Color Number, 2 Color Number...) and it would hopefully give them the correct price of the imprint job based on the quantity and then return lowest price based on the vendor. This is the formula i am using for this: =INDEX($T$4:$Y$7,MATCH(C18,$S$4:$S$7,0),MATCH(E18,$T$3:$Y$3,1))

When i enter in that formula i get this Error "You may not use reference operators or array constants for Data Validation" I also tried to use the # when referencing my sheet/cell, and i also tried to define the name and that didnt work either.

12-2425-4950-7475-99100-199200-299
12255075100200
Vendor 1
$2.25​
$2.00​
$1.85​
$1.85​
$1.60​
$1.50​
Vendor 2
$4.00​
$3.40​
$3.05​
$3.05​
$2.85​
$2.85​
Vendor 3
$3.29​
$2.75​
$2.40​
$2.00​
$1.90​
$1.75​
Vendor 4
$1.40​
$1.50​
$1.25​
$1.25​
$1.25​
$1.10​
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

ExcelGzh

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Lrodriguez

There must be something odd about the values in cells C18 and/or E18 and/or the ranges $S$4:$S$7 or $T$3:$Y$3, because if you try your formula with fixed values in all the cells it works fine.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,800
Messages
5,626,958
Members
416,211
Latest member
lanka123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top