Dynamic TEXT Axis Chart

[Rhonda502]

I need to provide a quad-week and single week 'look' option in the same chart. If I choose a date axis I can get the weeks to expand and contract showing what I need (for the most part). But I loose some of the chart aesthetics which is not ideal because this applies to 4 of 8 'group' charts.

My Ask:

Can someone help me create a dynamic 'text' axis that will not chart cells with the #N/A value. And perhaps better stated, if the date is #N/A (resulting from formula), ignore those cells.

Any help appreciated!

Rhonda502

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

[Rhonda502]

Example Spreadsheet Attached.

My ask from original post: I need to provide a quad-week and single week 'look' option in the same chart.
If I choose a date axis I can get the weeks to expand and contract showing what I need (for the most part).
But I loose some of the chart aesthetics which is not ideal because this applies to 4 of 8 'group' charts.
Any assistance appreciated.

[Glenn Kennedy]

I am not 100% sure if this is what you want. I changed the formula in h13 to:
=IF(H6="","",H6)

and the formulae in H16 and below to:

=IF(H7>0,H7,NA())

your formula put something in that LOOKED like the #N/A error, but was just text, not the error. I also set the X-axois range to automatic.

Any good??

[Rhonda502]

Glenn,

Thank you for the assist. Your formula is much cleaner and one I didn't know. I've added a screen shot which might better explain what I'm trying to achieve.

Dynamic Chart Picture.png

[Glenn Kennedy]

I'm about to go away now, but will be back in the Irish morning. Will look at it again.

[Glenn Kennedy]

I think I may have added the wrong file last night. Is this any better?

[Rhonda502]

Glorious!!! ! Thank you Glenn!

[Glenn Kennedy]

You're welcome!!

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.