Adapting an array formula

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!

--
6-West

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)

"sixwest" <[email protected]> wrote in message
news:[email protected]...
> 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,Sheet
1!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,Sc
hedule!C:B,0)))
>
> THANKS!
>
> --
> 6-West

Sorry, not an array formula, standard.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"sixwest" <[email protected]> wrote in message
news:[email protected]...
> 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,Sheet
1!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,Sc
hedule!C:B,0)))
>
> THANKS!
>
> --
> 6-West

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
--
6-West


"Bob Phillips" wrote:

> Sorry, not an array formula, standard.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "sixwest" <[email protected]> wrote in message
> news:[email protected]...
> > 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,Sheet
> 1!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,Sc
> hedule!C:B,0)))
> >
> > THANKS!
> >
> > --
> > 6-West
>
>
>

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)

"sixwest" <[email protected]> wrote in message
news:[email protected]...
> 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
> --
> 6-West
>
>
> "Bob Phillips" wrote:
>
> > Sorry, not an array formula, standard.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "sixwest" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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,Sheet
> > 1!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,Sc
> > hedule!C:B,0)))
> > >
> > > THANKS!
> > >
> > > --
> > > 6-West
> >
> >
> >

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.
--
6-West


"Bob Phillips" wrote:

> 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)
>
> "sixwest" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > --
> > 6-West
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Sorry, not an array formula, standard.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "sixwest" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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,Sheet
> > > 1!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,Sc
> > > hedule!C:B,0)))
> > > >
> > > > THANKS!
> > > >
> > > > --
> > > > 6-West
> > >
> > >
> > >
>
>
>

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/micro...06871cf92f8465
to determine exactly what is contained in a pair of cells that you think
should match but Excel doesn't.

Jerry

"sixwest" wrote:

> 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.
> --
> 6-West
>
>
> "Bob Phillips" wrote:
>
> > 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)
> >
> > "sixwest" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > > --
> > > 6-West
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Sorry, not an array formula, standard.
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "sixwest" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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,Sheet
> > > > 1!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,Sc
> > > > hedule!C:B,0)))
> > > > >
> > > > > THANKS!
> > > > >
> > > > > --
> > > > > 6-West
> > > >
> > > >
> > > >
> >
> >
> >