I'd like to create a 'To Do' list by pulling data from other sheets

[Keegan1116]

Hi there,

I've create a project management workbook to keep track of what I need to do and when, by using an interactive checklist. I will get alerts to the right of each checklist item when certain parameters are met (ie: when certain dates are reached, when other items are completed, etc..).

I've attached a copy of the workbook (minus important information) as an example if that helps!

Basically what I want to create, is a 'To Do' List, that goes into the "Projects" sheet, looks for any actions that need to be completed, and then shows them in the "To Do" sheet, and also shows which project that action was pulled from.

I'm not sure how to go about doing this, whether by VLOOKUP or something else, but it would be good to get a start on this as I've been wanting to do it for quite some time.

Any help will be greatly appreciated!

[BlindAlley]

Re-post your template showing one or two examples in the TO DO tab of exactly what you are expecting the results to be.

[Keegan1116]

Done! I have uploaded the new attachment showing examples.

[BlindAlley]

Got it.

Basically you are looking for a 'Master' to do list that looks into the Project list and updates actions taken for each project?


How many projects are you normally working on, 1 - 3, 1 - 10, or more?

BA

[Keegan1116]

Yes exactly! Right now, I have about 22 on the go, some bigger than others. Also, I've split them into different worksheets based on location. I really only need the To Do list for one of these worksheets (as shown in the attached file), so I would say about 10-15

[BlindAlley]

Ok, I'll work on it during the day, on and off coz I'm going to be in & out depending on what Mrs Alley's requirements are.

[Keegan1116]

Oh yes, you have to keep the boss happy! Otherwise there are cut-backs or strikes

[BlindAlley]

So here we go with plan A.

The way that your Project List is setup caused a few work around's to create the To Do List. I created a few helper cells in the project tab and setup a few test projects to collect the data for the 1st project and then transfer it to the List in ROW order.The formulas then pull in Project 2 and so on.
First of all I setup a trigger to identify where action is required for each project and ignore the rows where no action is needed. I then created a separate area to identify all of the projects that have to do items in them.
Without knowing how your actual worksheet is setup I created a few test scenarios, not all of the formula ranges have been entered {or known} so a bit of work on your end is required.

A few of the formulas used are:
Create sequential numbers ignoring blanks
a} =IF(AB4="","",1)
b} =IF(AB5="","",MAX($AC$4:AC4)+1)

Identify projects requiring action.
a} =IFERROR(INDEX($L$4:$L$360,MATCH(AD4,$Y$4:$Y$360,0)),"")
b} {=IFERROR(INDEX($L$4:$L$360,SMALL(IF($Y$4:$Y$360=$AD4,ROW($Y$4:$Y$360)-ROW(INDEX($Y$4:$Y$360,1,1))+1),AF$3)),"")}
B is an array formula and requires 'Shift, Ctrl, Enter' after making any changes.

Let me know how this works in the real world and feel free to ask questions.

Steve

[Keegan1116]

Holy crap dude, that must have taken a while. I really appreciate you taking the time to work on this - I'm looking over everything you've created to try and understand how the formulas are working. Question: Is having array's and indexing going to slow the spreadsheet down if I end up having like 30 or 40 or even 50 projects on the go at once?

[BlindAlley]

Holy crap dude, that must have taken a while. I really appreciate you taking the time to work on this - I'm looking over everything you've created to try and understand how the formulas are working. Question: Is having array's and indexing going to slow the spreadsheet down if I end up having like 30 or 40 or even 50 projects on the go at once?

Array formulas shouldn't slow you down unless you are working on a 1990's 286 PentiumII with 4 MEG of Ram

The array's are only a small part of the worksheet, the rest are regular formulas.
I would also suggest that when creating IF formulas use cell references instead of typing "=IF(A1=1, "Send invoice to Shipping","No action Required", for example, changes can then be done in a single cell rather than re-typing text for every IF statement.

[Keegan1116]

I'm not sure I understand what you mean about the IF statements. What would the alternative be? For the formulas I have under the 'Action Required' cells, each formula is different depending on certain variables, so I didn't ever find a way to simplify any of them. Is this what you mean?

[BlindAlley]

Yep, It's just a pet peeve of mine when I see so called Excel advice websites using actual dates like IF(A1>"Tues 15th September", or actual text =COUNTIF(A1:A30,"Tomatoes","") it annoys me no end.

Don't get me wrong your formulas will work fine but what if Debra leaves the company, you would have to go and change every cell with that name. {Using 'Find/Replace' is the way to do it btw}, but if you create a list to begin with and reference that cell{s} it becomes far easier to change the formulas.

=IF(AND(H16="",K15<>""),"WAITING ON ZIRC CHECK FROM DEBRA","No Action Required") &"")
becomes
=IF(AND(H16="",K15<>""),A1,B2)&"")

btw: Don't ever select a single cell when using Find/Replace even if you only want that one cell changed.
Find out why at your own peril.

[Keegan1116]

Ahh I See what you mean. Lmao "what if Debra leaves" had me laughing so hard for some reason. I suppose I did it like that just so I wouldn't have to have any ugly cells with random text anywhere in the spreadsheet, though I suppose I could just put it far down the line so no one sees it. It does also help for understanding the formula right away rather than searching cell by cell what does what. I am trying very hard to simply everything I can, so I'll certainly take this point and mull it over.

[BlindAlley]

Hey, I like Debra I hope she is there forever

One thing I like to do is have an info tab where I put all of these lists and Dropdown lists to reference in the formulas, and I copy some of the 'interesting' formulas into it in case I **erase the originals, and a few notes on how the spreadsheet works.

I then hide that tab.

** As if I have ever done that

[Keegan1116]

That's a great idea. I didn't know you could hide tabs!! I'll have to do this. How do you reference a cell on another sheet though?

[BlindAlley]

Damn it, I just lost a 20 minute typed reply because it logged me out before I could post it.

To answer your question in an active cell with your formula open you would select the 2nd tab and navigate to the cell or range you need to reference and select that, the new cell will show up in your formula after that you can either close the formula, navigate to a 2nd range or tab, or go back to the original cell.

You can see an example in your to do list tab where all of the formulas refer back to the project tab.

[Keegan1116]

That's happened to me too, but it was because I clicked the wrong 'submit' button. Thanks for all the help with this!

[BlindAlley]

That's happened to me too, but it was because I clicked the wrong 'submit' button. Thanks for all the help with this!

Thanks, glad to be of help.