church visitation
 

Hi.

We are small church about 250 members.
We do visit our church members and we record them in an Excel spreadsheet.

Column A - 1st name, column B - last name
and from column d up to column Y, we input the date of when we visited each
members for example in column C:3 across their name.

Our pastor is requesting me to revise the worksheet to allow him to see at a
glance those members that have not yet been visited, so visitaion may be
scheduled.

I have do conditional formatting by adding counting the no of blanks from
Column d to column BZ (which is 22) and highligting the member's name in
column A, formula =(Z2:Z200=22). That means those with highlight have not
been visited.

This seems so crude. Is there a better way? Appreciate your help and advice.

church visitation
 

Perhaps you could insert a new column to hold formulas employing Excel's MAX
function to evaluate the range holding the visitation dates, then sort by
that column.

So, say you made a new column A, to the left of the names. Cell A2 formula
would be =MAX(D2:X2) and then copy that down the list. If you wanted a
blank when no visits have been made, you could add an IF to that, e.g.
=IF(MAX(D2:X2)=0,"",MAX(D2:X2))

Hope that helps.

"tabylee" wrote:

Hi.

We are small church about 250 members.
We do visit our church members and we record them in an Excel spreadsheet.

Column A - 1st name, column B - last name
and from column d up to column Y, we input the date of when we visited each
members for example in column C:3 across their name.

Our pastor is requesting me to revise the worksheet to allow him to see at a
glance those members that have not yet been visited, so visitaion may be
scheduled.

I have do conditional formatting by adding counting the no of blanks from
Column d to column BZ (which is 22) and highligting the member's name in
column A, formula =(Z2:Z200=22). That means those with highlight have not
been visited.

This seems so crude. Is there a better way? Appreciate your help and advice.

.

church visitation
 

Thanks very much. I have input the formula. and it works fine.
B Lynn B wrote:
Perhaps you could insert a new column to hold formulas employing Excel's MAX
function to evaluate the range holding the visitation dates, then sort by
that column.

So, say you made a new column A, to the left of the names. Cell A2 formula
would be =MAX(D2:X2) and then copy that down the list. If you wanted a
blank when no visits have been made, you could add an IF to that, e.g.
=IF(MAX(D2:X2)=0,"",MAX(D2:X2))

Hope that helps.

Hi.

[quoted text clipped - 17 lines]

.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200912/1