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.
Thanks,
David
PowerBook G4 15" Aluminum 1.25ghz, Mac OS X (10.4.6)
Posted on Sep 15, 2016 5:13 AM
This may do the trick:
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.
Posted on Sep 15, 2016 5:50 AM

