Prev: IF/AND ARRAY in MS Excel 2003
Next: Inserting a picture XL2003
From: Amylou on 18 Feb 2010 14:28
What I am trying to do:

I have a list of 35 clients (column B), a row of 7 available catalogs (row
1: catalogs 1-7 in columns C-I). We have approximately 50 mailings per
calendar year so I have columns O-U (O(orange)=catalog 1, P(purple)=catalog
2, Q(green)=catalog 3, etc) set up with color coded columns to the catalog
columns, here is were I enter the date a specific catalog is to be mailed
out.

I am looking for a quick way for anyone of my co-workers to get into my
catalog address file and send off the necessary catalogs only to the clients
who are supposed to receive that particular catalog by simply adding the date
into the correct column.

After each client name, under the corresponding catalog column, I have
either an “X”, 1 or 2 (meaning they do not receive, receive 1 or receive 2
catalogs).

What I am looking for is an array formula that will allow an “X” to be
entered into column A next to the name and address of the client that is
supposed to receive that catalog. And from there I can use a Conditional
Formula to color the clients name and address making everything pretty darn
simple.

The problem I am having is that the Array formula I am using is not working
for some reason and even after reading most of the previous posts dealing
with Array Formulas I can not figure out why. The formula I am using is:
=IF(AND(C2=1,$O2:$O51=TODAY()),”X”,””) After entering the formula I am
remembering to hit “Ctrl”, ”Shift” and “ENTER” all at the same time and I am
getting an “X” result in every row whether there is a current date in the
catalog column or not. My spread sheet seems to be ignoring my Array
completely.

Thank you.

 | 
Pages: 1
Prev: IF/AND ARRAY in MS Excel 2003
Next: Inserting a picture XL2003