Dynamic Variable within formula?

Greetings all - thanks for all the past help and for letting me get as far as I have. I have spent some time browsing the KB, but have not seen anything like this in the past. If I missed it, sorry for the post - please direct me there....

I have a spreadsheet (attached) being driven off of pivot data where the user can select the months from B2 to view the data needed. The formula in cell B48 reads =B47/12/13 What this is doing, is taking the total from cell B47 and then dividing it by 12 shifts then dividing it by 13 individuals. This works famously if the user chooses only one month, but falls over if the user chooses two months because the formula cannot accommodate the additional 12 shifts for the second month. In other words, the corrected formula should read =B47/24/13 because there are now 24 shifts for 2 months of data. Similarly, if the user chooses any number of months, the script should count the number of months selected and multiply by 12. The result should be put into the middle position of the formula.

This issue occurs in parts of rows 48, 58, and 66.

Can anyone suggest a solution for this issue?


Thanks,
Ron

I was only able to create a UDF, so I had to save it as a xlsm.

I only tested it on Row 48.

I'm wondering if an Array of PivotItems could be created?????

Anyway, here's the code.

Code:

---

Function CalcShifts(B47 As Long) As Long
Dim AddShifts As Long
Dim X As Long
With Sheets("2010").PivotTables("PivotTable1")
    For X = 1 To .PivotFields("Month").PivotItems.Count
        If .PivotFields("Month").PivotItems(X).Visible = True Then
            AddShifts = AddShifts + 12
        End If
    Next
End With
CalcShifts = B47 / AddShifts / 13
End Function

---

David,

I don't know how you did it, but I am elated that you did it!
Can you go over the problem solving process you used to get to the solution?

Again Many thanks,
Ron

Quote:

---

Originally Posted by RonZ

...but I am elated that you did it!

---

Glad I could help.

Quote:

---

Can you go over the problem solving process you used to get to the solution?

---

Well, I knew that since the dropdown was a list, that it had to be respresented in a collection somewhere. So I opened up my Excel book (read my profile for a description of the books.), until I figured out Visible = True was how Excel determined how it used the items.

Then I had to figure out the object name. I recorded a macro selecting one, then two months. That showed me the names used. (PivotFields("Month").PivotItems(X))

The next step was iterating through the collection. When I found one visible, add 12 to the total number of shifts. AddShift = AddShift + 12.

Finally, move all that into a UDF function so it could be used in a sheet function.