• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting project start and finish dates from excel calendar to cells in designated tab.

N

noob noob noobie noo

New Member
Hi, really hope someone can help.. I have two tabs. Tab 1 is a simple calendar, dates down the page in a column. The subsequent columns in the tab are for individual project tasks. Hours worked/planned are entered into relevant date/project cell.
I’d like to then extract the first date and last date for each project and have them inserted into designated cells in the second tab.
Many thanks for any help!
 
That's great Narayan! Many thanks! I'll need to spend a little time understanding how that works, hopefully I'll be able to plug it into my main workbook.

Cheers :)
 
Hi Narayan, I've expanded three of the fields, 'dates', 'datarange' and 'jobsheader'. I did this through the 'Name Manager' via the edit function. For some reason this causes '#N/A' (value not available error) to appear. Any idea how to solve this? I'd learnt a lot trying to figure out what you did, thought I'd cracked it, but it seems I'm still in the dark a little here. Really great if you could help.

I've uploaded a workbook with the error.

Many thanks
 

Attachments

  • workload plan test range.zip
    668.7 KB · Views: 7
Hi ,

When you use a MATCH function , the lookup range is supposed to be either a single column or a single row.

You have used a named range JobsHeader , which though really is a single row , because of merged cells has become 3 rows high. Change the definition of this named range to :

=CALENDAR!$D$1:$XC$1

Narayan
 
You must log in or register to reply here.
Back
Top