I have a complicated spread sheet I am working on.
I've done well with it except I need some assistance on the conditional formatting.
Based on the contract provisions what gets billed is based on time. There is a long list of services typically not billed together. If they are, a modifier code is added to explain why.All services provided must be listed even if not billed since time is important as is tracking what was done
So all the services provided are entered it sums at the bottom and indicates how much can be billed based on the times that were put in by the person then completes indicating how they wish to bill.
The formatting is set so if two services typically not billed together are, the modifier box will turn orange indicating a modifier reason code must be chosen.
the boxes turning orange when needed works. However, if they chose not to bill one of the services that is in the list they no longer need the modifier.
I need the orange box to go away if that other service is put as a 0 so we don't put modifiers that are not needed.
They could appear anywhere in the list of services so I can't simply say if the box below is 0 don't turn orange.
[/IMG]
Above is the sample. 2 units are billed for service A and 0 for the other 2 since very little time was spent doing those services. It is up to the person who does the form how they want to assign the units depending on how many are allowed.
conditional format formulas:
the formula for the cell to turn orange
=AND($AT43<>"No",$B43="",OR($K<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">43>0,$K43="")) applies to: =$B$43:$G$52
to turn grey
Grey for modifier =OR(AND($AT43="No",$H43<>""),A<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">ND($K43=0,$B43="",$A43<>"")) applies to =$B$43:$G$52
if needed this the the lookup formula referenced as cell AT
AT formula: =IF(ISERROR(MATCH(T50,$AS$13:$<wbr>AS$348,FALSE)),"No",MATCH(T50,<wbr>$AS$13:$AS$348,FALSE))
The table for the lookup is large and fairly complicated .
How do I get if they choose 0 for one of the paired codes it goes back grey? in this example service W and service A are considered pairs not typically billed. If they are, service A needs a modifier reason code. Since they are choosing not to bill W, since it was only a few minutes, a modifier is not needed ---but the box is still orange. The user might still put the modifier in since the sheet says any red or orange cells need to be addressed before going to the next date. Putting a NA in the drop down isn't going to help as it still stays orange
any help is appreciated
Thank you!
I've done well with it except I need some assistance on the conditional formatting.
Based on the contract provisions what gets billed is based on time. There is a long list of services typically not billed together. If they are, a modifier code is added to explain why.All services provided must be listed even if not billed since time is important as is tracking what was done
So all the services provided are entered it sums at the bottom and indicates how much can be billed based on the times that were put in by the person then completes indicating how they wish to bill.
The formatting is set so if two services typically not billed together are, the modifier box will turn orange indicating a modifier reason code must be chosen.
the boxes turning orange when needed works. However, if they chose not to bill one of the services that is in the list they no longer need the modifier.
I need the orange box to go away if that other service is put as a 0 so we don't put modifiers that are not needed.
They could appear anywhere in the list of services so I can't simply say if the box below is 0 don't turn orange.
Above is the sample. 2 units are billed for service A and 0 for the other 2 since very little time was spent doing those services. It is up to the person who does the form how they want to assign the units depending on how many are allowed.
conditional format formulas:
the formula for the cell to turn orange
=AND($AT43<>"No",$B43="",OR($K<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">43>0,$K43="")) applies to: =$B$43:$G$52
to turn grey
Grey for modifier =OR(AND($AT43="No",$H43<>""),A<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">ND($K43=0,$B43="",$A43<>"")) applies to =$B$43:$G$52
if needed this the the lookup formula referenced as cell AT
AT formula: =IF(ISERROR(MATCH(T50,$AS$13:$<wbr>AS$348,FALSE)),"No",MATCH(T50,<wbr>$AS$13:$AS$348,FALSE))
The table for the lookup is large and fairly complicated .
How do I get if they choose 0 for one of the paired codes it goes back grey? in this example service W and service A are considered pairs not typically billed. If they are, service A needs a modifier reason code. Since they are choosing not to bill W, since it was only a few minutes, a modifier is not needed ---but the box is still orange. The user might still put the modifier in since the sheet says any red or orange cells need to be addressed before going to the next date. Putting a NA in the drop down isn't going to help as it still stays orange
any help is appreciated
Thank you!
