IF/AND ARRAY in MS Excel 2003
in [General]
|
From: Amylou on 18 Feb 2010 14:24 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: word to excel data transfer help Next: if/and array in MS Excel 2003 |