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.
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 code | Workshops run | Spaces available | Cost |
| 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! |
