Hi all,
I have searched and searched. Unfortunately i haven't found an answer to the question; how (IF) can i reference a field outside of the pivot table in a pivot table field formula?
What do i want:
To compare a calculated field in the pivottable [that contains percentage of row] to 'Activesheet!$A$1'
If the calculated field is higher than cell value i want to show that field.
I want to hide all the fields where it is less or equal to that value
Problem1:
When i try to reference the 'Activesheet!$A$1' field inside of a pivot table formula it states no reference can be made ('references to matrix, named...' are not supported...')
Problem2:
I can't select a calculated field as a filter criteria?
(i can work around this by using the calc field in table and then setting filter on that column, so this is less of a problem than problem 1)
if Problem1 is impossible to solve:
Is there a way to get the total number of columns and use that in the pivot table formula?
If an example is needed let me know and i will provide one (the workbook this is all in is quite heavy with vba code; so will create one without, so it is more clear than).
Thank you kindly!,
deduijk
I have searched and searched. Unfortunately i haven't found an answer to the question; how (IF) can i reference a field outside of the pivot table in a pivot table field formula?
What do i want:
To compare a calculated field in the pivottable [that contains percentage of row] to 'Activesheet!$A$1'
If the calculated field is higher than cell value i want to show that field.
I want to hide all the fields where it is less or equal to that value
Problem1:
When i try to reference the 'Activesheet!$A$1' field inside of a pivot table formula it states no reference can be made ('references to matrix, named...' are not supported...')
Problem2:
I can't select a calculated field as a filter criteria?
(i can work around this by using the calc field in table and then setting filter on that column, so this is less of a problem than problem 1)
if Problem1 is impossible to solve:
Is there a way to get the total number of columns and use that in the pivot table formula?
If an example is needed let me know and i will provide one (the workbook this is all in is quite heavy with vba code; so will create one without, so it is more clear than).
Thank you kindly!,
deduijk