Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If..then..Lookup?
No idea if this is the right place to post this, but giving it a shot...Apologize for all the wasted space, but I'm a visual learner and it's the only way I know to explain my problem :)
WORKSHEET1: A1 B1 C1 10/13 10/16 This row=date of assignment Project Essay This row=assignment name Student1 80 95 Student2 75 80 WORKSHEET2: A1 B1 C1 D1 E1... 10/13 10/14 10/15 10/16 10/17 This row=dates of school year Student1 80 95 Student2 75 80 (A32) Project Essay I input grades and their dates on Worksheet 1. Worksheet 2 lists all the working days of the school year, as well as grades on the same dates they were given (listed in Worksheet 1). Row #32 on Worksheet 2 gives the name of the test/assignment..(Silly state department standards). I would like to be able to input the assignments and grades on worksheet one and Worksheet 2 would automatically be updated with the data on the same date. Can this be done? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If..then..Lookup?
One way:
Assume only one assignment per day. In Sheet2, Cell B2: =IF(ISNA(MATCH(B$1,Sheet1!$1:$1,FALSE)), "", VLOOKUP($A2,Sheet1!$A:$Z,MATCH(B$1,Sheet1!$1:$1,FA LSE),FALSE)) Copy down and across as far as necessary (if you have more than 26 grades, extend the Sheet1!$A:$Z reference to suit). In B32: =IF(ISNA(MATCH(B$1,Sheet1!$1:$1,FALSE)), "", INDEX(Sheet1!$2:$2,MATCH(B$1,Sheet1!$1:$1,FALSE))) Copy across as far as necessary. In article , Robbyn wrote: No idea if this is the right place to post this, but giving it a shot...Apologize for all the wasted space, but I'm a visual learner and it's the only way I know to explain my problem :) WORKSHEET1: A1 B1 C1 10/13 10/16 This row=date of assignment Project Essay This row=assignment name Student1 80 95 Student2 75 80 WORKSHEET2: A1 B1 C1 D1 E1... 10/13 10/14 10/15 10/16 10/17 This row=dates of school year Student1 80 95 Student2 75 80 (A32) Project Essay I input grades and their dates on Worksheet 1. Worksheet 2 lists all the working days of the school year, as well as grades on the same dates they were given (listed in Worksheet 1). Row #32 on Worksheet 2 gives the name of the test/assignment..(Silly state department standards). I would like to be able to input the assignments and grades on worksheet one and Worksheet 2 would automatically be updated with the data on the same date. Can this be done? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If..then..Lookup?
J.E.
Thank you soooo much! First formula worked like a charm. I'm getting a #ref error on the second one, but I'll play with it some to figure out why : I started this gradesheet to make my life and the lives of my faculty easier, but it seems the simple stuff I want to do aren't so simple. I've gained a whole new repect for programmers after tackling this little project Have a happy holiday. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Lookup looks to the prior column if zero appears in the lookup col | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) |