Change calculated field in pivot based on option button

[dancing-shadow]

Morning - hoping this is a quick and easy fix!

I have an option button on the same sheet as a pivot table. The pivot table has two similar calculated fields currently

Within SLA % = 'Within SLA' / Projects
Outside SLA % = 'Outside SLA' / Projects

What I want is to toggle both calculated formula between /Projects and /Jobs by clicking the option button.

The code I currently have is not working:

Please Login or Register  to view this content.

Am I going about this the right way or is there a better option?

TIA

[dflak]

Here's my suggestion. On the pivot table page, have a data validated box that has "Within SLA" and "Outside SLA" as its choices (Cell B1 on sheet Pivot for the sake of example). In the source data for the pivot table, create a helper column called Numerator with the formula: =IF(Pivot!$B$1 = "Within SLA",[Within SLA],[Outside SLA]). Then the calculated pivot table formula becomes Numerator / Jobs.

Toss in a change event on Pivot!B1 to refresh the pivot table when it's changed.

[dancing-shadow]

Thanks for the good suggestion (hadn't actually thought of this). Having tried it out though I've found an issue is that I have freeze panes on the adjacent column (yes, I could move this but it'd then keep a random column of data in view, which looks awful, and I can't amend the columns in the table underneath as it's a pivot). If the sheet is scrolled to the right, the drop-down box for the data validation does not appear as it is hidden behind the frozen column... Users would be very confused by this and claim it's not working and they're unable to select what they want...

Edit:
I just found this - https://www.mrexcel.com/forum/excel-...vot-table.html
I don't really understand it well enough though, but maybe I can use it for what I want?

[dancing-shadow]

I've done it, although it could probably be written in a better way. Decided to delete the calculated field altogether and re-create it with the formula I need. So I have 2x macros (one for each option) - one calculates on Jobs, the other on projects.

Please Login or Register  to view this content.