Assigning Values to a Data Validation in conjuction with another formula

**Phrick** · 05-12-2014, 12:58 PM

Hi excellions!

Got a problem I've been struggling with.

What I need is for my formula to include additional criteria whereby if a word is 'lower' than another, it determins whether the formula will be completed, e.g. If High is selected in cell 1 and Low is selected in cell 2, then formula completes but if Low is selected in cell 1 and High in cell 2 it won't . However obviously excel can't tell the words apart so I thought I may have to assign a numerical value to get it to work.

I realise this may seem like nonsense so I've made a draft spreadsheet to attach which I think explains it better.

I found something which was on the same lines as I wanted but couldn't get it to work in conjuction with my existing formula.
http://www.excelforum.com/excel-gene...down-list.html

Testworkbook.xlsx

Any help would be greatly appreciated.

Kind Regards

Rick

**Jonmo1** · 05-12-2014, 01:04 PM

You can try

=IF(MATCH(B3,$E$1:$E$6,0)>MATCH(B2,$E$1:$E$6,0),IF(OR(C$2="",C3="",B2=B3),"N/A",IF((C3-(C3*0.2))>C$2,"Yes","No")),"")

**Phrick** · 05-12-2014, 01:13 PM

That seems to work perfectly! I'm in wonder at how you guys do this so quickly.

Just to help me understand, can you briefly explain how the formula works? Is it that the MATCH function in this case has assigned the list a numerical value and the > determins whether it is higher or lower?

Thanks

**Jonmo1** · 05-12-2014, 01:16 PM

Yep, the MATCH function returns the position # where a value (B2 or B3) is found within a list (E1:E6).

Then it's an IF to compare the result of each match.

**Phrick** · 05-12-2014, 01:18 PM

So simple when you think about it but haven't come across the MATCH function in my googling.

Well, works a treat and would never have worked it out on my own, thanks again Jonmo!

**Jonmo1** · 05-12-2014, 01:25 PM

You're welcome.

Assigning Values to a Data Validation in conjuction with another formula

LinkBack

Thread Tools

Rate This Thread

Display

Assigning Values to a Data Validation in conjuction with another formula

Re: Assigning Values to a Data Validation in conjuction with another formula

Re: Assigning Values to a Data Validation in conjuction with another formula

Re: Assigning Values to a Data Validation in conjuction with another formula

Re: Assigning Values to a Data Validation in conjuction with another formula

Re: Assigning Values to a Data Validation in conjuction with another formula

Thread Information

Users Browsing this Thread

Similar Threads

Need help with Data Validation Formula for a set of numerical and text-based values

Data validation formula to show values of not empty cells

Creating formula using multiple Data Validation values

Assigning numerical value to text in data validation list

Assigning values to a Validation Menu

Bookmarks

Bookmarks

Posting Permissions