Find a non-blank cell and bring back text a in same row

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" <Rod@discussions.microsoft.com> wrote in message
news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
> 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" <Rod@discussions.microsoft.com> wrote in message
> news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
> > 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)),ROW(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" <Rod@discussions.microsoft.com> wrote in message
news:43BF522A-B7B6-496F-97FF-D648BF93C168@microsoft.com...
> 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" <Rod@discussions.microsoft.com> wrote in message
>> news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
>> > 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)),ROW(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" <Rod@discussions.microsoft.com> wrote in message
> news:43BF522A-B7B6-496F-97FF-D648BF93C168@microsoft.com...
> > 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" <Rod@discussions.microsoft.com> wrote in message
> >> news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
> >> > 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" <Rod@discussions.microsoft.com> wrote in message
news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
> 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" <ken.wright@NOSPAMntlworld.com> wrote in message
news:%23s5nrfAMFHA.3844@TK2MSFTNGP14.phx.gbl...
> 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" <Rod@discussions.microsoft.com> wrote in message
> news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
> > 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" <Rod@discussions.microsoft.com> wrote in message
news:300B8704-54C7-4BE5-AD78-6C1CBFDEADC0@microsoft.com...
> 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)),ROW(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" <Rod@discussions.microsoft.com> wrote in message
>> news:43BF522A-B7B6-496F-97FF-D648BF93C168@microsoft.com...
>> > 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" <Rod@discussions.microsoft.com> wrote in message
>> >> news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
>> >> > 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" <Rod@discussions.microsoft.com> wrote in message
> news:300B8704-54C7-4BE5-AD78-6C1CBFDEADC0@microsoft.com...
> > 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)),ROW(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" <Rod@discussions.microsoft.com> wrote in message
> >> news:43BF522A-B7B6-496F-97FF-D648BF93C168@microsoft.com...
> >> > 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" <Rod@discussions.microsoft.com> wrote in message
> >> >> news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
> >> >> > 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" <Rod@discussions.microsoft.com> wrote in message
news:1AC2E8A4-7585-4564-950E-CE2629340AF9@microsoft.com...
> 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" <Rod@discussions.microsoft.com> wrote in message
>> news:300B8704-54C7-4BE5-AD78-6C1CBFDEADC0@microsoft.com...
>> > 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)),ROW(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" <Rod@discussions.microsoft.com> wrote in message
>> >> news:43BF522A-B7B6-496F-97FF-D648BF93C168@microsoft.com...
>> >> > 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" <Rod@discussions.microsoft.com> wrote in message
>> >> >> news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
>> >> >> > 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" <Rod@discussions.microsoft.com> wrote in message
> news:1AC2E8A4-7585-4564-950E-CE2629340AF9@microsoft.com...
> > 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" <Rod@discussions.microsoft.com> wrote in message
> >> news:300B8704-54C7-4BE5-AD78-6C1CBFDEADC0@microsoft.com...
> >> > 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)),ROW(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" <Rod@discussions.microsoft.com> wrote in message
> >> >> news:43BF522A-B7B6-496F-97FF-D648BF93C168@microsoft.com...
> >> >> > 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" <Rod@discussions.microsoft.com> wrote in message
> >> >> >> news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
> >> >> >> > 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" <Rod@discussions.microsoft.com> wrote in message
news:7B996968-C220-4162-9230-693A9C6FC69E@microsoft.com...
> 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" <Rod@discussions.microsoft.com> wrote in message
>> news:1AC2E8A4-7585-4564-950E-CE2629340AF9@microsoft.com...
>> > 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" <Rod@discussions.microsoft.com> wrote in message
>> >> news:300B8704-54C7-4BE5-AD78-6C1CBFDEADC0@microsoft.com...
>> >> > 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)),ROW(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" <Rod@discussions.microsoft.com> wrote in message
>> >> >> news:43BF522A-B7B6-496F-97FF-D648BF93C168@microsoft.com...
>> >> >> > 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" <Rod@discussions.microsoft.com> wrote in message
>> >> >> >> news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
>> >> >> >> > 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" <Rod@discussions.microsoft.com> wrote in message
> news:7B996968-C220-4162-9230-693A9C6FC69E@microsoft.com...
> > 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" <Rod@discussions.microsoft.com> wrote in message
> >> news:1AC2E8A4-7585-4564-950E-CE2629340AF9@microsoft.com...
> >> > 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" <Rod@discussions.microsoft.com> wrote in message
> >> >> news:300B8704-54C7-4BE5-AD78-6C1CBFDEADC0@microsoft.com...
> >> >> > 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)),ROW(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" <Rod@discussions.microsoft.com> wrote in message
> >> >> >> news:43BF522A-B7B6-496F-97FF-D648BF93C168@microsoft.com...
> >> >> >> > 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" <Rod@discussions.microsoft.com> wrote in message
> >> >> >> >> news:9CF34D0E-3F8A-4EA5-A6CF-B440AC24062E@microsoft.com...
> >> >> >> >> > 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
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>