[mso] WinXP, Excel 2003, and Wild Cards in formulas

From: "Graham Jones" <agjones@xxxxxxxxxxxxxx>
To: <mso@xxxxxxxxxxxxx>
Date: Thu, 2 Apr 2009 15:51:40 +0100
Dear All, please bear with me and read this, I've tried to explain clearly so 
it's a bit long but I'm really stuck!
I have been provided with a spreadsheet subdivided into groups of 5 rows, where 
Row 1 is a Header Row, A1=?Student name?, B1=?Current/Future Study Programme?.

This means that Row 2 contains (Column A) ?Jane Smith?, (Column B) her Current 
Study Programme. Rows 3 to 7 are then (Column A) blank, and (Column B) Future 
Study Programme CHOICES numbered Ascending 1 to 5, i.e. B2=?1?, B3=?2?, B4=?3?, 
B5=?4?, B6=?5?.

The whole pattern then repeats for ?John Smith? in Rows 8 to 14 and so on for 
500 names!

So that I can apply Autofilter I have first inserted a new column A, such that 
the above explanation now refers in all cases to Column B instead of A, and 
Column C instead of B.

I have the following formula in cell A2, Filled Down for 500 Rows, so that the 
IF FALSE value replicates the cell above itself, UNLESS a new name appears in 
NEW Column B:
=IF(AND(B2>=1,B2<=5),A1,B2)

As such, I now have the appropriate Student Name on ALL rows relating to 
him/her, so when I Autofilter on Column B for, say, Future Study Programme 
choice 2, Column A shows who is applying for it.

However, the NAME of that choice is in Column C, which I haven?t yet mentioned. 
It contains one of three Current Study Programmes, ?Foundation, ?A-Level? or 
?Private? on Row 2, and a various length text string name for the Future Study 
Programme in Rows 3 to 7.

As such, I then added a new Column D containing the following IF formula 
similar to the previous one, because I need also to know which Current Study 
Programme the student is on:
=IF(OR(C2="Foundation",C2="A-level",C2="Private"),C2,D1)

Still all well and good - When I Autofilter on Column B for Future Study 
Programme CHOICE 2, I now have the appropriate Student Name on all rows 
relating to him/her, the Future Study Programme choice abbreviation, AND their 
Current Study Programme!

However, and here?s the rub and the question for all you brainacs that is 
killing me?...
The Foundation Current Study Programme has about 20 subdivisions, e.g. 
?Foundation S/BUS A?, ?Foundation S/BUS B?, ?Foundation S/LAW?, Foundation 
J/BUS A? and so on, and I need to know which specific subdivision the 
individual student is following!

I really do not want to have to list ALL the subdivisions in the nested OR in 
my above formula, not least because the list changes, and so I have tried to 
use a Wild Card *:
=IF(OR(C2="Foundation*",C2="A-level",C2="Private"),C2,D1), and also
=IF(OR(C2="Foundation"*,C2="A-level",C2="Private"),C2,D1)

Both of them return the IF FALSE by replicating the content of D1, so my 
question is ? please can someone put me out of my misery and correct (I hope!) 
the first stage of my OR condition, or explain why it won?t work (had a similar 
sort of ?killer? problem the other day and ultimately found out that COUNTIF 
simply doesn?t like nested AND or OR!

Thanks for reading this far and for any help you can give.

Best wishes
Graham

############################################################
Please consider the environment before printing this e-mail.

This e-mail message, including any attachments, is for the sole use of the 
person to whom it has been sent and may contain information that is 
confidential or legally protected. If you are not the intended recipient or 
have received this message in error, you are not authorized to copy, 
distribute, or otherwise use this message or its attachments. Please notify the 
sender immediately by return e-mail, and permanently delete this message and 
any attachments. Study Group makes no warranty that this e-mail is error or 
virus free. Study Group International Ltd, Registered in England, number 
3108030, Registered office: 1 Billinton Way, Brighton, East Sussex BN1 4LF. 
Study Group International Ltd is the parent company of Bellerbys Educational 
Services and Embassy CES.
############################################################

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top.  This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister group. 
 This group will not allow for posting of emails, but will allow you to join 
and share problem files, templates, etc.:  
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE 
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
[mso] WinXP, Excel 2003, and Wild Cards in formulas

Other related posts:
