Search : in
By :

Excel: alphanumeric if then statements

Last answer on Aug 26, 2009 10:46:40 pm BST imainternforhcs, on May 28, 2008 7:56:08 pm BST 
 Report this message to moderators

Hello,
I am setting up a database template for people who are not Excel trained and I am attempting to make the input easy for the users as well as myself when I begin analyzing the data. I am trying to an if-then statement or create a macro (I know nothing about macros) that will allow me to have the users select their county for a list validation and the county number appear the cell to the right of the county name.
like this

County County Number
Kioskea 67856

Any advice would be helpful.

Configuration: Windows XP
Firefox 2.0.0.14

Best answers for « Excel: alphanumeric if then statements » in :
Basic Excel Formulas ShowBasic Excel Formulas Below are some basic formulas for Microsoft excel: Basic formula : ADDITION cell A1 to A10 = sum (A1: A10) AVERAGE cell A1 to A10 = average (A1: A10) MAXIMUM cell A1 to A10 = max (A1: A10) MINIMUM...
Avoid duplicates in Excel ShowAvoid duplicates in Excel In order to avoid duplication when encoding in a column from an excel sheet: take the conditional format on the first cell under the headings (eg A2) choose the following formula:...
[VBA] Detecting changes in cell Show[VBA] Detecting changes in cell The Event Change feature of a sheet will detects the change in the active cell but it gives no information about the content. The example given below will help you to find out if the cell was changed,...
Spreadsheets - Conditional expressions ShowWhat is a Conditional Structure? Conditional structures are instructions that allow to test if a condition is true or not. Conditional structures may be associated together. To successfully execute these tests using logical functions, spreadsheets...
Ask your question Reply

1

Pablositisimo, on Feb 4, 2009 10:30:58 pm GMT

You don't need a macro for that.

You can create a validation list on a hidden worksheet that has 2 columns:
Column A contains the country names; column B contains the number values.
Then create 2 named ranges:
Range named "Country" that refers to column A only, and
Range named "Country_Codes" that refers to both columns A & B.

On your user input form, add validation to the cell that the user will input the country name.
The validation type will be a list, and in the list textbox enter "=Country" (omitting the quotes).
Make sure you check "In-Cell Dropdown"

Now for the field to the right that you want to contain the number:
Use the formula "=VLOOKUP(whatever cell has the country name,Country_Codes,2,FALSE)"

Make sure that the lookup cell is protected.

   
Reply to Pablositisimo

2

xmux, on Mar 22, 2009 10:08:37 pm GMT
  • +3

And for example if i need a macro

like:

If Selection.Cells(x, 8) <> _
"AD" Or "AE" Or "AF" Or "AG" Or "AI" Or "AL" Or "AM" Or "AN" Or "AO" Or "AQ" Or "AR" Or "AS" Or "AT" Or "AU" Or "WS" Or "YE" Or "YT" Or "ZA" Or "ZM" Or "ZW" Then
MsgBox "Account Name cannot be empty! " & "Place: Row: " & x & " Column: 8"
GoTo Einde
End If

Why i got "run time Error: mismatch"??

Thanks

   
Reply to xmux

3

 Srini, on Aug 26, 2009 10:46:40 pm BST
  • +1

Try this

If Selection.Cells(x, 8) <> "AD" Or Selection.Cells(x, 8) <>"AE" Or Selection.Cells(x, 8) <>"AF" Then
MsgBox "Account Name cannot be empty! " & "Place: Row: " & x & " Column: 8"
GoTo Einde
End If

   
Reply to Srini
Ask your question Reply
They need your help