David Ostler

Q: Auto-Populate Agenda

Hi,

I'm trying to automatically populate a "Daily Agenda" from a "Task Chart" as in the picture below.  When the date of the daily agenda is changed I'd like the tasks and details from my task chart to automatically fill in the agenda.  Any ideas what formula I would use to get this working?  I've been working on this for a few days now but I can't seem to find the right combination of formulas to get it working.

 

Screen Shot 2016-09-16 at 12.09.45 AM.png

 

Thanks,

David

PowerBook G4 15" Aluminum 1.25ghz, Mac OS X (10.4.6)

Posted on Sep 15, 2016 5:13 AM

Close

Q: Auto-Populate Agenda

  • All replies
  • Helpful answers

  • by Wayne Contello,Solvedanswer

    Wayne Contello Wayne Contello Sep 15, 2016 5:50 AM in response to David Ostler
    Level 6 (19,312 points)
    iWork
    Sep 15, 2016 5:50 AM in response to David Ostler

    This may do the trick:

    Screen Shot 2016-09-15 at 7.45.04 AM.png

     

     

    add a column in the task list as shown above -- this column should be column A now.

    A2=IF(AND(B2=Daily Agenda::$B$1, COUNTA(B2:D2)>2), MAX($A$1:A1)+1, "")

     

    this is shorthand for... select cell A2, then type (or copy and paste from here) the formula:

    =IF(AND(B2=Daily Agenda::$B$1, COUNTA(B2:D2)>2), MAX($A$1:A1)+1, "")

     

    select cell A2, copy

    select cells A2 thru the end of column A, paste

     

    now in the table "Daily Agenda" I suggest NOT merging the cells of the first row.  My example assumes you will follow this advice and there are two header rows:

    - enter the date in cell B1

     

    A3=IF((ROW()−2)≤MAX(Task Chart::A), ROW()−2, "")

    B3=IF(A3="", "", VLOOKUP(ROW()−2, Task Chart::A:D, 3, 0))

    select cell B3, copy

    select cell C3, paste

    select cells A3 thru C3, copy

    select cells A3 thru the end of column C, paste

     

    now change the text you have in the cell B1 of table "Daily Agenda" as needed.

  • by David Ostler,

    David Ostler David Ostler Sep 18, 2016 12:20 PM in response to Wayne Contello
    Level 1 (4 points)
    Wireless
    Sep 18, 2016 12:20 PM in response to Wayne Contello

    Perfect!  Exactly what I was looking for.