using data validation with several different lists

[Guest]

Is it possible to tdo following:


Working with Data Validation,

On sheet 1 I have six lists named one, two, three, four, five, and Other.
they are located in columns A,C,E,G,I,K respectivley.

on the sheet named "Daily Log" I have individual drop down lists in each of
cells k22 thru k40 (excluding cells 26,27,28,34,35,) this drop down list is
used in conjunction with the lists on sheet 1.

in the source box of the data validation feature, I want to enter a formula
that can do the following:
let's assume that i am in the K22 cell (drop down list) and I want a list
from sheet 1 but first it is necessary to refer to cell A22 to determine
which list to use.

IF CELL A22 STARTS WITH NUMBER 1 THEN USE "LIST ONE" ON SHEET 1
IF CELL A22 STARTS WITH NUMBER 2 THEN USE "LIST TWO" ON SHEET 1
3
THREE
4
FOUR
5
FIVE
IF CELL A22 STARTS WITH ANY TEXT THEN USE "OTHER" ON SHEET 1

I do not have a clue how to do this, your help woul dbe appreciated and
would make my life easier.

Thank You,
Brian

 

[Paul Corrado]

Debra Dalgleish has a great page on this at her site.

http://www.contextures.com/xlDataVal02.html

 

[Jason Morin]

I would start with MVP Debra Dalgleish's website that
discusses using dependent lists in Validation:

http://www.contextures.com/xlDataVal02.html

What you may have to do in another cell is translate A22
to your list name. For example:

A22 = 158

First number is 1. We need to convert it to the
word "one", which is a named range for your first list.

In A23 use:

=VLOOKUP(LEFT(A22)*1,
{1,"one";2,"two";3,"three";4,"four";5,"five"},2,0)

Then use =INDIRECT(A23) in Validation as shown on Debra's
site.

HTH
Jason
Atlanta, GA