Hey guys, couldn't find anything on my specific issue but I may have not been searching correctly.
I've created a pricing workbook with the following tabs-
Tab 1 is for Input. Tab 2 is the Prices Tab that essentially is customer facing.
On the Input tab for some items, I have check boxes for certain services. When checked, that service will be unhid on the Prices Tab and the price will also be in that line item. If unchecked, the service will be hidden and the price will be set to zero. Items and their prices are grouped by things like Monthly, One Time, etc and then it just does a sum of the group regardless if the items are hidden or not. So total Monthly costs, etc.
So I had a bump in the road the other day where I had checked a service in the Monthly section. The row for whatever reason did not unhide however it did input the correct price. So while the total Monthly cost was technically correct, there was one item that remained hidden that should've been unhid. See example below-
Monthly Item Service #1 $2,000
Monthly Item Service #3 $1,000
Total Monthly Service Costs $4,000
Monthly item service #2 of $1,000 should have appeared but didn't although it's price was included in the Total.
I'm trying to figure out a way to implement a way to double check each group so I avoid this error. My thought process was to maybe create a formula or macro that totaled only the un-hidden items for each group and compare that total to what the Prices Tab is showing for each group total. So in the example above, the logic would be to total Monthly Service Item #1 and #3 with a total of $3,000. Then compare that to the Total Monthly Service costs of $4,000. Where this would obviously show there's an anomaly somewhere.
I'm open to ideas though. Thoughts?
I've created a pricing workbook with the following tabs-
Tab 1 is for Input. Tab 2 is the Prices Tab that essentially is customer facing.
On the Input tab for some items, I have check boxes for certain services. When checked, that service will be unhid on the Prices Tab and the price will also be in that line item. If unchecked, the service will be hidden and the price will be set to zero. Items and their prices are grouped by things like Monthly, One Time, etc and then it just does a sum of the group regardless if the items are hidden or not. So total Monthly costs, etc.
So I had a bump in the road the other day where I had checked a service in the Monthly section. The row for whatever reason did not unhide however it did input the correct price. So while the total Monthly cost was technically correct, there was one item that remained hidden that should've been unhid. See example below-
Monthly Item Service #1 $2,000
Monthly Item Service #3 $1,000
Total Monthly Service Costs $4,000
Monthly item service #2 of $1,000 should have appeared but didn't although it's price was included in the Total.
I'm trying to figure out a way to implement a way to double check each group so I avoid this error. My thought process was to maybe create a formula or macro that totaled only the un-hidden items for each group and compare that total to what the Prices Tab is showing for each group total. So in the example above, the logic would be to total Monthly Service Item #1 and #3 with a total of $3,000. Then compare that to the Total Monthly Service costs of $4,000. Where this would obviously show there's an anomaly somewhere.
I'm open to ideas though. Thoughts?