Adapting an array formula

[Guest]

I’m having trouble manipulating this formula and hope there’s an answer out
there:

What I'm trying to do is to have a link between sheets but with
different data being propagated. Such as:

Sheet "A" (which I’ve called “Scheduleâ€) is the sheet with employee names
listed. Let's say
Employee "1" is listed in cell A8 and I input 9:30 AM in cell C8 (i.e.,
Employee "1" scheduled for 9:30).

Sheet "B" (called “Printoutâ€) is a list of the schedule times. If 9:30AM is
displayed in cell A4 on “Printoutâ€, is there a way to automatically propagate
"Employee 1" (from “Schedule†A8) into B4 when "9:30 AM" is input to C8 (in
“Scheduleâ€)?

I’ve been lucky to get an initial response (thanks “Toppersâ€) that works on
a test sheet but I’m unable to manipulate it to work with what I have:

=IF(ISNA(MATCH(A1,Sheet1!B:B,0)),"",INDEX(Sheet1!$A$1:$A$1000,MATCH(A1,Sheet1!B:B,0)))

I’ve been changing it to:

=IF(ISNA(MATCH(A8,Schedule!C:B,0)),"",INDEX(Schedule$A$8:$A$1000,MATCH(A8,Schedule!C:B,0)))

THANKS!

 

[Bob Phillips]

In B4 of Printout

=IF(ISNA(MATCH(A4,Schedule!C:C,0)),"",INDEX(Schedule!$A:$A,MATCH(A4,Schedule
!C:C,0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

Sorry, not an array formula, standard.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Thanks, that seemed to work. The only other question I have is regarding
quarter hours:

I successfully copied the formula to the rest of the time slots on Sheet 2
("Printout") except that it doesn't seem to work with quarter hours (such as
"7:15","7:45") in that it doesn't "pull" those times over.

Is this a limitation of the formula?

Thanks again

 

[Bob Phillips]

Certainly isn't.a limitation of the formula, but may be an FP arithmetic
thing in Excel. Did you populate the cells with drag-and-copy? If so, go to
the schedule sheet, and over type the 7:15 cell with 7:15 and see if the
formula works okay then.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Actually I set them up as a list on another sheet (Validate/Data, List). For
example, on the Schedule sheet there is a "7" column. Each cell in that
column has a list installed: 7:00, 7:15. 7:30, 7:45, where the user selects a
time choice. This is repeated for "8", "9", etc...

Could that be causing the problem?

Thanks again for your help.

 

[Guest]

Excel time values are decimal fractions of a day. With 24 hours in a day (a
multiple of 3), relatively few "round" times will be terminating binary
fractions. This makes it quite easy to produce discrepancies if either
1. at least one of the times is calculated (either by a formula or dragging
a fill handle)
2. one or both times have associated dates that are not the same (since a
date/time value is the time fraction plus the integer number of days since
1900).

Probably the easiest way to begin sorting out which of these is happening to
you is if you use the D2D function at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
to determine exactly what is contained in a pair of cells that you think
should match but Excel doesn't.

Jerry