I am having trouble getting the following formula from cpearson's page to work:

IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",
INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

It removes blank cells from a list and has worked for me before. This time
I notice that when I type in a named range into the formula, it turns blue (I
assume indicating that Excel recognizes it as a variable name). But, only
one of the two named ranges turns blue, and I am getting a circular
reference error.

Insight into the blue color in relation to named ranges?

Thanks in advance,

Billy