Copy a line of data to a new worksheet every time value appears (without blank rows)

[bbsmuffins]

Hi,

I'm creating a project resource planner and I need help summarising it per person. On Sheet 1, the project name will be listed (i.e. Project A) alongside a named resource (i.e. Chris) and then a weekly time estimate plotted against a calendar. On Sheet 2, every time the named resource appears I want it to carry across the project name and estimated resource.

I've attached a simplified demo version; I want columns B-BC on Sheet 2 to autofill with data from columns B & D-BC whenever the names in column B of Sheet 2 appear in column C of Sheet 1.

This will be managed by somebody not very confident on Excel, so I really want a formula that will automatically do it rather than having to play around with pivot tables, filtering or any other manual function.

Thank you for your help

[CRIMEDOG]

So I added a couple of helping columns. Sheet 1 A:A added the project name next to each entry - you can use an Offset if it will ALWAYS be the same number of rows for each project - Then I inserted a row (D) to count the instance of Each name in the sheet :

Please Login or Register  to view this content.

ON SHEET 2 : Added in column A:A a count (series) beside each line so Chris has 1-10, Ellie has 1-10 etc... You have to unmerge B & C (it is not a good Idea to merge cells in this kind of application - just resize them )
in column B

Please Login or Register  to view this content.

This is an array formula.
Under each date

Please Login or Register  to view this content.

also an array formula.

There may be an easier way, but this is how I would try it.

Edit: Cheers - just saw you are in England