|
Adapting an array formula
Im having trouble manipulating this formula and hope theres an answer out
the What I'm trying to do is to have a link between sheets but with different data being propagated. Such as: Sheet "A" (which Ive 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€ť)? Ive been lucky to get an initial response (thanks €śToppers€ť) that works on a test sheet but Im 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))) Ive been changing it to: =IF(ISNA(MATCH(A8,Schedule!C:B,0)),"",INDEX(Schedu le$A$8:$A$1000,MATCH(A8,Schedule!C:B,0))) THANKS! -- 6-West |
Adapting an array formula
In B4 of Printout
=IF(ISNA(MATCH(A4,Schedule!C:C,0)),"",INDEX(Schedu le!$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" wrote in message ... I'm having trouble manipulating this formula and hope there's an answer out the 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(Schedu le$A$8:$A$1000,MATCH(A8,Sc hedule!C:B,0))) THANKS! -- 6-West |
Adapting an array formula
Sorry, not an array formula, standard.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "sixwest" wrote in message ... I'm having trouble manipulating this formula and hope there's an answer out the 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(Schedu le$A$8:$A$1000,MATCH(A8,Sc hedule!C:B,0))) THANKS! -- 6-West |
Adapting an array formula
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" wrote in message ... I'm having trouble manipulating this formula and hope there's an answer out the 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(Schedu le$A$8:$A$1000,MATCH(A8,Sc hedule!C:B,0))) THANKS! -- 6-West |
Adapting an array formula
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" wrote in message ... 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" wrote in message ... I'm having trouble manipulating this formula and hope there's an answer out the 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(Schedu le$A$8:$A$1000,MATCH(A8,Sc hedule!C:B,0))) THANKS! -- 6-West |
Adapting an array formula
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" wrote in message ... 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" wrote in message ... I'm having trouble manipulating this formula and hope there's an answer out the 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(Schedu le$A$8:$A$1000,MATCH(A8,Sc hedule!C:B,0))) THANKS! -- 6-West |
Adapting an array formula
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" wrote in message ... 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" wrote in message ... I'm having trouble manipulating this formula and hope there's an answer out the 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(Schedu le$A$8:$A$1000,MATCH(A8,Sc hedule!C:B,0))) THANKS! -- 6-West |
| All times are GMT +1. The time now is 06:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com