Milestones

[jasman]

Hi. Im creating a timeline in excel and need it to automatically enter milestones by looking at dates entered by the user in another sheet.

basically im after formulae in sheet1 for 2 rows of cells that:

: looks at the date in row 4 of that column and if it matches date in cell c4 of Sheet 2, then add a triangle into the current cell (i guess the traingle addign would be putting a little triangle on the sheet somewhere and if the criteria is met it copies and pastes the traingle cell into the current one?)

 

[Akashwani]

Hi,

Can you provide some sample data?

Excel Workbook
	A	B
1	Lookup Date	Status
2	02/05/2011	r
Sheet1

Sheet1 B2 needs formatting as Wingdings3 and you will get a triangle when there is a match.

Excel Workbook
	C
1	Dates
2	01/05/2011
3	02/05/2011
4	03/05/2011
5	04/05/2011
6	05/05/2011
Sheet2

I hope this helps you out.

Ak

 

[jasman]

thank you so much that worked.

 

[Akashwani]

Hi,

Thanks for the feedback, I'm pleased it worked for you.

Ak

 

[jasman]

Hi. Sorry one more queury related to this. There is a column next to A2 (Lookup date) column which has some text in it. Is ther anyway the text could also be added to the cell along with the triangle, or maybe adjusting the formula so that according to what the text is the traingle changes colour.

Text=

MJ8- Blue triangle
JU8- green triangle
FT8- yelow triangle....etc

Think its a longshot but would be impressive

 

[Akashwani]

Hi jasman,

You can.
Try this, in your formula use Char(53) format the cell as Webdings.
You will need to use Conditional Formatting. In 2007, Highlight your cell, go to Conditional Formatting, New Rule, Use a formula etc. Use something like this.... =$C$2=$D$2 (D2 has the text in) click Format and choose your formatting, click OK, click New Rule and repeat until you have set your 3 formats.......

Excel Workbook
	A	B	C	D
1	Lookup Date	Status	*	*
2	02/05/2011	5	Blue	Blue
3	*	*	*	Green
4	*	*	*	Yellow
Sheet1

Excel Workbook
	A	B	C	D
1	Lookup Date	Status	*	*
2	02/05/2011	5	Green	Blue
3	*	*	*	Green
4	*	*	*	Yellow
Sheet1

Excel Workbook
	A	B	C	D
1	Lookup Date	Status	*	*
2	02/05/2011	5	Yellow	Blue
3	*	*	*	Green
4	*	*	*	Yellow
Sheet1

As you can see from the examples above, you will need to choose your colours carefully to get a stand out triangle.

I hope that works for you.

Ak

 

[jasman]

hi. thanks but i cant reference other worksheets when using conditional formatting. Im in excel 2003

 

[Akashwani]

Hi,

You can, create a named range for each condition.
Highlight the cell with say Blue in, Right Click Name a Range call it Blue.
Then refer to the Named Range in your Conditional Formatting.....
=$C$2=Blue

I hope that helps.

Ak