Unofficial Excel Stuff

Book

Suggestions

Home Page

Bloglines

1906
CelebrateStadium
2006

OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal

Saturday, April 15, 2017 – Permalink –

Custom Lists

Your Way

At times, you will want to enter the same information repeatedly in a spreadsheet.
It could be employee names, products or State names.

Here is a way to produce the information when needed.

Make a list of the items.
Enter the data in, say, A1, B1, C1 etc.
Be sure to enter in the right order; maybe Supervisor and then team members in alphabetic order.

Select all of the cells.
Go to Tools>Options and choose the Custom lists tab.
Click Import.
OK your way out.

Now you can delete the entries on the spreadsheet.

Choose any cell and type one of the items that are on your Custom List.
Click and drag on the fill handle; the tiny square at the bottom right corner of the selected cell.
As you pull, Excel will duplicate your list.

(In Excel 2007+, go to the Office Logo. Choose Excel Options. Edit Custom Lists will be on the Popular page.)

Also see:

The First Shall Be Last
by Dick Kusleika

Sorting by Color
by Chip Pearson

See all Topics excel
Labels: Customize, General, Lists, Reference, Tips, Tutorials

<Doug Klippert@ 3:19 AM

Comments: Post a Comment

Monday, June 22, 2015 – Permalink –

Choose List Criteria

Set values

Somewhere in the workbook, create three named ranges:

Type (elements of this named range should be Sedans, SUVs)

Sedans (elements of this named range should be Toyota Camry, Nissan Altima, Mazda 6)

SUVs (elements of this named range should be Toyota RAV4, Ford Escape, Jeep Liberty)

Select A1:A10, go to Data>Validation select List. Under source, type:
=Type

Select B1:B10, go to Data>Validation select List. Under source, type:
=INDIRECT(A1)

Choose a vehicle type from the selection that appears in the A column, and the choices in B will be limited to the entries in the appropriate list.

To create a named range, select the list: Hit Ctrl+F3. Enter the name and hit OK (or Enter), or Type the name in the Name Box on the left side of the Formula bar and hit the Enter key, or Go to Insert>Name. If the list selected includes a label/title, choose Create, otherwise choose Define, type the name and hit Enter).
Also Contextures.com:
Data Validation -Create Dependent Lists

See all Topics excel
Labels: Customize, Formulas, General, Lists, Reference, Tips, Tutorials

<Doug Klippert@ 3:16 AM

Comments: Post a Comment