0

why does this report a circular reference in the last 3 columns I use?

=COUNTIF(INDIRECT(CHAR(COLUMN()+64)&$B$51&":"&CHAR(COLUMN()+64)&$B$52),"2")

$B$51 and $B$52 are constants I enter, so it only counts a limited number of rows

The more confusing issue is that the circular reference keeps changing, which cell is wrong

This circular reference error appears on every worksheet this formula is on, except in a different column

Improve this question
5
  • Instead of 64 use 63, presently its referencing its own column when you use the COLUMN() function Commented Oct 16 at 0:49
  • 1
    If you have Office 365 you could avoid volitile INDIRECT by using a combination of INDEX and TAKE. Commented Oct 16 at 5:20
  • Yes, your formula is a circular reference. what are you trying to do? it seems Excel identifies that you are using a reference to the same column, so when you use in addition a pointer (B51, B52) it raises a flag. notice, when you use indirect with pointer cell it will require to check for circular references every time you make a change. and it might slow down the file preformence. Commented Oct 31 at 12:18
  • which cells does function apply? Commented Nov 24 at 23:43
  • @Bhattacharya - referencing own column not an issue - its referencing its own cell - no? Commented Nov 24 at 23:48
Related questions
0
Countif formula with multiple criteria
0
Calculation order when iterative calculation (circular reference) is enabled
0
Excel - Problem with circular reference using offset formula
Load 5 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.