Microsoft Excel

creating controls

• form controls vs. activex controls
• Dynamic Range Names - offset(), index()
• =OFFSET(reference, rows, cols, [height, width])

lookup tables

• For the LOOKUP function to work correctly, the data being looked up must be sorted in ascending order. If this is not possible, consider using the VLOOKUP, HLOOKUP, or MATCH functions.

•  =INDEX( 'Cover Pieces'!material_widths, MATCH( E34, 'Cover Pieces'!material_names, 0 ) )

• Index( array, row_number, column_number ) - returns either the value or the reference to a value from a table or range
• Match( value, array, match_type ) - searches for a value in an array and returns the relative position of that item.
  • value is the value to search for in the array.
  • array is a range of cells that contains the value that you are searching for.
  • match_type (optional)
    • 1 (default) The Match function will find the largest value that is less than or equal to value. You should be sure to sort your array in ascending order.
    • 0 The Match function will find the first value that is equal to value. The array can be sorted in any order.
    • -1 The Match function will find the smallest value that is greater than or equal to value. You should be sure to sort your array in descending order.

other functions

• small() - return smallest value
• large() - opposite of small