Named range formula

[global_zoo]

Hi guys,

I've been trying to sort out defined names for hours with little luck.

I have several columns filled with IF statements, pulling numeric values where they exist and returning the word "FALSE" where no data exists. There is a header row, and 99 rows of results in each column.

I want to make defined ranges I can then use in charts (SERIES formula) which select just the numeric values in each column (ie not the cells that return "FALSE"). I currently have the formula, =OFFSET('Middle Is'!$G$2,0,0,COUNT('Middle Is'!$G$1:$G$100),1)

This formula works when G2 is numeric, but not when it returns "FALSE" even though there are numeric results further down the column.

I'd be most appreciative of any help. Do I need a different formula? I'm stuck!

Cheers

[global_zoo]

PS I'm using Excel 2007

[MarvinP]

Hi global_zoo and welcome to the forum,

It looks like you are trying to do a Dynamic Named Range. See if http://www.ozgrid.com/Excel/DynamicRanges.htm doesn't help.

[global_zoo]

Thanks MarvinP.

I had been looking at the Ozgrid site... it gives the basic formula I'm using [=OFFSET($A$1,0,0,COUNT($A:$A),1)] but it doesn't work. I'm guessing it senses my IF statement as numeric, however, I pasted values and changed the FALSE cells to text with no result.

Can you include a statement within the formula to specifically tell it to exclude cells with "FALSE"?

[global_zoo]

I've worked out that it is something to do with the reference cell experession in the OFFSET formula. I think I need to change this reference to a nested reference that gives the cell that is the first numeric value in the column. Can INDEX do this?

[JBeaucaire]

You have a worksheet demonstrating the range you're trying to analyze and a mockup of the results you're trying to achieve?

[global_zoo]

You have a worksheet demonstrating the range you're trying to analyze and a mockup of the results you're trying to achieve?

I sure do, as attached.

In the file, you'll find the the named ranges I'm working on (ie _0Long, _0Lat, _1Long etc) which use the formula, =OFFSET(S_0Lat,0,0,COUNT('Middle Is'!$G$1:$G$100),1)

I believe I need to insert some statement to replaced the relative position of the range, currently defined manually by another range (S_0Lat, S_0Long etc).

Many thanks

[Marcol]

I don't see why Sheet1 is needed.

With Sheet2
In A2

Please Login or Register  to view this content.

Drag/Fill Down

In F2

Please Login or Register  to view this content.

Drag Across to Column U then down as required.

For the Dynamic Names
e.g. "_0Lat"
Refers to:=

Please Login or Register  to view this content.

Where 0 (red) is the "Bin Class" to be matched and counted.

Is this what you require?

[global_zoo]

Marcol - thank you! This is perfect!

Thanks for taking the time to help me out.

Cheers