Named Ranges not selecting correct data

[SavGDK]

Hello,

I am currently using this formula in my named ranges =IFERROR(OFFSET('Misc Calculations'!$T$9,0,0,COUNTA('Misc Calculations'!$T:$T)-1),"")

I thought this would help with my graphs so it would only pickup up the numbers in the columns and exclude everything else, primarily I get a #N/A in the column if the size changes. The server I am pulling the data from sets this up in an array, not sure if that matters or not.

Does anybody have a suggestion of how to update this so it only pulls in the numeric values?

Thank you,

dan

 

[XOR LX]

Hi,

Remove the IFERROR.

Regards

 

[SavGDK]

Hello,

Thank you for the reply, unfortunately when I remove the IFERROR it still picks up everything in the column and does not stop at numerical values.

Thank you,

dan

 

[XOR LX]

Are the numerical values in contiguous cells?

Can you give a reduced example, e.g. 10 rows's worth, of the sort of data you have in that column, plus your expected result?

Regards

 

[SavGDK]

Hopefully this will work,

The columns with the data can change length based on a user selection. When the length changes the server automatically generates the #N/A. I am trying to make sure the named ranges ignore anything that is not numeric so that when I generate graphs from the named ranges the graphs don't have large blank spaces from all the #N/A at the end of the columns. If I try to include a copy of the spreadsheet none of the data will show up because it is from a database.


[TABLE="width: 101"]
<tbody>[TR]
[TD="align: right"]1486.86[/TD]
[/TR]
[TR]
[TD="align: right"]1492.15[/TD]
[/TR]
[TR]
[TD="align: right"]1473.74[/TD]
[/TR]
[TR]
[TD="align: right"]1458.79[/TD]
[/TR]
[TR]
[TD="align: right"]1466.16[/TD]
[/TR]
[TR]
[TD="align: right"]1467.68[/TD]
[/TR]
[TR]
[TD="align: right"]1461.41[/TD]
[/TR]
[TR]
[TD="align: right"]1526.1[/TD]
[/TR]
[TR]
[TD="align: right"]1444.54[/TD]
[/TR]
[TR]
[TD="align: right"]1424.47[/TD]
[/TR]
[TR]
[TD="align: right"]1423.15[/TD]
[/TR]
[TR]
[TD="align: right"]1422.97[/TD]
[/TR]
[TR]
[TD="align: right"]1435.67[/TD]
[/TR]
[TR]
[TD="align: right"]1437.09[/TD]
[/TR]
[TR]
[TD="align: right"]1432.96[/TD]
[/TR]
[TR]
[TD="align: right"]1440.9[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A

thank you dan[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[XOR LX]

Try defining it rather as:

='Misc Calculations'!$T$9:INDEX('Misc Calculations'!$T:$T,MATCH(9.9E+307,'Misc Calculations'!$T:$T))

Regards

 

[SavGDK]

Thank you for the help. This works!!

 

[XOR LX]

You're welcome!

Cheers