Need Excel to return first and last value from a specific range

  • I am trying to build a CONCATENATE formula using 2 values fetched from a range elsewhere in the workbook. This would be repeated 52 times so ideally I would want that formula to increment by itself.
    Here's the situation. I am building a workbook to be used in tracking hours spent on a project, for various departments. The project manager will input hours daily into the spreadsheet on the appropriate sheet (there is one sheet per month) and I want a summary page where we can quickly see total hours per department, per week and month.
    I have attached a workbook with a rough structure of what I'm looking at. The 2 cells in yellow on the Summary page are where I want to put this formula (and further on down of course). What I want is for the formula to look at the value in the B column (Week 1), then go in the appropriate monthly sheet (January in this case), and return the start and end dates of this particular week. The end result of C3 should be 'From 01/01/2013 to 05/01/2013' and so on, for 52 weeks.
    So far I have tried a mix of OFFSET, INDEX, MAX and MATCH functions but I can never get it right... it's driving me crazy!
    In an ideal world, column B (containing Week 1, 2, etc...) would automatically populate from the monthly sheets but since it's not much work doing it manually, I don't mind if there isn't a formula for it.
    Hopefully that makes sense, please don't hesitate if you have any questions.
    Thanks for any help you can provide!

  • Re: Need Excel to return first and last value from a specific range

    Q2 and Q3 contains the names of the Worksheets - so should be populated with all the sheets - I have just done for January and February


    then it will do a 3D look across all the sheets for the week number

    as the departments are in specific columns - then the formula just needs to be changed for each column to sum

    Then you will need to change the formula for each department
    and also need to unmerge the week numbers

    see attached -

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!