Lists and Variables?

Seenage

New Member
Joined
Oct 24, 2006
Messages
9
Hi, this is my first post, so please bear with me:

Here is what I need to do.

I have a List, displayed as a drop down list using Data-> Validation -> Lists.

Now, for my current formula, I need to check and see if 3 things ( in an array ) are true, and one of those 3 things is the data in that list.

What I'm looking for is a way so that I cycle through all the values in the list using just one statement.

Some pseudocode to help explain:

If Name = "Something" AND If Date = Date-2 AND If ArrayValue = Yes
THEN display a string of text
Else If Name = "Something Else" AND yatta yatta

So, my problem is that I can't make more than 7 nested if statements. What I WANT it to be is:

If Name = ValueInList AND If Date = Date-2 AND If ArrayValue = Yes
THEN display a string of text
ALSO goto next value
Else Display Nothing

Is this possible?

Thanks in Advance.

~Seen
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Are you wanting to do this with a formula or code?

What are the ranges involved?

What is date and arrayvalue.


Tony
 

Seenage

New Member
Joined
Oct 24, 2006
Messages
9
I want to do this with a formula.

Here is a mock setup of my data:

ColumnA B C D E
Name 1 Name 2 Phone # Date Call?


Now, those are the values that I have to work with.

Heres what I am trying to do:

I've got a way to compare the date with todays date (datedif function) and checking to see if column E = Yes or No is easy.

However, I want to check if the Name in Column A is a valid name (one that is in the List range). This range is being provided by another workbook (names.xls) . Is there a way to go through each name in that dynamic range?
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

How about something like

=if(and(e5="yes",countif([names.xls]sheet1!namerange,a5)>0),"do if valid","")

You will have to complete the relevant ranges. Basically, if column E has a yes, and the count is >0 the name is in the valid list on names.xls, then do your processing.


HTH

Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,112,937
Messages
5,543,093
Members
410,583
Latest member
gazz57
Top