Reference Timescale or Location Axis with Formulas

Have you ever needed a shape to span the full height or width of the chart? Maybe you’re using block shapes as vertical “curtains” to represent a work window or project-wide activity, or horizontal “swimlanes” to group related activities? Or perhaps you’re striking a line across a linear schedule to represent a contract milestone?

Schedule containing curtains and swimlanes

These shapes can be defined using dates and locations that match the edges of the chart, but this requires manual updates if the chart axis is changed. Turns out there’s an easier way.

Thanks to source code, the timescale and location axis controls on the GraphicSchedule ribbon are also tracked as named variables that can be referenced in Excel formulas. The named variables are as follows:

This means you can link your Start, Finish, Loc1, or Loc2 entries directly to these xMin, xMax, yMin, or yMax variables using Excel formulas. So, if you want to achieve the look shown in the schedule above, just create some simple formulas in your data table, like this:

Much easier, right?

However, there are couple of caveats associated with this tip:

  1. xMin and xMax always reference the Timescale, even if you flip the axes to plot time on the vertical axis. Likewise, yMin and yMax always reference the Location Axis. Sorry, that’s just the way it is. [insert complicated programming explanation here]
  1. In some cases, shapes linked to these variables may not redraw immediately after changes are made to an axis. [again, it’s complicated] The good news is these shapes will redraw accurately as soon as you take your next action in the data table. You can also force linked shapes to redraw by pressing the ‘Calculate Now’ button on the Formulas ribbon.

 

Enjoy those linked variables!