View Single Post
  #7   Report Post  
Old February 6th 06, 09:55 PM posted to microsoft.public.excel.worksheet.functions
R Weeden
 
Posts: n/a
Default Comparison IF statement

Sheet AC is for the school "Acacia". The sheet name identifies the school.
Each school's data is on a separate Sheet using the schools two-letter
abbreviation as the Worksheet name.

I have a column on the "Schools" worksheet that lists each school. On the
row for each school I have a cell that shows the submission date from each
schools worksheet. On a cell to the right of the submission date is where I
am trying to put the formula to show if the latest submitted file has been
processed.


"Tom Ogilvy" wrote in message
...
So where is the data that identifies the school on the AC sheet. A column
of submission dates doesn't identify the school . I will assume the
school
identifier is in column B on AC and in column A of the Schools sheet. In
B2 for example enter

= IF(INDEX(AC!$A$1:$C$500,
MATCH(MAX(IF(AC!$B$1:$B$500=A2,AC!$A$1:$A$500)),AC !$A$1:$A$500,0),3)="","Not
Processed","Processed")

With Ctrl+Shift+Enter rather than entere and drag down the column.

--
Regards,
Tom Ogilvy


"R Weeden" wrote in message
...
That returns several errors. I am probably not explaining myself well
enough.

I have a worksheet (AC) that has a column of submission dates (Column A)
and a column of response dates (column C).
I enter the date in column A when I submit a file. After this file is
processed I enter the response date in column C.

I have another sheet (Schools) that tracks submission dates.
On the School sheet I am trying to create a cell in a column that checks

to
see if each schools latest submission file has been processed. If it has
been processed (EX: check the column of Submission dates to fine the

latest
date (the last non-blank cell, or the last cell with a date in it). Then

I
want to check in the response date column for that row and see if the
response date is blank.

If it is blank I am trying to return "Not Processed" in that school's row

on
the Schools sheet.

If it is not blank (there is a date in the cell) I am trying to return
"Processed".


Thanks for your help and patience... :-)

"Tom Ogilvy" wrote in message
...
Say in A3 on sheet1 you put a formula like


=INDEX(Sheet2!$A$1:$A$100,SMALL(IF((Sheet2!$A$2:$A $100<"")*(Sheet2!$C$2:$C$
100=""),ROW($A$2:$A$100)),ROW(A1)),1)

Entered with Ctrl+Shift+enter and then drag fill it down until it
starts
to
return errors.

--
Regards,
Tom Ogilvy


"R Weeden" wrote in message
...
That works for the single cell, but how would I check the value based
upon
the last non-blank cell of column A, comparing that to the value of
the
same
row in column C to determine if it is blank.

We have one column that has file submission dates and another column

that
has file response dates. I am trying to find which submission date
(Column
A) do not have a response date (Column C).


"Tom Ogilvy" wrote in message
...
=if(And(Sheet2!A3<"",Sheet2!C3=""),Sheet2!A3,"")

--
Regards,
Tom Ogilvy


"R Weeden" wrote in message
...
I am trying to compare two cells in a row within a range on one
worksheet
and return a value on another worksheet. Example: Cell A3 on
Worksheet
1
has an IF statement the compares the cells in column A to the cells

in
column C on worksheet 2. IF cell a3 in Column A is NOT blank
check
to
see
if the cell c3 is blank. If both conditions are met then return a
value
to
A# on Worksheet1. I am having trouble getting the comparison name

to
work.

Thanks in advance.

R Weeden