Dynamic Name Range

[MichaelJ300]

Hello,

I'm trying to understand why my calculation is not working on the text.

Any help is appreciated!

=offset('NIQ CALCS (2)'!$D13:$D$23,,,COUNTA('NIQ CALC'!$D$13:$D$23))

 

[MichaelJ300]

What I'm noticing with this formula... I'm using CountA because of the variables. The cells have formulas in them to bring back a sport name. For some reason it still recognizes the empty cells because of the CountA formula. If I were to use numbers I wouldn't have this problem since I would only use the Count formula.

I Need to figure out a way to have the range only see cells with values in them. Thoughts?

 

[Fluff]

Try

=offset('NIQ CALCS (2)'!$D13:$D$23,,,COUNTifs('NIQ CALC'!$D$13:$D$23,"?*"))

 

[MichaelJ300]

=offset('NIQ CALCS (2)'!$D13:$D$23,,,COUNTifs('NIQ CALC'!$D$13:$D$23,"?*"))

Thank you for the try. Sill did not work

 

[Fluff]

In your 1st image you showed "blank" cells, but in the 2nd image you showed cells with 0, so which is it?

 

[MichaelJ300]

In your 1st image you showed "blank" cells, but in the 2nd image you showed cells with 0, so which is it?

Agree, it happens with both.

 

[Fluff]

What is the formula in those cells as it looks like a spill range?

 

[MichaelJ300]

What is the formula in those cells as it looks like a spill range?

First, let me say thank you for helping on this!

No, it just brings back the range with blanks with formulas. Now if I hard code them without a formula it works. Otherwise it recognizes blanks as the range.

What I'm trying to do is make this dynamic for chart purposes. On the first image you can see that the offset counta function is brining in all rows because I have formulas (which I don't understand). Where the 2nd is bringing in the correct amount of rows. Where I don't have formulas. I hope this makes more sense.

 

[Fluff]

Thanks for that, with that formula in the cells the formula I showed in post#3 should return exactly what you want.

 

[MichaelJ300]

Thanks for that, with that formula in the cells the formula I showed in post#3 should return exactly what you want.

I'm not sure what I was missing but this formula but it is working. So I have two more questions. A) What does the ("?*") do? B) Although the formula is working I'm getting an error. I'm placing it in Name Manger and trying to use the name for my range. Any idea why? Below is an image.