• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot table calculated field

Hi,

Use an If condition in F: if E is empty then F = "" else "the formula you already have"
In G, IFERROR return 0.
In you code use the following for F (replace line 49 of module 3):
Code:
  ActiveCell.FormulaR1C1 = _
  "=IF(RC[-1]="""","""",[@[Date and time escalation was resolved]]-[@[Date and time escalation was received]])"

and the following for G (replace line 123 of module 3):
Code:
    ActiveCell.FormulaR1C1 = "=IFERROR(IF([@SLA]*24<1,1,0),0)"

On a side note, I noticed this sample isn't using some of the things we discussed earlier (the formula for when the issue is resolved in the next day, for instance).



Hi Pcosta87

Thanks a lot my friend, I have rectified that issue by rounding the numbers
, also the if formula works fine to solve the empty cell, now with the issue that
we discussed earlier if the issue is resolved next day, I tried to combine the o2 things together but the error message comes.

Just wondering how do we combine 02 if statements together.

1. ActiveCell.FormulaR1C1 = _

"=IF(RC[-1]="""","""",[@[Date and time escalation was resolved]]-[@[Date and time escalation was received]])"


2. ActiveCell.FormulaR1C1 = _
"=IF(And(DAY([@[Date and time escalation was resolved]])>DAY([@[Date and time escalation was received]]),[@[Date and time escalation was resolved]]-[@[Date and time escalation was received]]-""15:00:00"",[@[Date and time escalation was resolved]]-[@[Date and time escalation was received]], RC[-1]="""","""",[@[Date and time escalation was resolved]]-[@[Date and time escalation was received]]))"


Other very interesting thing I noticed, if I give this file to someone else
to use it , as long as the data is there and they refresh the pivot table then its fine, but if the SLA sheet is empty and someone accidently refresh the pivot table then pivot table collapse as it cant find the within SLA cell and then it shatters and then I have to edit the pivot table again which is a pain. is it possible when the SLA sheet is empty then nobody can refresh the pivot table.

thanks

Tauqeer
 
Hi,

Something like this:
Code:
ActiveCell.Formula = "=IF([@[Date and time escalation was resolved]]="""","""",IF(DAY([@[Date and time escalation was resolved]])>DAY([@[Date and time escalation was received]]),[@[Date and time escalation was resolved]]-[@[Date and time escalation was received]]-""15:00:00"",[@[Date and time escalation was resolved]]-[@[Date and time escalation was received]]))"
 
is it possible when the SLA sheet is empty then nobody can refresh the pivot table

Hmmm, perhaps protecting the sheet where you have the pivot table until the SLA sheet has data...
Untested though.
 
Hi Mate , I have tried the if formula , its coming with error.
Here the sheet.
 

Attachments

  • Esc_SLA Data 11 Apr 2017 trial macro.xlsm
    495.3 KB · Views: 2
When I put this code to combine the 02 if statements, it comes with run time error, and debug shows the below code as Highlighted in error.



ActiveCell.FormulaR1C1 = _
"=IF([@[Date and time escalation was resolved]]="""","""", IF(DAY([@[Date and time escalation was resolved]])>DAY([@[Date and time escalation was received]]),[@[ Date and time escalation was resolved]]-[@[Date and time escalation was received]]-""15:00:00"",[@[Date and time escalation was resolved ]]-[@[Date and time escalation was received]]))"



If I remove the first part "=IF([@[Date and time escalation was resolved]]="""",""""


Then it works fine to cater the next day resolve and deduct 15 hours .
 
Hi,

I noticed you have some extra spaces in the field names that shouldn't be there...
Try it with:
Code:
ActiveCell.Formula = "=IF([@[Date and time escalation was resolved]]="""","""",IF(DAY([@[Date and time escalation was resolved]])>DAY([@[Date and time escalation was received]]),[@[Date and time escalation was resolved]]-[@[Date and time escalation was received]]-""15:00:00"",[@[Date and time escalation was resolved]]-[@[Date and time escalation was received]]))"
 
Fantastic, really appreciate your help, it worked, only last row was empty but not
a big deal, atleast you have solved the problem, I am attaching this sheet for you, now the only thing left is to stop the pivot to refresh if the sla sheet is empty, may be a greyed out the refresh button if sla sheet is empty or hidden pivot table sheet if the sla sheet is empty...
 

Attachments

  • Esc_SLA Data 21 Mar 2017 trial.xlsm
    646.8 KB · Views: 1
Fantastic, really appreciate your help, it worked, only last row was empty but not
a big deal, atleast you have solved the problem, I am attaching this sheet for you, now the only thing left is to stop the pivot to refresh if the sla sheet is empty, may be a greyed out the refresh button if sla sheet is empty or hidden pivot table sheet if the sla sheet is empty...
I'm glad it worked :)

As for the PT, I wouldn't go as far as hiding the sheet (although that is also a solution)... have you tried protecting it?
You could add something to the SLA code to remove the protection (or the hidden status) from the PT sheet. Running the code would show the sheet or remove protection.
 
Hi

with the protection, I don't know how to protect the sheet without password , as password option will make it complicated, I tried to hide the pivot sheet, didn't work. I tried to use this code in the SLAFinal macro. didn't work..

If Sheets("SLA").Range("A2") = "" Then

Sheets(“PIVOT&GRAPH”).Visible=xlsheetveryHidden

endif

If Sheets("SLA").Range("A1") = "SOB" Then

Sheets(“PIVOT&GRAPH”).Activate

endif
 
Hi

with the protection, I don't know how to protect the sheet without password , as password option will make it complicated, I tried to hide the pivot sheet, didn't work. I tried to use this code in the SLAFinal macro. didn't work..

If Sheets("SLA").Range("A2") = "" Then

Sheets(“PIVOT&GRAPH”).Visible=xlsheetveryHidden

endif

If Sheets("SLA").Range("A1") = "SOB" Then

Sheets(“PIVOT&GRAPH”).Activate

endif
Hi,

In the SLAFinal code, where you had:
Code:
If Sheets("SLA").Range("A2") = "" Then
    Exit Sub
  
  End If

it should read:
Code:
If Sheets("SLA").Range("A2") = "" Then
    Sheets("PIVOT&GRAPH").Visible = xlSheetVeryHidden
    Exit Sub
  
  End If

and to make it visible again, immediately before the "End sub" add:
Code:
Sheets("PIVOT&GRAPH").Visible = True
 
Hi My friend,
I have tried your step, it didn't hide the pivot table .
 

Attachments

  • Esc_SLA Data weekly trial.xlsm
    566.7 KB · Views: 2
Hi,

That is odd :confused:
Did you try it with no data?... as long as "A2" is empty it will hide the PT sheet.
It does here. Please double check.

Thanks
 
Hi

I am attaching the sheet for you, I have added the code and when the clear macro deletes the data and A2 is empty it still shows the PT sheet.

If Sheets("SLA").Range("A2") = "" Then
Sheets("PIVOT&GRAPH").Visible = xlSheetVeryHidden
Exit Sub

End If


Sheets("PIVOT&GRAPH").Visible = True
 

Attachments

  • Esc_SLA Data 21 Mar 2017 hidden pivot.xlsm
    840.4 KB · Views: 1
Oh, so obvious :)

You want to hide right after clearing the contents of the sheet... that bit of code should be part of the code to clear the sheet and not in the SLAFinal...
Also, since you are clearing the data, A2 will always be empty so you just need:
Code:
Range("A2:N2000").ClearContents
Sheets("PIVOT&GRAPH").Visible = xlSheetVeryHidden

You can delete this:
Code:
If Sheets("SLA").Range("A2") = "" Then
Sheets("PIVOT&GRAPH").Visible = xlSheetVeryHidden
Exit Sub
form the SLAFinal and just leave the following at the bottom:
Code:
Sheets("PIVOT&GRAPH").Visible = True
 
Fantastic my friend, it worked, thanks a lot for being patient and answer all my questions, you are legend...really appreciate your help...
 
Back
Top