Need help with formulas to populate task names and convert dates to count values.

Need help with formulas to populate task names and convert dates to count values.

Excel Help Item 1 -->I would like cells B3, B9, B15 & B21 in Sheet1 to auto populate the task names from the ADR sheet, row 1 (Currently, I pasted the task names in these cells). The formula should take into account that the task names in the ADR sheet will vary from day to day. Don't need the (Plan, FCST or ACT) part in these cells, just the main task names only.

Excel Help Item 2 --->I would like cells C4:C6, C10:C12, C16:18 & C22:24 in Sheet1 to auto populate the three task names from their respective main tasks from the ADR sheet. (Currently, I pasted the task names in these cells).The formula should take into account that the task names in the ADR sheet will vary from day to day.

Excel Help Item 3 --->Lastly, I would like the date values from the ADR sheet to populate as count values for each of the three Plan, FCST and ACT items, per task, for each Week Ending column in Sheet1.

Does each task always have 3 phases? Is the data layout the same as in your real sheet?

OK. Take a look at this, then.

I've done 2 or 3 manual checks and it seems OK to me.

You're welcome & thanks for the reputation. Any problems, just shout...

I do have a quick follow up question. I just realized that the dash between the task name and the Plan, FCST and ACT phases doesn't exist on the ADR sheet. Would it be possible to update the formulas to accomidate that?

So, basically there is the task name 1 space and then the phase: Plan, FCST, ACT.

So ADR B1 reads: "Task 1 Plan" correct? How many tasks can there be - more than 9??

Can be done, BtW. However, it's a bit of a pity though - as the hypen was very useful!!

I guess it must be v. late night with you; so I'll just take a shot at it with this one...

OK. The solution will work with Task numbers >9.

Here is an augmentation to the workbook prepared by Glenn Kennedy. Use his formulae for the Date counts.

I have used column A as a helper and added a list for the tasks. Just add a new task at the bottom of the list of tasks and fill the formula in column C down as far as required for the number of tasks and the column will be correctly filled in. Conditional formatting takes care of the formatting.

Back at Post 3, you said that there were 3 phases in each task. Now it appears that there are 8. How many can there be... 3, 8 or any number in between?

And another thing.... What sort of entry goes into the 8th column in each set (AssignedTo), a date, text, or what?

One final piece of confusion. Do you want all 8 phases shown on sheet 1, or just the first 3.

No replies and i have to go shortly. Take a look at htis. If it's still not right, please specify exactly what output you need.

Sorry, the report just got update by our IT so there are now 8 phases per task but I still only care about the 3 (Plan, FCST & ACT). In the 8th column (Assigned To) is where a persons name would populate so Text. I only care about the date to count values in Sheet1 for the Plan, FCST and ACT phases of each task.

The column headers on the added worksheets don't match the earlier data. The yellow area shows an example of what you have to change in order for the formulae to work IF you amend the headers like I did on worksheet ExADR1 which will allow the use of a wildcard match with the values in column C of Sheet1.

The formula used is just an update of that given by Glenn Kennedy.

Glenn - This works great, I appreciate your help.

One last request on this and I think I'm golden. In the attached Sheet1 tab I inserted rows 7, 8 & 9. I'm going to add this to all tasks in Sheet1 and this will be for a cumulative total for each (Plan, FCST & ACT) from the ADR tab. Cells D7:D9, highlighted in yellow are where I need the formulas. So, cells D4:D6 in Sheet1 are looking at weekly incremental date to count values from the ADR tab for any ADR report pasted there. I would like D7:D9 in Sheet1 to look at all dates in the ADR tab for each phase (Plan, FCST & ACT) and put a cumulative date to count value up to the date in D3 in Sheet1. Is this doable?

I've spent most of the day constructing a compost heap; I'm tired and your words are dancing in front of my eyes. Can you run that past me again, slowly, and maybe give me a manual calculation of the result you're expecting.

I'll give it a bash in the UK morning. For a change, it's sunny and warm here. Beer and BBQ time. G'nite.

Absolutely, have a great nite and thank you again for all your help.

I think I figured out the cumulative values. Of course, I simply tweaked one of your formulas.

Thanks to the both of you that responded.