Saturday, April 16, 2011

yogi_Retrieve Info From Form Input Into Multiple Rows of SheetA Into One Row Of Sheet B

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  

eamonm asked:

The ultimate objective is to post an assignment from multiple teachers on the web so students can see what the home work is. The way I thought of doing this is to have a form that teachers enter their information in, the form is very basic - their name, description of assignment.

The spreadsheet form the form looks something like (this is Sheet1):

Here is my design logic - if may not be the most direct so if there is a better solution I'm open to it.

1. Each day the teachers use the form to enter more assignments, so there would be 4 rows inserted each day Monday through Friday.

2. In the spreadsheet I create an additional sheet called Intermediate that has the following columns:

I populate the CurrentDate manually myself from 04-18-11 to the end of the year (this has only Monday through Friday dates). My thinking is that I use a formula in Intermediate that takes CurrentDate and gets information from Sheet1 to populate Intermediate.

Where I'm stuck is with getting the four rows of data from Sheet1 into one row in Intermediate. Logically I think I would do something like the following formula from Intermediate:

Each teacher name column would have a lookup formula that would use the manually entered date CurrentDate to lookup information from Sheet1. The resulting data from the above example would look like:

The Post column would have a formula that compares the system date (true current date) to the date in column CurrentDate. If system date is equal or greater than CurrentDate then Post column = "Y".