The IF function is frequently useful --
IF(CONDITION, action or value if CONDITION is true, action or value if CONDITION is false)
If you have standard text to enter, maybe one of 5-10 possible entries, maybe using the INDEX function would help.
List of possible values in E1:E9
If you want the first value, you enter 1 in B1, 3rd value in the list, enter 3, ...
Copy this formula down Column C -- =INDEX($E$1:$E$9, B1)
Once you have the values you want in Column C, Add a column, and then use Copy | Paste Special | Values to copy the formula results to your new column
MATCH provides the complimentary operation for INDEX - given a value you want to find, look it up in an array of possible values, and return the index number for that value's location in the array.
MATCH (location of possible values, lookup value, match type)
If match_type is omitted, it is assumed to be 1.
If match_type is 1, array of values must be sorted in ascending order and
MATCH finds the largest value that is less than or equal to lookup_value.
If match_type is 0, MATCH finds the first value that is exactly equal to
lookup_value. Lookup_array can be in any order.
If match_type is -1, array of values must be sorted in descending order and
MATCH finds the smallest value that is greater than or equal to
lookup_value.
The INDEX function technique above can be combined with List Box or Combo Box tools from the Forms (or Control Toolbox) toolbar.
Once you've added your List Box or Combo Box the key is to right-click the Box and choose Format Control...
In the Control tab you specify (using the example above), Input Range: $E$1:$E$9
Choosing a value from the list will populate the cell specified in Cell Link, say: B1
You'd still have to convert this number to a value in another column using the INDEX function discussed above.
The List Box or Combo Box will only populate one cell with a value.
If you need to calculate or dynamically enter a cell reference for a formula, INDIRECT is a useful function. SUM(INDIRECT("A"&D5):A15) would concatenate A with the row number specified in D5 and sum in column A from that row through A15.
Close relatives of the INDEX function are VLOOKUP, HLOOKUP, and LOOKUP
For Example:
The lookup table of the INDEX example would be extended to two columns.
Column D would number the values in Column E, that is, Column D contains the numbers 1 through 9.
In Column B you enter the number of the value you want to pick from the lookup table.
Column C contains the formula: =VLOOKUP(B1,$D$1:$E$9,2)
This translates to ==> Lookup the value in B1 in the first column of the table in $D$1:$E$9, find the value in a row in the table, return the value in the 2nd column of the table.