Validation

1. Type the entries in one column or row.
   
   You can name the list by selecting the entries and then clicking in the Name box on the Formula bar and entering a name, and then press Enter.
   
   
2. Next, select the cell where you want the dropdown list.
   
   
3. On the Menu bar, go to the Data menu, click Validation, and then click the Settings tab.
   In the Allow box, click List.
   
   
4. Enter the name of the list or its location.
   
   Make sure the reference or name is preceded with an equal sign (=).
   
   Make sure the In-cell dropdown check box is selected.
   
   If your list is short, you can type the entries directly in the Source box, separated by commas.

Labels: Customize, General, Tips

Format don't show

1. Select the range
   
   
2. Goto Format>Conditional Formatting
   
   
3. Select Formula Is
   
   
4. Enter
   =A2=A1
   
   
5. Click the Format button.
   
   
6. Select a font color to match the cell background color.
   
   
7. Click OK and OK

1. Select the range
   
   
2. Go to Format>Conditional Formatting
   
   
3. Choose Formula Is
   
   
4. Enter
   =COUNTIF($A$2:$A$100,A2)>1
   
   
5. Click the Format button.
   
   
6. Select a font or background color for highlighting.
   
   
7. Click OK and OK

Labels: Customize, Formats, Formulas, General, Tips, Tutorials

Cardinal numbers

From the Microsoft Knowledgebase collection:


How to Convert a Numeric Value into English Words - 213360

This support article contains the VBA code needed to create a User Defined Function, You can, then, change 32.50 into "Thirty Two Dollars and Fifty Cents" by entering something like the following formula into a cell:

=SpellNumber(32.50)


Also:

From John Walkenbach:

BAHTTEXT?
If you use Excel 2002+, you may have discovered the new BAHTTEXT worksheet function. This function converts a number to Thai text, and adds a suffix of "Baht".
This is the only built in function that translates numbers to words and then only in Thai.

For Access see:
Access- Numbers to Words


To create cardinal numbers in Word, see:
Word - Numbers to Words



See all Topics excel

Labels: Formats, General, Reference, Tips, Tutorials

Programmatically pick cells

Microsoft has provided 22 ways to select cells/ranges by using Visual Basic procedures in Excel.

Here are a few of the subjects covered:

• How to Select a Cell on the Active Worksheet
  
  
• How to Select a Cell on Another Worksheet in the Same Workbook
  
  
• How to Select a Range of Cells on the Active Worksheet
  
• How to Select a Named Range on a Worksheet in a Different Workbook
  
  
• How to Select a Cell Relative to the Active Cell
  
  
• How to Select the Union of Two or More Specified Ranges
  
  
• How to Select the Intersection of Two or More Specified Ranges
  
  
• How to Select the Last Cell of a Column of Contiguous Data
  
  
• How to Select the Blank Cell at Bottom of a Column of Contiguous Data

How to select cells/ranges by using Visual Basic procedures

Dick Kusleika has some comments on the coding: How to Spread Bad Habits See all Topics excel

Labels: Formulas, Functions, General, Shortcuts, Tips, VBA