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