E-TIPS

Finance, Valuation, Risk & Modelling

your one-partner solution for building skills and knowledge

Change Your Life with Excel

Keeping it Valid

elcome to Change Your Life with Excel, once

again jam-packed with spreadsheet goodness.

The 2 tips presented below are closely related. The first

is quite straightforward and many of you may be familiar

with it. However, the 2nd

tip goes a step further and gets a

bit fancy, using a trick from a rarely used Excel function.

As always, the features covered here are based on Excel

2003 however most can still be found in other versions.

Time Saving Tip

Entering Data from a List

If you’ve ever found yourself having to enter the same

items into a worksheet again and again then I hope that

this tip will save you some time and frustration. With a

little bit of setting up you will no longer need to manually

type items, but instead you will just select them from a

list.

To do this you use a feature in Excel called Data

Validation.

As an example, suppose you have a worksheet column

into which you repeatedly enter staff names (perhaps an

attendance record or a schedule of each time staff take

leave).

Firstly you need to set up a list of all the staff names. For

good housekeeping this should always be done in its own

worksheet. Once done:

Highlight the whole list of staff names

Name the highlighted list by typing a name in

the Name Box as in the following illustration

(when choosing a name for a range of cells you

cannot use spaces or start the name with a

number). In this example, call the list

“StaffNames”

Press ENTER to store the name

By naming this list it allows you to store it in a different

worksheet to the one in which you use the staff names.

The name “StaffNames” typed into the Name Box

Now select your main worksheet – the one in which you

will enter and use these staff names (this must be in the

same workbook or file).

Highlight all the cells you are ever likely to enter

staff names into

From the Menu Bar select Data | Validation

In the dialog box change “Any value” to “List”

using the drop down menu

Click into the Source field

Press F3. This will bring up a menu of all the cell

ranges you have named

Pressing F3 from the Source field

Select “StaffNames”

Click OK twice

W

By Leigh Drake

Director

Arc Business Processes

www.arcbusiness.com.au

E-TIPS

Finance, Valuation, Risk & Modelling

your one-partner solution for building skills and knowledge

Now, whenever you select a cell into which you need to

enter a staff name, notice that a drop down arrow

appears on the right edge of the cell.

Click the drop down arrow

From the list of staff names which appears

simply select the name you require

Click the drop down arrow to reveal the list of staff names

Trick

Creating a Conditional Drop Down List

Continuing with the previous example, what if there were

hundreds of staff. This would make the drop down list

you’ve just created extremely long and cumbersome.

Wouldn’t it be great if you could enter a department name

in an adjacent cell (again using a drop down list) and

automatically have the staff names drop down list show

only the staff in that department?

The answer to this question is yes, it would be great and

yes, Excel can do it! As always it just takes a little bit of

setting up.

Once again, it is good practice to set up the list of staff

and departments in a separate worksheet.

Type in the department names across the top of

the worksheet

Underneath each department name type in the

staff names which belong to that department as

shown in the following illustration

Now you need to set up a drop down list for the

department names exactly as before:

Highlight the department names (cells A2:E2 in

the illustration)

Name the highlighted cells by typing a name in

the Name Box , say “Department”

Press ENTER to store the name

Set up of staff names and the departments they belong to

Highlight all the cells you are ever likely to enter

department names into

Use Data Validation as previously shown,

selecting the name “Department” after pressing

F3

Next, create names for each departmental list of staff

names. These names will simply be the department

names.

Highlight the range of cells containing the list of

names, ensuring you include all department and

staff names (some lists will be longer than

others)

From the Menu Bar select Insert | Names |

Create

In the dialog box ensure only Top row is ticked

Click OK

Select Top row in the Create Names dialog box

E-TIPS

Finance, Valuation, Risk & Modelling

your one-partner solution for building skills and knowledge

How ranges are named using Create Names

Finally, the trick which makes this all work.

Highlight all the cells you are ever likely to enter

staff names into (in column B in the illustration

below)

From the Menu Bar select Data | Validation

In the dialog box change “Any value” to “List”

using the drop down menu

Click into the Source field

Type in “=INDIRECT(A2)“ (don’t type the quote

marks) where cell A2 will contain the first

department name

Click OK

Type the INDIRECT function in the Source field

The INDIRECT function returns the contents of a range

which is specified as text (huh!?). In other words if cell A2

contains the text “Administration”, this is equivalent to

saying “=INDIRECT(Administration)” which tells Excel

that “Administration” is actually a range that you’ve

named and it will return all the staff names within that

range. I know – Excel can be a bit confusing at times!

Now whenever you enter a department name, the

adjacent staff names drop down list only shows names

for staff in that department.

Drop down list of staff names only shows names for the adjacent

department

If this leaves you craving more Excel magic I’d strongly

recommend a visit to www.mrexcel.com to download

some of his video podcasts which reveal more “tricks of

the trade”.

Until next time, happy Excelling!