1

I have an example here of my spreadsheet.

Basically my main page has a drop-down (via data validation) where you can toggle through any month and it responds by showing data for that month.

What I want to do is be able to type a note for a specific day and have it show up in Sheet1!B2:B where the date in Sheet2!A2:A matches Sheet1!A2:A. So Sheet2 will have any number of notes across any month of the year. If there is a duplicate date in Sheet2!A:A then it could just grab the first or throw error (this will be an edge case). I would even be fine populating Sheet2!A2:A with all 365 days if it would make things easier.

What would be best is if I could just type my change in Sheet1!B2:B and it would grab the date from Sheet1!A2:A and make the entry in Sheet2, and then sync changes both ways. I know nothing about Google Docs scripts though.

  • Can you have multiple notes on one day? – Jacob Jan Tuinstra Feb 25 '14 at 14:39
  • I don't need to support that. If it occurs I would just want the first or last. See 2nd paragraph. – gooddadmike Feb 25 '14 at 15:10
  • You can always squeeze all notes into one cell. – Jacob Jan Tuinstra Feb 25 '14 at 15:12
  • That would actually be best. What change would I make for that? – gooddadmike Feb 25 '14 at 16:55
  • If you start each new comment with a hyphen, then you can use this post, here on Web Applications, to enter a new line into a cell: webapps.stackexchange.com/a/25361/29140 – Jacob Jan Tuinstra Feb 26 '14 at 20:21
2

Use the following formula, to keep track of the notes:

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A32,Sheet2!A:B,2,0), ""))

If you have more than one note per day, then you can expand the formula to cover more columns:

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A32,Sheet2!A:B,{2,3,4,5,6},0), ""))
  • To clarify I don't have extra notes in column to the right of the date. I have extra rows. It would look lite this in CSV Date,Note \n 3/2/14,"Note 1" \n 3/2/14,"Note 2" \n – gooddadmike Mar 1 '14 at 7:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.