Help please, going mad !!!

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Can anyone please help with a formula that I can't get to work, assuming it
is possible.

What I am trying to do is create a sheet so when interviewing school leavers
their qualifications can be entered and the sheet provides quick answers as
to the key skill requirements.

Example.If A b or c is entered in one cell,( this column which could be
headed "GCSE English" , the word "No" would appear in another cell which i
could head the column "Key skills Comms"

I can get this to work:
In cell B10 I put the formula =IF(A10="a","YES","NO")

But it only checks for "A", I want it to check for "B" and "C".

Ideally, it would do something useful if grades D,E,F,G appeared as well !!!

Any help appreciated

Bob
 
Bob

If I have understood you properly, this
formula will do the job:

=IF(OR(A10={"a","b","c"}),"Yes","No")
 
...
...
Example.If A b or c is entered in one cell,( this column which could be
headed "GCSE English" , the word "No" would appear in another cell which i
could head the column "Key skills Comms"

I can get this to work:
In cell B10 I put the formula =IF(A10="a","YES","NO")

But it only checks for "A", I want it to check for "B" and "C".

Ideally, it would do something useful if grades D,E,F,G appeared as well !!!
Click to expand...

=IF(LEN(A1)=1,LOOKUP(A1,{"A";"B";"C";"D";"E";"F";"G"},
{"YES";"YES";"YES";"NO";"NO";"NO";"NO"}),"")

but this assumes A10 would either be blank or contain a single letter from A-G.
 
How about

=CHOOSE(MATCH(A1,{"a","b","c","d","e","f"},0),"No","No","No","Yes","Maybe","Give
Up")

a,bc,d,e,f,g occupy positions 1,2,3,4,5,6 respectively in the array
{"a","b","c","d","e","f"}. the MATCH function returns that position and apsses
it to the CHOOSE function, which uses that number to pick out either No, yes
maybe etc from the 6 options listed - "No","No","No","Yes","Maybe","GiveUp"

With the IF function:-

=IF(OR(A1="a",A1="b",A1="c"),"No",IF(A1="d","Yes",IF(A1="e","Maybe",IF(A1="f","G
iveUp",""))))

Limited to 7 nested IFs.

Another option is to have a table of values in the sheet somewhere along with
the message you want returning, and then use VLOOKUP to go get that value, eg:-

With your data a,b,c,d,e,f in A30:A35 and the No,No,No,Yes,Maybe,Giveup in
B30:B35

=VLOOKUP(A1,A30:B35,2,0)
 
Now when I paste the formula into the cell I'm getting the formula showing
in the cell instead of the formula working?
 
Back
Top