Lists and Variables?

[Seenage]

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

 

[Seenage]

bump?

No replies.

Please, help!

~Seen

 

[acw]

Hi

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

What are the ranges involved?

What is date and arrayvalue.


Tony

 

[Seenage]

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]

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