MS Excel 2010 hints and troubleshooting

Hi there,
These are a list of tips for Excel. I'm updating this as a list for me for future reference.

These are generally for Excel 2010


Dynamic list
Problem: 

static validation list

When you create a valid values drop down list for a field, you have to make room for future entries. This either creates a drop down list with lots of spaces, or the need to update the validation of the entries whenever you add a value to the validation table.

Solution 
This works for me

1. Make a valid values list
2. Create a Dynamic Range (I'll explain this below) in an area larger than the current lookup table size to allow for additional entries later.
3. When entering the Data Validation values - just call the named range you created

Lets say you want a dropdown list with valid statuses. It's best to create a separate tab for this. I'll call the tab Lookup, and create a . list in column A with some statuses.

Mark a range that goes below the current list

Create a named range <CTRL>F3

In the field Refers To type the dynamic range =OFFSET(Lookup!$A$2,0,0,COUNTA(Lookup!$A$2:Lookup!$A$20,1))

This will create a range called Lookup_Status starting at A2 until a maximum of A20. The COUNTA function makes sure that the list length is only as long as there are data in the list. 

To put this in a validation of a cell, select the cells to validate, select from the menu bar Data --> Data Validation.

In the Setting tab, select Allow List

In the source entry field type the name of the dynamic range   =Lookup_Status (with the equals sign)

and you're done.

Dynamic dropdown

After adding a value to the validation list

The validation drop down list will contain only the values you want. If you add values to the lookup table, the values will be added to the drop down list automatically.

HTH

Have fun !!