• 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.

HELP pretty please..Multiple tabs, weekly calender finding date with range etc

B

bellswhiskey

New Member
Well good afternoon all


So I could do with some serious help. after approx 5 hours I still haven't cracked it, and Im at a loss so any help would be appreciated. I am not an excel whizz and bear in mind this needs to be done with forumlas only (i.e. no macros)


so...here we go. I have 1 worksheet with 2 tabs The first tab is a 3yr Weekly calender, the 2nd tab has a range of project codes, associated tasks and the relevant dates these tasks need to be done by. I need the information in tab 2 to appear in the calender in tab 1 next or near to the closest date that was entered in Tab 2.


i.e.


Tab 1) The calendar

Project 1 Project 2 Project 3

Week 1 01/11/10

Week 2 08/11/10

Week 3 015/11/10

etc up to week 52 then

Year 2 starts - Week 1 01/11/11 etc


Tab 2) The project list and deadlines


Project 1 Project 2

Task 1 02/11/10 - (Data to appear in Tab 1) Task 1 02/11/10 - (Data

Task 2 10/11/10 - (data) Task 2 10/11/10 - (data)

Task 3 16/11/10 - (data) Task 3 16/11/10 - (data)


I cannot find any formulas that can do it...and Im sure it can be done .....so from a desperate if not slightly noob user of excel HELP!!!.


Cheers muchly


Bells
 
I'm going to assume that all dates are in a cell/column by itself. I'm afraid the general layout of your 2nd tab isn't quite clear, so I'll start by offering some suggestions, and we'll see where we get to.

First, XL has a hard time trying determine "closest" date, so we need a helper column to help it out. On Tab2, we're going to convert all the dates to 1st day of week using this formula:

=A2-WEEKDAY(A2-1,2)+1

The -1 and +1 are there to account for Tuesday being the "start day" of week.


Since both tabs use good labels of "project 1", 2, etc, we can determine which column of data we want to look at by using a combination of the INDEX and MATCH function. Index lets us define a large array, with arguements letting us choose row and/or column to look at. We'll figure out which column we want by using the MATCH function. Something like:

=INDEX(Tab2Data,,MATCH(Project_Name,Tab2_Row_with_project_names,0))

would let XL know the range of cells to look at. Then, we can probably combine it with some sort of LOOKUP formula to get the correct dates lined up. So, rough layout of formula:

=LOOKUP(Tab1_date,Index formula to get date helper column, Index formula to get data)


If you run into trouble, first try looking at the XL help file on the functions. You could also try posting a sample workbook, which would help us have a better feel for the layout.

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Oh my goodness you are wonderful..... and thanks for replying so quickly. You are quite right in that tab 2, the date and the data adjacaent to that date is indeed in its own column.


So....I am going to give this a whirl and have a look over the weekend and see how I get on. I have done a dummy workbook for you but will only upload this if I get into more serious trouble.... so as to not bother you too much.

Once again my sincere thanks and I'll let you know how I get on, on Monday.


You're a star


Bells
 
Good morning all


Here is a link to an uploaded file: http://hotfile.com/dl/130170344/ee46c1f/Project_calender_and_dates.xlsx.html
 
Nope Im still stuck, I just cannot seem to get the formula's to work, now maybe Im just being stupid, but any help would be gratefully appreciated, Im now starting to tear my hair out...and don't wish to go bald at such a (youngish) age .
 
Try the formula below. It's not a well written one but it seems to work on your test data. Few things you have to do though:


1. this formula goes into E3 and assumes column B has the dates

2. in the project dates and tasks sheet, the column (range) with the dates for a project should be named "Date1" for project 1, "Date2" for project 2 and so on

3. in the same sheet the column (range) with the tasks should be named "Task1" for project 1, "Task2" for project 2 and so on.

4. In the formula change all occurrences of "Date1" and "Task1" depending on which project you are looking for

5. Someone should be able to shorten the formula because it is too long, but I am not sure how to.


=IF(IFERROR(MATCH($B3-3,Date1,0),0)+IFERROR(MATCH($B3-2,Date1,0),0)+IFERROR(MATCH($B3-1,Date1,0),0)+IFERROR(MATCH($B3,Date1,0),0)+IFERROR(MATCH($B3+1,Date1,0),0)+IFERROR(MATCH($B3+2,Date1,0),0)+IFERROR(MATCH($B3+3,Date1,0),0)>0,INDEX(Task1,IFERROR(MATCH($B3-3,Date1,0),0)+IFERROR(MATCH($B3-2,Date1,0),0)+IFERROR(MATCH($B3-1,Date1,0),0)+IFERROR(MATCH($B3,Date1,0),0)+IFERROR(MATCH($B3+1,Date1,0),0)+IFERROR(MATCH($B3+2,Date1,0),0)+IFERROR(MATCH($B3+3,Date1,0),0)),"")
 
Another lengthy formula, but requires no change to your current setup. Put this in E3:

=IF(ISNA(INDEX('Project dates and tasks'!$C:$O,MATCH($B3,INDEX('Project dates and tasks'!$B:$N,,MATCH(E$1,'Project dates and tasks'!$B$1:$N$1,0)),1),MATCH(E$1,'Project dates and tasks'!$B$1:$N$1,0))),"",IF(COUNTIF(E$2:E2,INDEX('Project dates and tasks'!$C:$O,MATCH($B3,INDEX('Project dates and tasks'!$B:$N,,MATCH(E$1,'Project dates and tasks'!$B$1:$N$1,0)),1),MATCH(E$1,'Project dates and tasks'!$B$1:$N$1,0)))>0,"",INDEX('Project dates and tasks'!$C:$O,MATCH($B3,INDEX('Project dates and tasks'!$B:$N,,MATCH(E$1,'Project dates and tasks'!$B$1:$N$1,0)),1),MATCH(E$1,'Project dates and tasks'!$B$1:$N$1,0))))


You can then copy this across to col H and as far down as needed, and formula will auto-adjust. If dates and tasks sheet changes in size, note that while one reference is currently B:N, the other is C:O. This offset is to account for the dates and data columns.
 
Trying to explain:

The section of formula that gets repeated several times it the INDEX(match, match, etc) thingy. This is looking at the large array on sheet2, and using the MATCH function to determine which columns and which rows to look at.

Beyond that, it first checks to see if the formula would give an error. If so, return blank. Otherwise, check if the entry has already been pulled (the COUNTIF part). If it has, return blank.
 
Well I just want to say how wonderful you all are here..... You really are truly brilliant. Many Many thanks for your help,


Oh and sorry it took so long to reply....I have been working on this spreadsheet for sometime now, and this is the last bit...


Also, in order to understand the formula better is there a site you recommend. What I would like to understand better is how the formaula is able to look at the date on Tab 2 and then "match" it to the nearest week etc on Tab 1


This is just so: 1) I understand it and 2) in case anything goes wrong I am able to fix it.


once again many thanks


Bells
 
Hello again all


I may be doing something wrong here.....but for some obscure reason the formula is not picking up all of the data, for example Tab 1 is picking up the text Task 1 project 1 for example but is not picking up the text PMC project 1 , PMC 2 project 1 but is picking up the text PMC 3 project 1....... (which all appear in column C on tab 2)


It probably is me being extremlely dense, so forgive my stoopidness... :) otherwise it all works fine ........
 
Hi ,


Can you let me know if my understanding of your problem is correct ?


1. You have a list of tasks for various projects ; the projects are in columns B , D , F , H , J , L and N.


2. The tasks are in the rows 2 through 20.


3. The tab 1 contains a weekly calendar from 2011 all the way till 2029 !


4. For each project , for each task in tab 2 , you wish to check the task date in column B ( or D , F , H , J , L or N for the other projects ) , and put that task name in the corresponding row in tab 1 , where the task date is closest to the date in the weekly calendar.


Is this correct ?
 
Hey Naray


That is indeed correct, the example spreadsheet is a lot smaller than the actual version since there are approx 20 projects.


At the moment I have been manually mapping the data onto the weekly calendar and with so many projects I was hoping to create some formula's to make it more automated.


However, it has proven to be more difficult than I anticipated, and everyone on here has been brilliantly helpful. It also doesn't help that the "weekly year must run" from 1st November to 30th October, (at themoment it doesn't i know) . So week 1 always starts 1st November


Bells
 
Hi Bells ,


Sorry I'm late in replying ; partly it is due to the time difference , since I'm in India.


I was trying the following workaround , and you can let me know if it suits you.


1. Type in the following formula in a different area of your Project dates and tasks Worksheet , say cell P2 : =MATCH(B2,'Project calendar'!$B$3:$B$220,1) ; this tries to find the closest date in your Project Calendar , which matches your task date. There is a possibility this match can be smaller than the task date e.g. a task date of December 1 , 2011 will match November 29 , 2011 in your Project Calendar , but this may not suit you.


2. Type in the following formula in Q2 : =INDEX('Project calendar'!$B$3:$B$220,'Project dates and tasks'!P2) ; this gives the entry number in the list of dates in your Project Calendar which matches the task date in step 1.


3. Type in the following formula in R2 : =IF(Q2<B2,P2+1,P2) ; this checks to see whether the matched date is less than the task date ; if it is , it increments the index by 1 , so as to go to the next entry in the Project Calendar.


4. Type in the following formula in S2 : =INDEX('Project calendar'!$B$3:$B$220,'Project dates and tasks'!R2).


5. Copy the list of tasks in column C , to column T ; this is because we want a lookup table , and since we will be looking up the date , the column of tasks cannot be to the left of the dates column.


6. In your Project Calendar Worksheet , type in the following formula , in cell E3 : =IF(ISERROR(VLOOKUP(B3,'Project dates and tasks'!$S$2:$S$15,1,FALSE))," ",(VLOOKUP(B3,'Project dates and tasks'!$S$2:$T$15,2,FALSE))) ; this will insert the task name corresponding to the date which is closest to the task date.


The only problem here seems to be that you can have multiple tasks on the same date !


I don't know how to tackle this.


Let me know how it goes.


Narayan
 
Hi Narayan and the rest of the group.

I just wanted to say thankyou to you all for all the help. I have however cracked it and it seems to work. It doesn't involve any complicated formulas except look up's and TRUNCs. In order to make it work I had to re-think it and this is what I did as follows:


New tab with Dates in weeks and days

Vlookup "from projects page" to New Tab

Vlookup from calendar sheet to Project sheet.


I have attached a new version so that you can see. Im sure there are better ways to get this done but to be honest it works. However I would not have been able to come to this conclusion had it not been for everyone here.... once again thanks


http://hotfile.com/dl/132199891/6d26ba6/Project_calender_and_dates_--13-10-11.xlsx.html
 
You must log in or register to reply here.
Back
Top