How can I have excel:
1) search a specific row, e.g R4:R500 and for every occurance of a non-blank
cell
2) bring back what corresponds to that row in col D?

Thanks

Instead of a formula based approach why not simply copy and paste the data
on column R on to another sheet
Sort the data
and paste it back on column D in your original sheet

"Rod" wrote in message
...
How can I have excel:
1) search a specific row, e.g R4:R500 and for every occurance of a
non-blank
cell
2) bring back what corresponds to that row in col D?

Thanks

Hi,

The source data will change daily. I would like to bring this summary sheet
up and have the sheet do the calculations and present the results in a clean
manner, e.g. no blanks, etc..

"N Harkawat" wrote:

Instead of a formula based approach why not simply copy and paste the data
on column R on to another sheet
Sort the data
and paste it back on column D in your original sheet

"Rod" wrote in message
...
How can I have excel:
1) search a specific row, e.g R4:R500 and for every occurance of a
non-blank
cell
2) bring back what corresponds to that row in col D?

Thanks

Ok then on column D type this formula
=IF(ISERROR(INDEX($r$4:$r$400,SMALL(IF($r$4:$r$400 <"",ROW($A$1:$A$397)),ROW(1:1)))),"",INDEX($r$4:$ r$400,SMALL(IF($r$4:$r$400<"",ROW($A$1:$A$397)),R OW(1:1))))

And array enter it (ctrl+shift+enter instead of just Enter)

And copy it down all the way for 397 rows.

This will give you a list of all non blanks


"Rod" wrote in message
...
Hi,

The source data will change daily. I would like to bring this summary
sheet
up and have the sheet do the calculations and present the results in a
clean
manner, e.g. no blanks, etc..

"N Harkawat" wrote:

Instead of a formula based approach why not simply copy and paste the
data
on column R on to another sheet
Sort the data
and paste it back on column D in your original sheet

"Rod" wrote in message
...
How can I have excel:
1) search a specific row, e.g R4:R500 and for every occurance of a
non-blank
cell
2) bring back what corresponds to that row in col D?

Thanks

No Luck. Here are my results:
1) I updated your suggestion to reflect the two different files: '[Tracking
Sheet]Other Srcs' and summary sheet, which is suppose to give me the clean
presentation of the information, call it COI
2) The formula looks like this:
{=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))}
3) and returned a value of 38430 in COI's A2 (A1 is a col heading of "NAME".
4) The desired result is for a return of "Tracy" given row D17 is "Tracy"
and R10 is 3/19/2005, formatted as Sat, Mar19, 05. This is the first
occurance of a data which should be returned as a desired match.

Thoughts?

"N Harkawat" wrote:

Ok then on column D type this formula
=IF(ISERROR(INDEX($r$4:$r$400,SMALL(IF($r$4:$r$400 <"",ROW($A$1:$A$397)),ROW(1:1)))),"",INDEX($r$4:$ r$400,SMALL(IF($r$4:$r$400<"",ROW($A$1:$A$397)),R OW(1:1))))

And array enter it (ctrl+shift+enter instead of just Enter)

And copy it down all the way for 397 rows.

This will give you a list of all non blanks


"Rod" wrote in message
...
Hi,

The source data will change daily. I would like to bring this summary
sheet
up and have the sheet do the calculations and present the results in a
clean
manner, e.g. no blanks, etc..

"N Harkawat" wrote:

Instead of a formula based approach why not simply copy and paste the
data
on column R on to another sheet
Sort the data
and paste it back on column D in your original sheet

"Rod" wrote in message
...
How can I have excel:
1) search a specific row, e.g R4:R500 and for every occurance of a
non-blank
cell
2) bring back what corresponds to that row in col D?

Thanks

Have you considered just using Autofilter?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Rod" wrote in message
...
How can I have excel:
1) search a specific row, e.g R4:R500 and for every occurance of a
non-blank
cell
2) bring back what corresponds to that row in col D?

Thanks

Never mind, just read the rest of the text, apologies.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Ken Wright" wrote in message
...
Have you considered just using Autofilter?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--

"Rod" wrote in message
...
How can I have excel:
1) search a specific row, e.g R4:R500 and for every occurance of a
non-blank
cell
2) bring back what corresponds to that row in col D?

Thanks

Rod

Explain me exactly what you need
My understanding is in your tracking sheet you got names on COLUMN R but
with blank rows. And in the sheet where you want "clean
presentation of the information" sheet COI on column D you need that name to
appear but without blank rows.
So where do the dates come from.....

This formula is going to do the following:-

Say column R in tracking sheet has names as follows:-

Tracy
....
....
Robert
....
Alex
....
Joe


In your clean presentation sheet his formula will show it as follows :

Tracy
Robert
Alex
Joe

IN short Dropping all blank rows

Explain if this is what you wanted to achieve




"Rod" wrote in message
...
No Luck. Here are my results:
1) I updated your suggestion to reflect the two different files:
'[Tracking
Sheet]Other Srcs' and summary sheet, which is suppose to give me the clean
presentation of the information, call it COI
2) The formula looks like this:
{=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))}
3) and returned a value of 38430 in COI's A2 (A1 is a col heading of
"NAME".
4) The desired result is for a return of "Tracy" given row D17 is "Tracy"
and R10 is 3/19/2005, formatted as Sat, Mar19, 05. This is the first
occurance of a data which should be returned as a desired match.

Thoughts?

"N Harkawat" wrote:

Ok then on column D type this formula
=IF(ISERROR(INDEX($r$4:$r$400,SMALL(IF($r$4:$r$400 <"",ROW($A$1:$A$397)),ROW(1:1)))),"",INDEX($r$4:$ r$400,SMALL(IF($r$4:$r$400<"",ROW($A$1:$A$397)),R OW(1:1))))

And array enter it (ctrl+shift+enter instead of just Enter)

And copy it down all the way for 397 rows.

This will give you a list of all non blanks


"Rod" wrote in message
...
Hi,

The source data will change daily. I would like to bring this summary
sheet
up and have the sheet do the calculations and present the results in a
clean
manner, e.g. no blanks, etc..

"N Harkawat" wrote:

Instead of a formula based approach why not simply copy and paste the
data
on column R on to another sheet
Sort the data
and paste it back on column D in your original sheet

"Rod" wrote in message
...
How can I have excel:
1) search a specific row, e.g R4:R500 and for every occurance of a
non-blank
cell
2) bring back what corresponds to that row in col D?

Thanks

Good Morning,

The Tracking sheet logs who was called and when an appointment was set (and
a lot of other things). It is of interest to know who is scheduled on a
given day for an appointment. (The appointment sheet is constantly being
updated by others.) Often calls are made but no appointment set - these are
to be skipped by the COI summary sheet, however, those whom have an
appointment scheduled are of interest to the COI sheet and should be
displayed: Name of person and Date of the Appointment.

Example of data in Tracking sheet:
ColD ColR
Name Appointment Date
Robert
Tracy Sat, Mar 19, 05
Marcos
Charles Mon, Mar21,05

In this case, Robert and marcos are of no interest, however, Tracy and
Charles are of interest. The CIO sheet should fail Robert and Marcos but
pass Tracy and Charles. The CIO sheet should not have blank rows just
because Robert and Marcos failed, but should have the returned values as such:

ColA ColC
Name Appointment
Tracy Sat, Mar 19, 05
Charles Mon, Mar21, 05

I hope this helps. Thanks much.


"N Harkawat" wrote:

Rod

Explain me exactly what you need
My understanding is in your tracking sheet you got names on COLUMN R but
with blank rows. And in the sheet where you want "clean
presentation of the information" sheet COI on column D you need that name to
appear but without blank rows.
So where do the dates come from.....

This formula is going to do the following:-

Say column R in tracking sheet has names as follows:-

Tracy
....
....
Robert
....
Alex
....
Joe


In your clean presentation sheet his formula will show it as follows :

Tracy
Robert
Alex
Joe

IN short Dropping all blank rows

Explain if this is what you wanted to achieve




"Rod" wrote in message
...
No Luck. Here are my results:
1) I updated your suggestion to reflect the two different files:
'[Tracking
Sheet]Other Srcs' and summary sheet, which is suppose to give me the clean
presentation of the information, call it COI
2) The formula looks like this:
{=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))}
3) and returned a value of 38430 in COI's A2 (A1 is a col heading of
"NAME".
4) The desired result is for a return of "Tracy" given row D17 is "Tracy"
and R10 is 3/19/2005, formatted as Sat, Mar19, 05. This is the first
occurance of a data which should be returned as a desired match.

Thoughts?

"N Harkawat" wrote:

Ok then on column D type this formula
=IF(ISERROR(INDEX($r$4:$r$400,SMALL(IF($r$4:$r$400 <"",ROW($A$1:$A$397)),ROW(1:1)))),"",INDEX($r$4:$ r$400,SMALL(IF($r$4:$r$400<"",ROW($A$1:$A$397)),R OW(1:1))))

And array enter it (ctrl+shift+enter instead of just Enter)

And copy it down all the way for 397 rows.

This will give you a list of all non blanks


"Rod" wrote in message
...
Hi,

The source data will change daily. I would like to bring this summary
sheet
up and have the sheet do the calculations and present the results in a
clean
manner, e.g. no blanks, etc..

"N Harkawat" wrote:

Instead of a formula based approach why not simply copy and paste the
data
on column R on to another sheet
Sort the data
and paste it back on column D in your original sheet

"Rod" wrote in message
...
How can I have excel:
1) search a specific row, e.g R4:R500 and for every occurance of a
non-blank
cell
2) bring back what corresponds to that row in col D?

Thanks

OK, The reason why you were getting date
Just use the formula that you use in the output sheet
For date cell


=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))



Format this cell as date



For Name cell same as above except instead of $R use $ D as follows:



=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))





PS: Array enter both these formulas (ctrl+shift+enter)







"Rod" wrote in message
...
Good Morning,

The Tracking sheet logs who was called and when an appointment was set
(and
a lot of other things). It is of interest to know who is scheduled on a
given day for an appointment. (The appointment sheet is constantly being
updated by others.) Often calls are made but no appointment set - these
are
to be skipped by the COI summary sheet, however, those whom have an
appointment scheduled are of interest to the COI sheet and should be
displayed: Name of person and Date of the Appointment.

Example of data in Tracking sheet:
ColD ColR
Name Appointment Date
Robert
Tracy Sat, Mar 19, 05
Marcos
Charles Mon, Mar21,05

In this case, Robert and marcos are of no interest, however, Tracy and
Charles are of interest. The CIO sheet should fail Robert and Marcos but
pass Tracy and Charles. The CIO sheet should not have blank rows just
because Robert and Marcos failed, but should have the returned values as
such:

ColA ColC
Name Appointment
Tracy Sat, Mar 19, 05
Charles Mon, Mar21, 05

I hope this helps. Thanks much.


"N Harkawat" wrote:

Rod

Explain me exactly what you need
My understanding is in your tracking sheet you got names on COLUMN R but
with blank rows. And in the sheet where you want "clean
presentation of the information" sheet COI on column D you need that name
to
appear but without blank rows.
So where do the dates come from.....

This formula is going to do the following:-

Say column R in tracking sheet has names as follows:-

Tracy
....
....
Robert
....
Alex
....
Joe


In your clean presentation sheet his formula will show it as follows :

Tracy
Robert
Alex
Joe

IN short Dropping all blank rows

Explain if this is what you wanted to achieve




"Rod" wrote in message
...
No Luck. Here are my results:
1) I updated your suggestion to reflect the two different files:
'[Tracking
Sheet]Other Srcs' and summary sheet, which is suppose to give me the
clean
presentation of the information, call it COI
2) The formula looks like this:
{=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))}
3) and returned a value of 38430 in COI's A2 (A1 is a col heading of
"NAME".
4) The desired result is for a return of "Tracy" given row D17 is
"Tracy"
and R10 is 3/19/2005, formatted as Sat, Mar19, 05. This is the first
occurance of a data which should be returned as a desired match.

Thoughts?

"N Harkawat" wrote:

Ok then on column D type this formula
=IF(ISERROR(INDEX($r$4:$r$400,SMALL(IF($r$4:$r$400 <"",ROW($A$1:$A$397)),ROW(1:1)))),"",INDEX($r$4:$ r$400,SMALL(IF($r$4:$r$400<"",ROW($A$1:$A$397)),R OW(1:1))))

And array enter it (ctrl+shift+enter instead of just Enter)

And copy it down all the way for 397 rows.

This will give you a list of all non blanks


"Rod" wrote in message
...
Hi,

The source data will change daily. I would like to bring this
summary
sheet
up and have the sheet do the calculations and present the results in
a
clean
manner, e.g. no blanks, etc..

"N Harkawat" wrote:

Instead of a formula based approach why not simply copy and paste
the
data
on column R on to another sheet
Sort the data
and paste it back on column D in your original sheet

"Rod" wrote in message
...
How can I have excel:
1) search a specific row, e.g R4:R500 and for every occurance of
a
non-blank
cell
2) bring back what corresponds to that row in col D?

Thanks

No luck. The Name col formula:
=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400)),ROW(1:1))))
which, by the way the ROW(1:1) changes to ROW(2:2), etc., as I copy it down
(not sure if that is what you wanted), brings back names which do not have
appointments.

The Appointment col function:
=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400)),ROW(1:1))))
which has the same issue with the ROW function as above, brings back dates
farther down the list, which do not correspond with the name in colD.

Both functions are missing appointment names and dates which should
have been selected for display in the COI sheet, but instead names and wrong
dates, such as Sat, Jan 00, which are not even on the sheet, are returned -
possibly calls but no appointments?

All in all, they seem to be triggering correctly, but bringing back the
wrong information.

Thoughts?



"N Harkawat" wrote:

OK, The reason why you were getting date
Just use the formula that you use in the output sheet
For date cell


=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))



Format this cell as date



For Name cell same as above except instead of $R use $ D as follows:



=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))





PS: Array enter both these formulas (ctrl+shift+enter)







"Rod" wrote in message
...
Good Morning,

The Tracking sheet logs who was called and when an appointment was set
(and
a lot of other things). It is of interest to know who is scheduled on a
given day for an appointment. (The appointment sheet is constantly being
updated by others.) Often calls are made but no appointment set - these
are
to be skipped by the COI summary sheet, however, those whom have an
appointment scheduled are of interest to the COI sheet and should be
displayed: Name of person and Date of the Appointment.

Example of data in Tracking sheet:
ColD ColR
Name Appointment Date
Robert
Tracy Sat, Mar 19, 05
Marcos
Charles Mon, Mar21,05

In this case, Robert and marcos are of no interest, however, Tracy and
Charles are of interest. The CIO sheet should fail Robert and Marcos but
pass Tracy and Charles. The CIO sheet should not have blank rows just
because Robert and Marcos failed, but should have the returned values as
such:

ColA ColC
Name Appointment
Tracy Sat, Mar 19, 05
Charles Mon, Mar21, 05

I hope this helps. Thanks much.


"N Harkawat" wrote:

Rod

Explain me exactly what you need
My understanding is in your tracking sheet you got names on COLUMN R but
with blank rows. And in the sheet where you want "clean
presentation of the information" sheet COI on column D you need that name
to
appear but without blank rows.
So where do the dates come from.....

This formula is going to do the following:-

Say column R in tracking sheet has names as follows:-

Tracy
....
....
Robert
....
Alex
....
Joe


In your clean presentation sheet his formula will show it as follows :

Tracy
Robert
Alex
Joe

IN short Dropping all blank rows

Explain if this is what you wanted to achieve




"Rod" wrote in message
...
No Luck. Here are my results:
1) I updated your suggestion to reflect the two different files:
'[Tracking
Sheet]Other Srcs' and summary sheet, which is suppose to give me the
clean
presentation of the information, call it COI
2) The formula looks like this:
{=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))}
3) and returned a value of 38430 in COI's A2 (A1 is a col heading of
"NAME".
4) The desired result is for a return of "Tracy" given row D17 is
"Tracy"
and R10 is 3/19/2005, formatted as Sat, Mar19, 05. This is the first
occurance of a data which should be returned as a desired match.

Thoughts?

"N Harkawat" wrote:

Ok then on column D type this formula
=IF(ISERROR(INDEX($r$4:$r$400,SMALL(IF($r$4:$r$400 <"",ROW($A$1:$A$397)),ROW(1:1)))),"",INDEX($r$4:$ r$400,SMALL(IF($r$4:$r$400<"",ROW($A$1:$A$397)),R OW(1:1))))

And array enter it (ctrl+shift+enter instead of just Enter)

And copy it down all the way for 397 rows.

This will give you a list of all non blanks


"Rod" wrote in message
...
Hi,

The source data will change daily. I would like to bring this
summary
sheet
up and have the sheet do the calculations and present the results in
a
clean
manner, e.g. no blanks, etc..

"N Harkawat" wrote:

Instead of a formula based approach why not simply copy and paste
the
data
on column R on to another sheet
Sort the data
and paste it back on column D in your original sheet

"Rod" wrote in message
...
How can I have excel:
1) search a specific row, e.g R4:R500 and for every occurance of
a
non-blank
cell
2) bring back what corresponds to that row in col D?

Thanks

My apologies Just change the formulas as follows and yes the row (1:1)
should change as you copy the formula down: -
Date part
=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))

Name Part
=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))




"Rod" wrote in message
...
No luck. The Name col formula:
=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400)),ROW(1:1))))
which, by the way the ROW(1:1) changes to ROW(2:2), etc., as I copy it
down
(not sure if that is what you wanted), brings back names which do not have
appointments.

The Appointment col function:
=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400)),ROW(1:1))))
which has the same issue with the ROW function as above, brings back dates
farther down the list, which do not correspond with the name in colD.

Both functions are missing appointment names and dates which should
have been selected for display in the COI sheet, but instead names and
wrong
dates, such as Sat, Jan 00, which are not even on the sheet, are
returned -
possibly calls but no appointments?

All in all, they seem to be triggering correctly, but bringing back the
wrong information.

Thoughts?



"N Harkawat" wrote:

OK, The reason why you were getting date
Just use the formula that you use in the output sheet
For date cell


=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))



Format this cell as date



For Name cell same as above except instead of $R use $ D as follows:



=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))





PS: Array enter both these formulas (ctrl+shift+enter)







"Rod" wrote in message
...
Good Morning,

The Tracking sheet logs who was called and when an appointment was set
(and
a lot of other things). It is of interest to know who is scheduled on
a
given day for an appointment. (The appointment sheet is constantly
being
updated by others.) Often calls are made but no appointment set -
these
are
to be skipped by the COI summary sheet, however, those whom have an
appointment scheduled are of interest to the COI sheet and should be
displayed: Name of person and Date of the Appointment.

Example of data in Tracking sheet:
ColD ColR
Name Appointment Date
Robert
Tracy Sat, Mar 19, 05
Marcos
Charles Mon, Mar21,05

In this case, Robert and marcos are of no interest, however, Tracy and
Charles are of interest. The CIO sheet should fail Robert and Marcos
but
pass Tracy and Charles. The CIO sheet should not have blank rows just
because Robert and Marcos failed, but should have the returned values
as
such:

ColA ColC
Name Appointment
Tracy Sat, Mar 19, 05
Charles Mon, Mar21, 05

I hope this helps. Thanks much.


"N Harkawat" wrote:

Rod

Explain me exactly what you need
My understanding is in your tracking sheet you got names on COLUMN R
but
with blank rows. And in the sheet where you want "clean
presentation of the information" sheet COI on column D you need that
name
to
appear but without blank rows.
So where do the dates come from.....

This formula is going to do the following:-

Say column R in tracking sheet has names as follows:-

Tracy
....
....
Robert
....
Alex
....
Joe


In your clean presentation sheet his formula will show it as follows :

Tracy
Robert
Alex
Joe

IN short Dropping all blank rows

Explain if this is what you wanted to achieve




"Rod" wrote in message
...
No Luck. Here are my results:
1) I updated your suggestion to reflect the two different files:
'[Tracking
Sheet]Other Srcs' and summary sheet, which is suppose to give me the
clean
presentation of the information, call it COI
2) The formula looks like this:
{=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))}
3) and returned a value of 38430 in COI's A2 (A1 is a col heading of
"NAME".
4) The desired result is for a return of "Tracy" given row D17 is
"Tracy"
and R10 is 3/19/2005, formatted as Sat, Mar19, 05. This is the
first
occurance of a data which should be returned as a desired match.

Thoughts?

"N Harkawat" wrote:

Ok then on column D type this formula
=IF(ISERROR(INDEX($r$4:$r$400,SMALL(IF($r$4:$r$400 <"",ROW($A$1:$A$397)),ROW(1:1)))),"",INDEX($r$4:$ r$400,SMALL(IF($r$4:$r$400<"",ROW($A$1:$A$397)),R OW(1:1))))

And array enter it (ctrl+shift+enter instead of just Enter)

And copy it down all the way for 397 rows.

This will give you a list of all non blanks


"Rod" wrote in message
...
Hi,

The source data will change daily. I would like to bring this
summary
sheet
up and have the sheet do the calculations and present the results
in
a
clean
manner, e.g. no blanks, etc..

"N Harkawat" wrote:

Instead of a formula based approach why not simply copy and
paste
the
data
on column R on to another sheet
Sort the data
and paste it back on column D in your original sheet

"Rod" wrote in message
...
How can I have excel:
1) search a specific row, e.g R4:R500 and for every occurance
of
a
non-blank
cell
2) bring back what corresponds to that row in col D?

Thanks

BINGO! You are awesome!

THANKS MUCH!

"N Harkawat" wrote:

My apologies Just change the formulas as follows and yes the row (1:1)
should change as you copy the formula down: -
Date part
=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))

Name Part
=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))




"Rod" wrote in message
...
No luck. The Name col formula:
=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400)),ROW(1:1))))
which, by the way the ROW(1:1) changes to ROW(2:2), etc., as I copy it
down
(not sure if that is what you wanted), brings back names which do not have
appointments.

The Appointment col function:
=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$4:$D$400)),ROW(1:1))))
which has the same issue with the ROW function as above, brings back dates
farther down the list, which do not correspond with the name in colD.

Both functions are missing appointment names and dates which should
have been selected for display in the COI sheet, but instead names and
wrong
dates, such as Sat, Jan 00, which are not even on the sheet, are
returned -
possibly calls but no appointments?

All in all, they seem to be triggering correctly, but bringing back the
wrong information.

Thoughts?



"N Harkawat" wrote:

OK, The reason why you were getting date
Just use the formula that you use in the output sheet
For date cell


=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))



Format this cell as date



For Name cell same as above except instead of $R use $ D as follows:



=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$d$4:$d$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))





PS: Array enter both these formulas (ctrl+shift+enter)







"Rod" wrote in message
...
Good Morning,

The Tracking sheet logs who was called and when an appointment was set
(and
a lot of other things). It is of interest to know who is scheduled on
a
given day for an appointment. (The appointment sheet is constantly
being
updated by others.) Often calls are made but no appointment set -
these
are
to be skipped by the COI summary sheet, however, those whom have an
appointment scheduled are of interest to the COI sheet and should be
displayed: Name of person and Date of the Appointment.

Example of data in Tracking sheet:
ColD ColR
Name Appointment Date
Robert
Tracy Sat, Mar 19, 05
Marcos
Charles Mon, Mar21,05

In this case, Robert and marcos are of no interest, however, Tracy and
Charles are of interest. The CIO sheet should fail Robert and Marcos
but
pass Tracy and Charles. The CIO sheet should not have blank rows just
because Robert and Marcos failed, but should have the returned values
as
such:

ColA ColC
Name Appointment
Tracy Sat, Mar 19, 05
Charles Mon, Mar21, 05

I hope this helps. Thanks much.


"N Harkawat" wrote:

Rod

Explain me exactly what you need
My understanding is in your tracking sheet you got names on COLUMN R
but
with blank rows. And in the sheet where you want "clean
presentation of the information" sheet COI on column D you need that
name
to
appear but without blank rows.
So where do the dates come from.....

This formula is going to do the following:-

Say column R in tracking sheet has names as follows:-

Tracy
....
....
Robert
....
Alex
....
Joe


In your clean presentation sheet his formula will show it as follows :

Tracy
Robert
Alex
Joe

IN short Dropping all blank rows

Explain if this is what you wanted to achieve




"Rod" wrote in message
...
No Luck. Here are my results:
1) I updated your suggestion to reflect the two different files:
'[Tracking
Sheet]Other Srcs' and summary sheet, which is suppose to give me the
clean
presentation of the information, call it COI
2) The formula looks like this:
{=IF(ISERROR(INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1)))),"",INDEX('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400,SMALL(IF('[Tracking Sheet.xls]Other
Srcs'!$R$4:$R$400<"",ROW('[Tracking Sheet.xls]Other
Srcs'!$D$1:$D$397)),ROW(1:1))))}
3) and returned a value of 38430 in COI's A2 (A1 is a col heading of
"NAME".
4) The desired result is for a return of "Tracy" given row D17 is
"Tracy"
and R10 is 3/19/2005, formatted as Sat, Mar19, 05. This is the
first
occurance of a data which should be returned as a desired match.

Thoughts?

"N Harkawat" wrote:

Ok then on column D type this formula
=IF(ISERROR(INDEX($r$4:$r$400,SMALL(IF($r$4:$r$400 <"",ROW($A$1:$A$397)),ROW(1:1)))),"",INDEX($r$4:$ r$400,SMALL(IF($r$4:$r$400<"",ROW($A$1:$A$397)),R OW(1:1))))

And array enter it (ctrl+shift+enter instead of just Enter)

And copy it down all the way for 397 rows.

This will give you a list of all non blanks


"Rod" wrote in message
...
Hi,

The source data will change daily. I would like to bring this
summary
sheet
up and have the sheet do the calculations and present the results
in
a
clean
manner, e.g. no blanks, etc..

"N Harkawat" wrote:

Instead of a formula based approach why not simply copy and
paste
the
data
on column R on to another sheet
Sort the data
and paste it back on column D in your original sheet

"Rod" wrote in message
...
How can I have excel:
1) search a specific row, e.g R4:R500 and for every occurance
of
a
non-blank
cell
2) bring back what corresponds to that row in col D?

Thanks