Forum Discussion
discontinuous selection
markusbohu The circular reference warning comes up because the simple COUNTIF formula that would calculate pay-out, sits within the range that the formula is addressing. But, in this case the pay-out will never affect the outcome of the COUNTIF, so you need to tell Excel that it should use iterative calculation. You'll find it under Excel, Preferences..., Formulas and Lists, Calculation. Tick the box and leave the other settings as they are. Now everything will work as you want it to. Have tested it with the attached workbook. Note that the COUNTIF can be written so that it looks at the entire row. That way, you can add people without ever having to expand the range.
markusbohu The circular reference warning comes up because the simple COUNTIF formula that would calculate pay-out, sits within the range that the formula is addressing. But, in this case the pay-out will never affect the outcome of the COUNTIF, so you need to tell Excel that it should use iterative calculation. You'll find it under Excel, Preferences..., Formulas and Lists, Calculation. Tick the box and leave the other settings as they are. Now everything will work as you want it to. Have tested it with the attached workbook. Note that the COUNTIF can be written so that it looks at the entire row. That way, you can add people without ever having to expand the range.
Riny_van_Eekelen Thank you. That is a much simpler solution than the one I came up with. The only disadvantage is that it depends on a global setting, which does not save with the excel file itself, so I wonder what happens when the file gets opened on a computer where this setting has not been adjusted.
Yesterday I came up with a MUCH more complicated solution:
I simplified the countif formula to: =SUM(COUNTIF(INDIRECT(BC3:BY3),"x"))
I put ROW($A3) in BB3, put numbers that increase by 2 in line 2 (starting with the ascii code of the letter of the first column with "x"and in the BC3:BY3 range I put in something like: =CHAR(BL$2)&$BB3 (and ="A"&CHAR(BM$2)&$BB3)
Then I hid the entire BB to BY column range and protected the sheet.
It works, but does not have the additional advantage of being able to easily add more employees, so I had to make sure I had plenty of columns available for them. My intention is to then hide the columns for each inactive or terminated employee, so they don't have to scroll.
