My objective is to create a list of actions to be taken today. On one
worksheet I have dates in
Column A which are in order 1/1/05, 1/2/05, 1/3/05, etc. In column B there
is a different activity
for each day. Obviously it is easy to use VLOOKUP Today() to capture
todays activity and print it in a column 1 on a report.

In Column C the dates are in random order and many cells are blank.
Whenever there is a date in
Column C there is an accompanying activity in Column D. How can I capture
todays activity
and print it in column 2 of the report? What happens if todays date
appears twice in Column C?

Thank you in advance for any ideas you can offer.

Here is one way, but it requires one more column.

Firstly in B1, enter this formula

=IF(Sheet2!$C$1:$C$100<TODAY(),"",ROW())

and copy down to say B100 (assuming 100 tasks for a day is the limit ;-)).

Then select all of the cells C1:C100, and copy this formula into the formula
bar and commit it with Ctrl-Shift-Enter

=IF(ROW(Sheet2!$C$1:$C$100)-ROW(Sheet2!C1)+1COUNT(B1:B100),"",
INDEX(Sheet2!D:D,SMALL(B1:B100,ROW(INDIRECT("1:"&R OWS(Sheet2!$C$1:$C$100))))
))

You should see numbers with gaps in column B, and the tasks with no gaps in
column C.

It can all be don e in one formula, but I can't find where I did that at the
moment.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"VJ7777" wrote in message
...

My objective is to create a list of actions to be taken today. On one
worksheet I have dates in
Column A which are in order 1/1/05, 1/2/05, 1/3/05, etc. In column B
there
is a different activity
for each day. Obviously it is easy to use VLOOKUP Today() to capture
today's activity and print it in a column 1 on a report.

In Column C the dates are in random order and many cells are blank.
Whenever there is a date in
Column C there is an accompanying activity in Column D. How can I capture
today's activity
and print it in column 2 of the report? What happens if today's date
appears twice in Column C?

Thank you in advance for any ideas you can offer.

Sorry, does work, but first formula should be

=IF(Sheet2!$C1<TODAY(),"",ROW())

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
Here is one way, but it requires one more column.

Firstly in B1, enter this formula

=IF(Sheet2!$C$1:$C$100<TODAY(),"",ROW())

and copy down to say B100 (assuming 100 tasks for a day is the limit ;-)).

Then select all of the cells C1:C100, and copy this formula into the
formula
bar and commit it with Ctrl-Shift-Enter

=IF(ROW(Sheet2!$C$1:$C$100)-ROW(Sheet2!C1)+1COUNT(B1:B100),"",

INDEX(Sheet2!D:D,SMALL(B1:B100,ROW(INDIRECT("1:"&R OWS(Sheet2!$C$1:$C$100))))
))

You should see numbers with gaps in column B, and the tasks with no gaps
in
column C.

It can all be don e in one formula, but I can't find where I did that at
the
moment.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"VJ7777" wrote in message
...

My objective is to create a list of actions to be taken today. On one
worksheet I have dates in
Column A which are in order 1/1/05, 1/2/05, 1/3/05, etc. In column B
there
is a different activity
for each day. Obviously it is easy to use VLOOKUP Today() to capture
today's activity and print it in a column 1 on a report.

In Column C the dates are in random order and many cells are blank.
Whenever there is a date in
Column C there is an accompanying activity in Column D. How can I
capture
today's activity
and print it in column 2 of the report? What happens if today's date
appears twice in Column C?

Thank you in advance for any ideas you can offer.

"Bob Phillips" wrote:

Sorry, does work, but first formula should be

=IF(Sheet2!$C1<TODAY(),"",ROW())

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
Here is one way, but it requires one more column.

Firstly in B1, enter this formula

=IF(Sheet2!$C$1:$C$100<TODAY(),"",ROW())

and copy down to say B100 (assuming 100 tasks for a day is the limit ;-)).

Then select all of the cells C1:C100, and copy this formula into the
formula
bar and commit it with Ctrl-Shift-Enter

=IF(ROW(Sheet2!$C$1:$C$100)-ROW(Sheet2!C1)+1COUNT(B1:B100),"",

INDEX(Sheet2!D:D,SMALL(B1:B100,ROW(INDIRECT("1:"&R OWS(Sheet2!$C$1:$C$100))))
))

You should see numbers with gaps in column B, and the tasks with no gaps
in
column C.

It can all be don e in one formula, but I can't find where I did that at
the
moment.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"VJ7777" wrote in message
...

My objective is to create a list of actions to be taken today. On one
worksheet I have dates in
Column A which are in order 1/1/05, 1/2/05, 1/3/05, etc. In column B
there
is a different activity
for each day. Obviously it is easy to use VLOOKUP Today() to capture
today's activity and print it in a column 1 on a report.

In Column C the dates are in random order and many cells are blank.
Whenever there is a date in
Column C there is an accompanying activity in Column D. How can I
capture
today's activity
and print it in column 2 of the report? What happens if today's date
appears twice in Column C?

Thank you in advance for any ideas you can offer.




Hi, Bob:
I tried to use your formula; couldn't make it work, so sent you an email
with sample records. Perhaps, due to spam, viruses, etc. you didn't receive
the email. Here is my version of your formula where the dates are in Column
M and the activities are in column K of the Kahley file:
Formula in Column B1 through B10 of the report:
=IF('D:\Server1 1.29.05\Daily 2005\[Kahley.xls]BPR'!$M$2<TODAY(),"",ROW())
Formula in Column C1 through C10 of the report:
{=IF(ROW('D:\Server1 1.29.05\Daily
2005\[Kahley.xls]BPR'!$M$2:$M$33)-ROW('D:\Server1 1.29.05\Daily
2005\[Kahley.xls]BPR'!$M$2)+1COUNT(B1:B10),"",INDEX('D:\Server1
1.29.05\Daily
2005\[Kahley.xls]BPR'!$K$2:$K$33,SMALL(B1:B10,ROW(INDIRECT("1:"&ROW S('D:\Server1 1.29.05\Daily 2005\[Kahley.xls]BPR'!$M$2:$M$33))))))}

You can see that Excel added some parentheses to your formula. Perhaps I
screwed it up.

Thank you for your help.
Vince