Voiceman1000
New Member
Hi all,
All of the Product Roadmaps in Excel that I've ever seen are terrible, and manual, amounting to nothing more than a basically a PowerPoint slide that requires the user to manually create chevron shapes for each product or major delivery, stretch it out over some desired timeframe, color code each one, and then manually type a label on each one. What a waste of time and a waste of Excel!
I am trying to create an "automated", date-driven Product Roadmap for a client (and will use for many future clients, and for myself!) that will dynamically show which Product(s) (= rows) will be delivered across a timeline (= columns, as months/weeks). This will be based on a "Start" and "End" date column that will be filled in for each Product, which will determine where the "bar" starts and ends across the timeline matrix. Very much like a Gantt Chart, at a very high (product) level. So the only thing a product/program manager ever has to enter, is the "Product Name" and a "Start" and "End" date for each one, and presto, this Excel template will automatically map it all out. On-going changes to the Roadmap would be super easy to maintain and update, saving a ton of time and creating a consistent, professional looking deliverable.
Mapping Products to timeline using Start and End Dates
Easy - got this licked already in the attached. Used Conditional Formatting to check the Start and End dates for each product against each week (columns) and fill in the cell with a color as appropriate. This works, but produces a block-y, squarish looking roadmap "bar" graphic for each product; not very attractive and not how these are typically represented in the many manual-intensive powerpoint or excel right-chevron looking bar graphics - which is what I want to achieve.
ASK #1: Add a right chevron terminator on the end of each bar graphic
To make this look more professional/executive/standard: How do I terminate each bar graphic (on the right week) with a right chevron, using the same size (= full height of the cel) and same color coding as the rest of the bar? Can I do this in Conditional Formatting (I've used Conditional Formatting for many years, but I'm not aware of being able to insert text or characters or a graphic of any kind into a cell based on conditional formatting; I've only seen colors, shadings, borders, fonts etc controlled by CF...)? Do I have to use a Formula in each cell, and if so, what character from which character set will yield the right size/shape for a right chevron?
ASK #2: Add the "Product Name" dynamically to the center of each bar graphic
This may be tied somewhat to Ask #1 (or not, depending on design suggestions from you all): I want to be able to put the Product Name on each bar graphic, centered in the middle of the graphic. I've figured out in the attached how to calculate this and place it in the right cell; that's not the problem I'm running into. The problem to solve is this: In order to keep this dynamic, if I use a cell-based formula to determine where I am and if I'm supposed to stick the Product Name in that cell, I've got to replicate the formula in every cell in the Product Roadmap timeline matrix. This leads to the problem: because each cell now has something in it (even if the formula says to put in "" if it's not the right cell), the Product Name will be truncated both left and right (presuming each cell is center justified) because there's something in every adjacent cell, so Excel won't allow the text to display fully over the cell's left and right boundaries (as it would if there were nothing else in those adjacent cells). So, I have been trying to find a way to "drop in" the Product Name into the right cell, based either on Cell Formatting or a macro?
Anybody have any suggestions for either one of these "Asks", or both?
I'm attaching my "Work in Progress" spreadsheet for your perusal. Also attaching a screenshot of it below as well, where you can see I've included example right chevron shapes with the Product Names centered on them (the "old fashioned" way) for reference on how I'd like the resultant spreadsheet to look.
Thanks so much for your help!
All of the Product Roadmaps in Excel that I've ever seen are terrible, and manual, amounting to nothing more than a basically a PowerPoint slide that requires the user to manually create chevron shapes for each product or major delivery, stretch it out over some desired timeframe, color code each one, and then manually type a label on each one. What a waste of time and a waste of Excel!
I am trying to create an "automated", date-driven Product Roadmap for a client (and will use for many future clients, and for myself!) that will dynamically show which Product(s) (= rows) will be delivered across a timeline (= columns, as months/weeks). This will be based on a "Start" and "End" date column that will be filled in for each Product, which will determine where the "bar" starts and ends across the timeline matrix. Very much like a Gantt Chart, at a very high (product) level. So the only thing a product/program manager ever has to enter, is the "Product Name" and a "Start" and "End" date for each one, and presto, this Excel template will automatically map it all out. On-going changes to the Roadmap would be super easy to maintain and update, saving a ton of time and creating a consistent, professional looking deliverable.
Mapping Products to timeline using Start and End Dates
Easy - got this licked already in the attached. Used Conditional Formatting to check the Start and End dates for each product against each week (columns) and fill in the cell with a color as appropriate. This works, but produces a block-y, squarish looking roadmap "bar" graphic for each product; not very attractive and not how these are typically represented in the many manual-intensive powerpoint or excel right-chevron looking bar graphics - which is what I want to achieve.
ASK #1: Add a right chevron terminator on the end of each bar graphic
To make this look more professional/executive/standard: How do I terminate each bar graphic (on the right week) with a right chevron, using the same size (= full height of the cel) and same color coding as the rest of the bar? Can I do this in Conditional Formatting (I've used Conditional Formatting for many years, but I'm not aware of being able to insert text or characters or a graphic of any kind into a cell based on conditional formatting; I've only seen colors, shadings, borders, fonts etc controlled by CF...)? Do I have to use a Formula in each cell, and if so, what character from which character set will yield the right size/shape for a right chevron?
ASK #2: Add the "Product Name" dynamically to the center of each bar graphic
This may be tied somewhat to Ask #1 (or not, depending on design suggestions from you all): I want to be able to put the Product Name on each bar graphic, centered in the middle of the graphic. I've figured out in the attached how to calculate this and place it in the right cell; that's not the problem I'm running into. The problem to solve is this: In order to keep this dynamic, if I use a cell-based formula to determine where I am and if I'm supposed to stick the Product Name in that cell, I've got to replicate the formula in every cell in the Product Roadmap timeline matrix. This leads to the problem: because each cell now has something in it (even if the formula says to put in "" if it's not the right cell), the Product Name will be truncated both left and right (presuming each cell is center justified) because there's something in every adjacent cell, so Excel won't allow the text to display fully over the cell's left and right boundaries (as it would if there were nothing else in those adjacent cells). So, I have been trying to find a way to "drop in" the Product Name into the right cell, based either on Cell Formatting or a macro?
Anybody have any suggestions for either one of these "Asks", or both?
I'm attaching my "Work in Progress" spreadsheet for your perusal. Also attaching a screenshot of it below as well, where you can see I've included example right chevron shapes with the Product Names centered on them (the "old fashioned" way) for reference on how I'd like the resultant spreadsheet to look.
Thanks so much for your help!
