Merging Cells with Same Text.

jcas

New member
Joined
Aug 2, 2011
Messages
11
Reaction score
0
Points
0
Hi I'm working on this timeline that is suppose to show the start and the end date for a certain number of tasks.Please see pics for a better idea. The dates are shown week by week (mondays) and they will change respecting to the program that they are related. So what I want to have is a timeline that will autopopulate the dates respecting to that first date. What i did was to relate the first cell in each row (year, month, day) with the start date that is on a separate sheet. Then I sum 7 days all the way to the right and for a row im displaying the years, for the other one the months and for the other one the days.

The problem is that to make it look better I want to merge all the cells that contain the same month or the same year. when i do this by using the merge and center button the sequence gets messed up because it is only using the value in the first merged cell and deleting the others.

Is there a way in which i can do al this automatically and not having the sequence problem? Or any suggestion of a simpler way of doing this?

thanks for the help
 

Attachments

  • what i have.JPG
    what i have.JPG
    77 KB · Views: 147
  • what i want.JPG
    what i want.JPG
    72 KB · Views: 77

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
I don't think it is a good idea to do that, merged cells will be more difficult to address in formulae. I would just use conditional formatting to hide the multiples.
 

jcas

New member
Joined
Aug 2, 2011
Messages
11
Reaction score
0
Points
0
I don't think it is a good idea to do that, merged cells will be more difficult to address in formulae. I would just use conditional formatting to hide the multiples.

But wouldnt that hide the months that are repeated over years?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Not sure if I understand what you mean, but you could test the year and month.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi jcas,

I've attached a copy of the file I use to plot out our budget timeline. It works using conditional formatting as Bob mentioned.

Change the dates in the green cells, and you'll see it updates the bars.

The trick is in getting the formula right. If you can do that in the grid, then you can make it work in the conditional formats. (And we can help you with that if you lay out your workbook -- or a sample -- and attach a copy.)

Hope it helps,
 

Attachments

  • Timeline.xlsx
    16.8 KB · Views: 194

jcas

New member
Joined
Aug 2, 2011
Messages
11
Reaction score
0
Points
0
Hi jcas,

I've attached a copy of the file I use to plot out our budget timeline. It works using conditional formatting as Bob mentioned.

Change the dates in the green cells, and you'll see it updates the bars.

The trick is in getting the formula right. If you can do that in the grid, then you can make it work in the conditional formats. (And we can help you with that if you lay out your workbook -- or a sample -- and attach a copy.)

Hope it helps,

Hi Ken:

That file is just great! Thats exactly what I will like to have. but to be honest, I have no idea on what I could do or what formulas should I put together to get something like that. I am a student and I'm starting programming with Excel. I have this assignment for my job and will really appreciate if you or somebody else give me a basic idea/examples on how I can make my file to work like yours.

thankss!
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Okay, so let's look at your frame right now (across the top). It's pretty non-standard. Are you comfortable setting up in a weekly format, or daily, or? (Right now you seem to have some that are 5 days, some that are more/less)

What I'd do is set up the frame of your report so that you have consistent dates across the top, then columsn to list your tasks start/end dates like I did.

If you can build up the frame you want, and upload it as a workbook (double click the "Reply to Thread" button or click it once then click "Go Advanced" to do this), we can help with the next part.
 

jcas

New member
Joined
Aug 2, 2011
Messages
11
Reaction score
0
Points
0
I worked on the frame, set up the dates by weeks (Mondays) and I put some of the tasks there to have as a guide. I am attaching what I did. Please help me with the next steps.

Thanks
 

Attachments

  • Timeline Heading (Practice).xlsx
    17.3 KB · Views: 59

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Try this
 

Attachments

  • XLGuru - 303 - Timeline Heading (Practice).xlsx
    15.7 KB · Views: 82

jcas

New member
Joined
Aug 2, 2011
Messages
11
Reaction score
0
Points
0
THanks BOB! Thats better than what I had. Now I need to know how can I format the months with colors so it can be more readable to the user. I made some minor changes to the file you attached. Thanks.
 

Attachments

  • XLGuru - 303 - Timeline Heading (Practice) with something else.xlsx
    17 KB · Views: 76

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi jcas,

So the formula you want is =IF(G$7<$F10,IF(G$7>=$E10,TRUE,FALSE),FALSE)

What you should do is:
  • Select your data range (G10:EL14) -- It is important to start the selection at G10
  • Set up a new Conditional Format using a formula
  • Enter the formula as above
  • Click Format-->Fill and choose a colour
  • Say OK till you're back out to the grid
At that point it should work for you.

If you need help finding the conditional format setup, let us know, but please also include what version of Excel you're using as it changed between 2003 and 2007.

HTH,
 

jcas

New member
Joined
Aug 2, 2011
Messages
11
Reaction score
0
Points
0
Ken that worked out great! Thanks!!! Im starting to love Excel!! Another question How can I do the formatting for getting a different color on each month like the timeline that you sent me? WHat would be the formula for this? Im using Excel 2007.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
You actually would set up 12 rules to do this.
  • Highlight from G10:EL50
  • Create a new rule based on formula: =Month(G$7)=1
  • Pick the colour for January
  • Repeat for the next 11 months, changing the last number in the formula to the month number
I'd also suggest that you go back and manage the rules to ensure that the "Stop If True" box is checked on each rule (including the one you already set up). This will prevent Excel from having to check needless rules once it's found a match. Make sure you sort them (using the arrows) starting with your existing rule at the very top, then the January, February, etc... rules as you move down.
 

jcas

New member
Joined
Aug 2, 2011
Messages
11
Reaction score
0
Points
0
Ken! i put the formulas and everything worked out really well. Thanks!!! I wonder if there's a way in which I could relate the actual week number (D5) with the week numbers in column (those in row 9) in such way that when each week reaches it will highlight the respective column. and also make it the active cell everytime the file opens. 'm attaching what I have so far.
 

Attachments

  • Timeline so far.xlsx
    19.4 KB · Views: 23

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Okay, so I'm going to make you do a bit more work on this one now. ;)

So the formula for the conditional format (providing that you start your selection in column G) is this: =G$9=$D$5

Now, you tell me where this rule should sit in relation to the others, and should it have "Stop If True" clicked?

As for selecting that cell automatically at open... yes, can certainly be done, but it involves VBA, and your file type must therfore changed to xlsm to run it. Are you okay with that?
 

jcas

New member
Joined
Aug 2, 2011
Messages
11
Reaction score
0
Points
0
Thanks!! I tink I did it right!! I put the formula right under the first rule and I clicked on the Stop if True box and it worked. And what are the consequences of changing it to xlsm? ALso if I enter a number in a cell like the '-135' that I have in G9 how can I make it sum (+1) automatically all the way to the right columns until it reaches 0?
 

Attachments

  • Timeline so far2.xlsx
    20.4 KB · Views: 23

jcas

New member
Joined
Aug 2, 2011
Messages
11
Reaction score
0
Points
0
ok never mind im ok with involving VBA. how can I do it?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Use a formula of, in H9 here,

=IF(G9<0,G9+1,"")

You also don't need to test every month in your CF, just use

=MOD(MONTH(G$8),2)=1 - odd months

and

=MOD(MONTH(G$8),2)=0 - even months
 

jcas

New member
Joined
Aug 2, 2011
Messages
11
Reaction score
0
Points
0
Bob Thats Greaat!!! do you know how i can set up the active cell for everytime I open the document.? I want the column of the current week (D5) to show everytime I open it. thanks again!!
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Do you mean you want it selected, or coloured (as you have with week 120 in your example). If the latter, should the colour in 120 be deleted.
 
Top