Refreshing Issues
Setup I am using, Form to Data Sheet to KPI Sheet to Dashboard.
Data is entered into Form, then it populates the sheet correctly. I have the entries separated by 1/4 of day, with them being called Q1, Q2, Q3, Q4. This is done with some formulas on the sheet that breakdown the entry timestamp. The Qx part updates/refreshes correctly on sheet. The KPI sheet is where I am having issues. I am fairly sure the formulas are correct here. I have it separating entries by Qx, using a max date reference. Example of issue. Q1 has 10 entries, Q2 has 10 entries, we are currently in Q3 and the first entry is made. After the auto refresh, KPI sheet only will show Q1 has 20 entries and Q2 has 1 and Q3 has 0. The Data Sheet always shows the correct Qx in the columns. I can either make another entry and/or open the Data form then press save on the Data form, then the KPI sheet will update to correct entry. Q1 has 10, Q2 has 10, Q3 has 1. It does this every time. This is totally baffling myself along with another user.
Answers
-
Hi Jason,
Could you post what formula you're using? Both for the cross-sheet calculation and also from within the sheet to create the Q1, Q2, etc? I'd like to test this and see if I get the same result.
Thanks!
Genevieve
-
In the KPI Sheet.
COUNTIFS({IMM#1 Production Tracking Range 1}, ="Front", {IMM#1 Production Tracking Range 23}, [Max Date]4, {IMM#1 Production Tracking Range 6}, CONTAINS("Q1", @cell), {IMM#1 Production Tracking Range 7}, "Weight Check")
=COUNTIFS({IMM#1 Production Tracking Range 1}, ="Front", {IMM#1 Production Tracking Range 23}, [Max Date]4, {IMM#1 Production Tracking Range 6}, CONTAINS("Q2", @cell), {IMM#1 Production Tracking Range 7}, "Weight Check")
So on all the way to Q8B. W ddi Q8A/B because of possible carryover past Midnight, so it does not show in Q1 the next morning. This is working fine also.
Data Sheet, this all is spot on everytime. the KPI is where it bonces around. Always going back to Prior "Q" then back to correct "Q" then back and forth until you reach the next "Q", then they stay set.
To get Q1......Q8B
=IF(AND([AM/PM Tracker]5 = "AM", [Value of Hour in Numerical]5 < 3), "Q8B", IF(AND([AM/PM Tracker]5 = "AM", [Value of Hour in Numerical]5 = 12), "Q8B", IF(AND([AM/PM Tracker]5 = "AM", [Value of Hour in Numerical]5 < 8), "Q1", IF(AND([AM/PM Tracker]5 = "AM", [Value of Hour in Numerical]5 < 10), "Q2", IF(AND([AM/PM Tracker]5 = "AM", [Value of Hour in Numerical]5 <= 11), "Q3", IF(AND([AM/PM Tracker]5 = "PM", [Value of Hour in Numerical]5 = 12, [Value on Minute in Numerical]5 < 30), "Q3", IF(AND([AM/PM Tracker]5 = "PM", [Value of Hour in Numerical]5 <= 3), "Q4", IF(AND([AM/PM Tracker]5 = "PM", [Value of Hour in Numerical]5 = 12), "Q4", IF(AND([AM/PM Tracker]5 = "PM", [Value of Hour in Numerical]5 < 6), "Q5", IF(AND([AM/PM Tracker]5 = "PM", [Value of Hour in Numerical]5 < 8), "Q6", IF(AND([AM/PM Tracker]5 = "PM", [Value of Hour in Numerical]5 < 10), "Q7", IF(AND([AM/PM Tracker]5 = "PM", [Value on Minute in Numerical]5 < 30, [Value of Hour in Numerical]5 = 10), "Q7", IF(AND([AM/PM Tracker]5 = "PM", [Value of Hour in Numerical]5 >= 10), "Q8A", "ERROR")))))))))))))
-
I can't see what may be causing this right off the bat. I'm wondering if it might have to do with the CONTAINS function looking for such a small value (although this shouldn't be the cause).
Based on your formula in the data sheet, it looks like there is only one output per criteria. Is there a reason you're using CONTAINS? Would you mind trying to look for an exact match instead, to see if that will help?
Ex:
=COUNTIFS({IMM#1 Production Tracking Range 1}, "Front", {IMM#1 Production Tracking Range 23}, [Max Date]4, {IMM#1 Production Tracking Range 6}, "Q1", {IMM#1 Production Tracking Range 7}, "Weight Check")
I will say it sounds like there may be something going on with the time versus the values, but you noted that the Data sheet always has the correct Q output from this formula so therefore the other cross-sheet COUNTIFS should be working as expected.
Let me know what happens with the above formula. The next step would be to check out that Max Date column and the date range to see if there's something going on there.
-
Changed the formula how you suggested still doing the exact same thing. I also tried 1-9 instead of Q1-Q8 to make sure that was not throwing it off. Yes the data sheet is always correct.
-
Hi Jason,
That is very strange. At this point I would suggest reaching out to Smartsheet Support so they can troubleshoot with you directly - if you're able to provide a screen recording of the issue that would be ideal, along with the sheet names & the tests you've performed. You may also want to link to this Community post so the Support team can review your formulas posted here.
Thanks!
Genevieve
