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!
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!