I'm using the formula
=OFFSET(Sheet1!$I$2,,,COUNTIF(Sheet1!$I$2:$I$6,"?*"))
in the Name Manager to remove blanks from a data range (I need to remove blanks because I will be using these values in a log function).
When I2:I6 is filled with non-numerical values, it works fine, tested with data validation -> list/drop down menu on cell B11.
However, when I place numerical values into cells I2:I6, the named range freaks out. For every numerical value I have in I2:I6 range, it will remove the bottommost value from that range.
So suppose I have:
10
a
b
12
e
the named range will only return 10, a, and b, deleting 12 and e in I5:I6 because I have 2 numerical values.
Obviously, when the entire range is filled with numerical values, the named range will not return any values.
How can I get a named range with numerical values while removing blank cells?
Bookmarks