Drop-down list with index(match

Lebene

New Member
Joined
Sep 20, 2019
Messages
40
Platform
  1. Windows
Hey guys, has the website had an upgrade? Looks great!

I'm trying to make a waste cost dashboard. Basically, all I want to do is select the supplier code from the drop-down list and once I enter the number of workshops run, it will automatically update the number of seats the event had space for and the cost. I have a reference table where it gets this information from.

I know how to do the index(match that returns a value based off of my drop-downlist.

But what I need is for it to be modifiable based on quantity at the same time

So I have the regular: =INDEX([RETURN VALUE],MATCH([DROP-DOWN LIST],[COST LIST])).
E.g. Introduction to Excel will return £5 and if I choose something else, the price will change.

Then I know the formula for quantity to add another unit of price to the total in another cell: =[QUANTITY]]*Cost[@Price]) / ==[QUANTITY]]*Cost[@Seats])

When I try to combine these two, it will look like it works but as soon as I use another value in the drop-down list, my 'cost' and 'seat' cells don't update, it'll retain the old information. Even if I delete the formula from the cell and re-enter the formula, now all it remembers is the old supplier code. I also can't use the same supplier code again below.

This is what I tried: =IF(INDEX($O$4:O11,MATCH(B4,$M$4:M11,0)),[@[Workshops run]]*Cost[@Price]) - I've also tried with vlookup... my colleague recommended using IF(ISNUMBER(MATCH - everything yields the same reaction.

In the table below, the old code was FK01 (Fake01) and the price per unit was £2000 - the price for FK04 is actually £1300 but it doesn't update.

Supplier codeWorkshops runSpaces availableCost
FK04
2​
50​
£ 4,000.00
FK02
1​
20​
£ 500.00
FK03
1​
20​
£ 1,000.00
FK04
1​
20​
£ 1,300.00
FK05
1​
30​
£ 1,750.00
FK06
1​
20​
£ 1,600.00
FK07
1​
25​
£ 2,000.00
FK08
1​
20​
£ 800.00
FK01
1​
#VALUE!​
#VALUE!​
FK02
1​
#VALUE!​
#VALUE!​
FK01
1​
#VALUE!​
#VALUE!​
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Lebene

New Member
Joined
Sep 20, 2019
Messages
40
Platform
  1. Windows
Hey guys, I actually can't believe it.

In frustration, I just said "UGHH I WISH I COULD JUST PUT *CELL" and did =VLOOKUP(B4,$M$3:$O$11,3,0)*[@[QUANTITY]]

and it worked..... so I figured it out after I stopped overcomplicating everything. :ROFLMAO:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

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