Please can you help
In the attached pivot table I would like to be able to display the date a milesrone occurs as a diamond symbol instead of a "1".
Ideally I would like some idea of scale of the dates as well (with respect to one another) bu this is not essential.
Thank you in advance.
Regards
J
I don't know about a diamond, however to put an asterisk add a column to the source data and use the information in the tutorial linked below:
1. Formula for added column: =IF([@[Progress/Comments ]]="on schedule ","*","")
2. Link to tutorial: https://www.mrexcel.com/excel-tips/p...20the%20result.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Re: project schedule - display milestone as a diamond in pivot table
Thanks Jete. I used the tutorial to display the milestone value under the date which worked even better. What would be good would be if the month column shade changed for a different month. I have manually done it in the attached example.
Appreciate you help as always Jete.
A low tech way to do this might be to populate B2 using the following and then drag the fill handle over to cell K2:Formula:
Please Login or Register to view this content.
Select cells B5:K15 and use the following formula as a conditional formatting rule: =ISODD(B$2)
Before pressing OK be sure to select the third option for applying the formatting: All cells showing "Milestone Report" values for "Key Milestones (remaining)" and "Forecast Date"
Let us know if you have any questions.
that works very well Jete thank you. see attached. The best of both worlds is that the non-payment milestones can be expressed as an asterix. On w/s 1 I have changed the date to turn red if the forecast date is later than the baseline date. Do you know if the formatting can be automatically applied to the pivot table date? Also is there any way to list the 'progress/comments' date into column L (at the end of the pivot table contents?
You're Welcome.
Another low tech proposal.
1. Populate B1:K1 using: =INDEX(Table5[[Baseline date]:[Baseline date]],MATCH(B4,Table5[[Forecast date ]:[Forecast date ]],0))
2. The following conditional formatting rule is applied to All cells showing "Milestone Report" values for "Key Milestones (remaining)" and "Forecast Date": =B$1<>B$4
As you'll see in the field list I added a field Comments which are the text values for the Progress/Comments field. Unfortunately when dragged to the Values area they do not align in column L and I feel that if it is possible at all it would take VBA of which I am pretty much clueless.
I also want to point out that rows 1:2 may be hidden by changing the font color to white as modeled in row 2.
Thank You for marking the thread as 'Solved'. I hope that you have a blessed day.
Thank you Jeet one again! That works very elegently. When I did it I thought if the asterixed fields could be red also to signify that they had moved off the baseline that would be a bonus but not essential.
To highlight the asterisks try:
Select cells B5:K15 and use the following formula as a conditional formatting rule: =B5="*"
Before pressing OK be sure to select the third option for applying the formatting: All cells showing "Milestone Report" values for "Key Milestones (remaining)" and "Forecast Date"
Let us know if you have any questions.
thanks Jeet. That formula works to turn all asterix's red the way I did it. It would be good if the asterix only turns red when the forecast date is later than the Baseline date as per the dates which are now changing colour. Have a great day. J.
Try changing the asterisk rule to read:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Thank You very much indeed for your support on this Jeet.
I used both the following rules in this order because sometimes the dates may be different but there is no asterix I guess.
=B$1<>B$4
=AND(B$1<>B$4,B5="*")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks