Excel 2013 Calculation Based on Substring Content

[eticket]

I have a spreadsheet in which column D always contains substrings of "women's", "women" or "womens" for women's clothing, or "men's", "men" or "mens" for men's clothing. Based on whether the substring is women's or men's, users enter a whole number value in column I, <= 20000 for women's and <= 10000 for men's.

After first validating the user's input in column I (<= 10000 or <= 20000), column J calculates a percentage, with a denominator of 227000 for women and 130000 for men.

Here's a data sample:

D3 I3 J3
Women's Pink Lace Scarf, 18 in. 6500 6500/227000
Men Lucky Brand Jeans, Blue, 34"(W) x 32"(L) 4100 4100/130000

I've tried data validation, if/then/or statements, and an array, but I haven't been able to figure this out. Can you help me?

Thank you!

eticket

 

[Scott Huish]

It's not clear what your question is? You said you had your data validation working for Column I. So what do you need help with exactly?

 

[eticket]

It's not clear what your question is? You said you had your data validation working for Column I. So what do you need help with exactly?

My apologies for being unclear. I'll try to be less confusing this time.

I need a way to examine a substring in cell D3 that always contains any one of the words mentioned. If the clothing item is women's, the user is required to enter a number <= 20000 into cell I3. I want Excel to validate that the number entered is <= 20000. If > 20000, the user will get prompted to enter a valid number. (I know pretty well how to use data validation but not with a complex formula.)

Once I3 has a valid number, I need a calculation in J3: I3 / 227000 (for women's clothing).

Is that better? Thank you for helping.