Named Ranges & Row definitions

❓ Questions & help Worksheet design
1

Greetings,

I think I found another bug.
When I’m naming my Named Ranges that don’t start with the first row, it seems to be off by 1 count.
If you can see from my image, I have a Named Range that should start on Row 6 (under the Table Header Row), but when I name my range to use, it give me an error if I define it as N6. If I define it as N7, then it works, and it does give me the correct results.

image
image771×245 39.3 KB

Please let me know if you need any further information.
thanks again for all your hard work on this project!!!

2

@benjamin_gunter , request you to share more details regarding the error that you are seeing.

To be specific,

  • Is it giving error on defining Named-Range as ‘Full Roster’!N6:N
  • Or is it an evaluation error when using Named-Range in a formula expression of a cell Eg. SUM(FILTERED_COUNT)

In the latter case, we may need to see if the contents of cell N6 are of a correct type with respect to the formula-expression it is being used in i.e. using text literals for math function.

3
  1. Is it giving error on defining Named-Range as ‘Full Roster’!N6:N
    • This is what happens when I use N6 as the range:
      image

I’m attempting to calculate the SUM of a Filtered View, so the function in Column N is:
SUBTOTAL(103,K)

The then results above my Primary Header Row look like this:
image

4

The value of cell at K6 seems to be of incompatible type as a result N6 is evaluating as #VALUE.

This #VALUE error then cascades to other aggregate formula cells which reference this cell either directly or as part of a cell-range.

As a general practice we should always account for possible error results in a formula and have some error handling. In this case, request you to consider defining N column formula as
=IFERROR(SUBTOTAL(103,K),NULL) so that error result is consumed and cell value is set as empty. Also, such error results will now not be passed onto other formula cells referencing this cell.

5

I’m pulling my hair out trying to figure this out, because the Column is a Formula, so i can’t overstand why one cell would create the problem:

f051633b0d1792eb4e40390c17656f24602b1421
f051633b0d1792eb4e40390c17656f24602b1421920×392 74.9 KB

Column K is also a Single Select datatype, so again, I can’t overstand why that cell would produce an error.

I could make a copy of this Worksheet and give you access to it so you can see what I mean. Help me to figure out why I can’t clear, and reset this data type.
I even went so far as to delete that row, and I still get this error / result.

thanks in advance for your assistance.

6

Hi @benjamin_gunter

Just sent you an email so we can connect and share the proper workbook with our team. Please let me know if you don’t receive anything on your end.

Best,
Thomas Van Steyn

7

Thanks for sharing a copy of the workbook.

All the range arguments passed to COUNTIFS function should be of the same dimensions. Teams & Location named-range variables where defined over range C7:C and K7:K, hence on switching FILTERED_COUNT to N6:N it was breaking with #VALUE but evaluating correctly for N7:N.

Updating all named-range variables to start at row-index 6 fixed the issue.

4 Likes
8

@msiraj @Vimalkumar_Selvaraj - We have some work to do with runtime ( dynamic) error reporting in formulas… If that had been there, it may have helped Benjamin in figuring it out himself.
I will be adding this to the backlog.
See below :

image
image1063×642 36 KB

1 Like