Hi Excel Fans,
Today I am posting the Friday Challenge a day early to give you time to try it out.
Here the user story on what we are trying to do:
‘As an Excel user I want to leave all my data intact (i.e. not delete or hide any rows) and I want to conditionally show the data points in an Excel column chart by typing in “Yes” next to the data.’
Here is what I mean with pictures:
(See as I type in a Yes in column C, then the data point is added to the Excel Chart and as I delete the Yes the column is removed from the graph.)
Here is the data set (Copy and paste it from this table to cell A1 in your test spreadsheet:
Excel 2012
| A | B | C | |
|---|---|---|---|
| 1 | 2011 | Display | |
| 2 | a | 330.33 | Yes |
| 3 | b | 329.35 | |
| 4 | c | 359.85 | |
| 5 | d | 376.94 | Yes |
| 6 | e | 446.86 | Yes |
| 7 | f | 457.7 | |
| 8 | g | 509.38 | |
| 9 | h | 549.9 | Yes |
| 10 | i | 566.84 | |
| 11 | j | 585.2 | |
| 12 | k | 658.24 | Yes |
| 13 | l | 720.2 |
This embedded resource is unavailable in the restored edition.
There are probably several ways to complete this task, so lets see what you got. If you want to contact me to send in how you did it, leave me a comment below (it asks for your email) and I will contact you for the file.
Good luck!
Steve=True
Reader discussion
Comments preserved from the original page. New comments are closed.
Reader
January 16, 2014 At 10:57 am
My VBA free solution has been sent. There is an easy way to solve this with VBA if desired.
Reader
January 16, 2014 At 1:06 pm
Interesting approach!
Reader
January 16, 2014 At 11:07 am
My workbook is on it’s way. It has a complicated formula that I had to dig up from a past project, but it works.
Reader
January 16, 2014 At 1:07 pm
Glad you were able to figure it out.
Reader
January 16, 2014 At 5:57 pm
Solved it using named ranges that incorporate offset If you send me an e-mail I’ll send to you
Reader
January 16, 2014 At 6:27 pm
Thanks Ron. I have sent you an email with my contact info. Sounds like one of the solutions. Steve=True
Reader
January 18, 2014 At 4:54 am
Hello! I am new to this forum & like your Blog very much. I have a solution for the challenge. I just have another table based on ‘Yes’ & Row number to extract the value from the main table. Finally I have used offset & named ranges to define its Series & category name. I can send my solution if you provide me your email address. Thanks.
Reader
January 20, 2014 At 8:53 am
Thanks Maruf,
I have sent you an email address for your submission. Sounds like you have it right.
Steve=True