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

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

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