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
|