Formula for data validation drop down that is dependent on a variable value.

**Johnny247** · 12-14-2019, 12:46 PM

Hi all,
I am trying to add some data validation to a column of cells on the tab RM order. (I think data validation would be the correct solution anyway!).
Column F on the attached file is a list of ingredients, that is populated via drop downs in column C.
This then generates all of the required ingredients, and shows the required quantity, and where the ingredients are, as well as if there is enough.
Now. Some of the ingredients can be substituted for alternatives. These are all on a tab named "Lookup List". Ideally, I would love a formula that checks each ingredient, and if there is not enough, it automatically checks the next on the list and if enough stock, uses that one but I suspect that is rather complicated, so I thought an easier way would be via data validation, so if the stock is zero (not enough), then you can use the drop down and select an alternative for that product.
I have tried to do it via a named range with name manager, but the validation will only work with a specific cell or column, and I have set the named range as a bigger range.
Can anyone help with a formula to resolve this please?

Thank you in advance for any help.

**JeteMc** · 12-17-2019, 12:33 PM

Looking on the RM order sheet I see that there is not enough of LIMMS002 (cell F9), however looking on the Lookup list sheet under GREEN RETAIL SMOOTHIE V2 I don't see anything that seems to be a substitute for LIMMS002, as the other four items are already listed. It may help if you give a specific example of an ingredient that has a substitute.
That said if you put a drop down in a cell containing a formula, the result of the drop down will override the formula so that if you change cell C6, for example, LIMMS002's substitute would still appear in cell F9.
It seems to me that a better option would be to conditionally (if N9="Not Enough") have cell O9 show the name of the substitute and P9:V9 to show %:amount to bring back of the substitute.
Let know if you have any questions.

**Johnny247** · 12-17-2019, 02:33 PM

Hi JeteMc. I think I see your point.
Some products have a replacement ingredient, some don't. I like the idea of putting the alternative in the next columns though, as that kind of gets the desired result by default.
How would that work with multiple substitute ingredients? For example, SUMMER BERRY AND BANANA SMOOTHIE uses STRSE006
, but if there is not enough, it "could" use any of these:STEGH901 STRSE006 STEEV901 STRMS902 STRAW001 STRMP901!. It would be dependant on what there was stock of. How would that work do you think? would it need another helper column, and label each one?
Many thanks in advance.

**JeteMc** · 12-17-2019, 09:56 PM

Perhaps this will work.
1. Make a list of all substitutes as modeled in column B on the Substitute list sheet.
2. Group the substitutes as modeled in column A
3. Find the amount of each substitute (column C) using:

Formula:

Please Login or Register  to view this content.

On the sheet:
1. In column Q, identify the group for the Stock coded to be substituted using: =IF(N21="Not Enough",INDEX('Substitute list'!A$2:A$10,MATCH(F21,'Substitute list'!B$2:B$10,0)))
2. In column P identify the amount of the most plentiful substitute in that group using:

Formula:

Please Login or Register  to view this content.

3. In column O display the name of the substitute using: =INDEX('Substitute list'!B2:B10,AGGREGATE(15,6,(ROW(B2:B10)-ROW(B1))/('Substitute list'!A2:A10=Q21)/('Substitute list'!C2:C10=P21),1))
My thinking is that going directly to the most plentiful substitute eliminates the need for repeated searches.
Let us know if you have any questions.

**Johnny247** · 12-18-2019, 07:06 AM

That is great, thank you. That works I think. How can I tidy the formulas up though please so it only populates O and P when there is a value greater than 0?
I have tried amending the formulas at the start with =IF(N16<>0,"","") but it does not work.
Thanks in advance.
I have fixed this now, but noticed something else; the formula in column P is suggesting to bring all available stock back, rather than what is needed, so should deduct the number in column I. I have tried to add this in as a sum either at the beginning, or end of the formula, but it is not reading correct.
Also on the formula in column P, I have added =IF(N17=0,"", which fixes if the cell has just a 0, but if there is a number in the cell, it returns a FALSE value. How can I get that to remove and show an empty cell please?
Finally, on the formulas in O & P, if "Not Enough" is shown in column N, I get a #NUM! value. Is there a way to get that to read something like "Zero Stock"?

Many thanks in advance.

**JeteMc** · 12-18-2019, 10:19 AM

Try the following:
1. Select P5:P39 and apply the following custom number formatting 0;-0;;@
2. Select O5 and paste the following into the formula bar before copying down:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**Johnny247** · 12-18-2019, 12:32 PM

Thank you for that. I have tried, but it does not appear to have worked.
I have made some progress on the second part of my reply above;
I have attached where I am up to so far.
I have added two extra columns (R & S). S now replicates column L, just for the substitute stock, and R is a sum to subtract external stock from required substitute stock to give a balance of what is needed, which fixes brining it all back as mentioned above, but as with the other two columns, I still get #NUM!
(The last two columns, T & U are what I will need to try and figure out next, which is where has the oldest stock and prioritise bringing it back first)
As always, any help is appreciated.
Kind Regards

**JeteMc** · 12-18-2019, 04:22 PM

For O5 and down: =IF(N5<>"Not Enough","",INDEX('Substitute list'!$B$2:$B$136,AGGREGATE(15,6,(ROW($B$2:$B$136)-ROW($B$1))/('Substitute list'!$A$2:$A$136=Q5)/('Substitute list'!$C$2:$C$136=P5),1)))
For P5 and down: =IF(N5<>"Not Enough","",AGGREGATE(14,6,('Substitute list'!C$2:C$136)/('Substitute list'!A$2:A$136=INDEX('Substitute list'!A$2:A$136,MATCH(F5,'Substitute list'!B$2:B$136,0))),1))
For Q5 and down: =IF(N5<>"Not Enough","",INDEX('Substitute list'!A$2:A$136,MATCH(F5,'Substitute list'!B$2:B$136,0)))
Still get #NUM! in rows 21:22 as COCMS002 and PFRMS002 are not on the Substitute list. If these items are not to be included then it might be best to wrap the formulas inside of IFERROR and either display a blank or message like "No Substitute".
Note that if the substitute list may grow or shrink then it may be better to turn columns A:C on that sheet into a table.
Will wait to hear how this goes so let us know if you have any questions or if this much at least is resolved.

**Johnny247** · 12-18-2019, 06:21 PM

Thank you very much.
Column Q works fine, Column O works fine (I have put IFERROR around as suggested and fixed that thanks).
Column P, is still not reading correctly unfortunately. It is calling for all available substitute stock to come back, rather than the required amount. Using P26 as an example, the required amount (or shortfall, -6292) is indicated in K26, however P26 calls for everything. P30 is another example of this(-11968).
Thanks in advance as always.

**JeteMc** · 12-18-2019, 07:25 PM

Column P wasn't meant to show the amount to be brought back, it was only meant to find the most plentiful substitute so that if there was not enough STRSE006 for the SUMMER BERRY AND BANANA SMOOTHIE we would not have to add a whole lot of helper columns to look at each substitute individually until found one was found that had enough. Because if the substitute that has the greatest quantity is not enough then there is truly "Not Enough". I feel that column R can be used to display the bring back amount of the substitute once found and column P could probably be included in the "Hide this column" columns.
As for column R I suggest the following formula: =IF(O5="","",IF(ABS(K5)>P5,"Not Enough",ABS(K5)))
Let us know if you have any questions.

**Johnny247** · 12-20-2019, 07:49 AM

Apologies for the delay.
All works as it should now thank you, with the exception of column R, If there is no stock, and no substitute, it is returning the value in column K as an amount to bring back, even though there is not any, when it should return a value of Not enough.
Apart from that, it is some great work, thanks.

**JeteMc** · 12-20-2019, 10:59 AM

In the copy of the file attached to post #8, column R would return #NUM! if there was no substitute, such as in cell R21. That could be remedied by modifying the formula in cell R14 (then copying down) to read:

Formula:

Please Login or Register  to view this content.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

**Johnny247** · 12-20-2019, 11:53 AM

Ahh!
I modified the formula in Column O to remove the #NUM! message with this:-

Please Login or Register  to view this content.

which meant it did not work using the above! I guess there is no way to do both, so it will read better with a #NUM! error rather than the wrong information.
Many thanks and kind regards,

**JeteMc** · 12-20-2019, 12:20 PM

You could use the formula in post #13 for column O and use the following for column R: =IF(O5="","",IF(O5="No Substitute","Not Enough",IF(ABS(K5)>P5,"Not Enough",ABS(K5))))
Let us know if you have any questions.

**Johnny247** · 12-20-2019, 06:30 PM

That's it! that works! thank you very much for the help.

**JeteMc** · 12-20-2019, 08:16 PM

You're Welcome.

Formula for data validation drop down that is dependent on a variable value.

LinkBack

Thread Tools

Rate This Thread

Display

Formula for data validation drop down that is dependent on a variable value.

Re: Formula for data validation drop down that is dependent on a variable value.

Re: Formula for data validation drop down that is dependent on a variable value.

Re: Formula for data validation drop down that is dependent on a variable value.

Re: Formula for data validation drop down that is dependent on a variable value.

Re: Formula for data validation drop down that is dependent on a variable value.

Re: Formula for data validation drop down that is dependent on a variable value.

Re: Formula for data validation drop down that is dependent on a variable value.

Re: Formula for data validation drop down that is dependent on a variable value.

Re: Formula for data validation drop down that is dependent on a variable value.

Re: Formula for data validation drop down that is dependent on a variable value.

Re: Formula for data validation drop down that is dependent on a variable value.

Re: Formula for data validation drop down that is dependent on a variable value.

Re: Formula for data validation drop down that is dependent on a variable value.

Re: Formula for data validation drop down that is dependent on a variable value.

Re: Formula for data validation drop down that is dependent on a variable value.

Thread Information

Users Browsing this Thread

Similar Threads

VBA for Dynamic Dependent Drop Down Data Validation Lists

[SOLVED] Validating the Validation (Data Dependent Drop Down Lists)

Required Dependent Data Validation drop down

[SOLVED] Data Validation - Drop-down Dependent List

[SOLVED] Multiple Dependent Data Validation drop down boxes

Dependent Data Validation/Drop Down

Excel 2007 : Data validation drop-down list dependent on another cell

Bookmarks

Bookmarks

Posting Permissions