Hi Domenic,

Thanks for ongoing help. As suggested, I tried recommended Formula:

=ISNUMBER(Cell Reference)

It returned the result TRUE.

Appreciate, if anything else comes to mind.

Thanks
Sam

--
Message posted via http://www.officekb.com

Can you confirm that the dynamic range 'Cost' refers to a single column
only?

In article ,
"Sam via OfficeKB.com" wrote:

Hi Domenic,

Thanks for ongoing help. As suggested, I tried recommended Formula:

=ISNUMBER(Cell Reference)

It returned the result TRUE.

Appreciate, if anything else comes to mind.

Thanks
Sam

Hi Domenic,

Dynamic Range Cost is a single column.

Unfortunately, I'm still getting to grips with how Dynamic Ranges work.

However, I think this may be the root of my problem -
This is the Formula in the Define Name Refers To Box:
=OFFSET(Stock!$R$70,1,0,COUNT(Stock!$R:$R)-1,1)

There is the column heading COST in Row 70 and the actual mumeric data
starts in Row 71.

There is a numeric value in column R above the start of the Named Range
Cost in cell R33 but gets included in the Dynamic Range stretching the full
length of the column using COUNT(Stock!$R:$R)-1,1).

Is it possible to still keep the Range Dynamic starting at Row 71 but
somehow restrict COUNT(Stock!$R:$R)-1,1)so that it starts from Row 71 and
not Row 1 including the entire length of the column.

Thanks
Sam

--
Message posted via http://www.officekb.com

Ah yes! There's your problem! Try defining your range using the
following formula instead...

=Stock!$R$71:INDEX(Stock!$R$71:$R$65536,MATCH(9.99 999999999999E+307,Stock
!$R$71:$R$65536))

Does this help?

In article ,
"Sam via OfficeKB.com" wrote:

Hi Domenic,

Dynamic Range Cost is a single column.

Unfortunately, I'm still getting to grips with how Dynamic Ranges work.

However, I think this may be the root of my problem -
This is the Formula in the Define Name Refers To Box:
=OFFSET(Stock!$R$70,1,0,COUNT(Stock!$R:$R)-1,1)

There is the column heading COST in Row 70 and the actual mumeric data
starts in Row 71.

There is a numeric value in column R above the start of the Named Range
Cost in cell R33 but gets included in the Dynamic Range stretching the full
length of the column using COUNT(Stock!$R:$R)-1,1).

Is it possible to still keep the Range Dynamic starting at Row 71 but
somehow restrict COUNT(Stock!$R:$R)-1,1)so that it starts from Row 71 and
not Row 1 including the entire length of the column.

Thanks
Sam

Hi Domenic,

That's Perfect:-

=Stock!$R$71:INDEX(Stock!$R$71:$R$65536,MATCH(9.99 999999999999E+307,Stock
!$R$71:$R$65536))

Thank you very much for all your time and patience. Most appreciated.

Thanks
Sam

--
Message posted via http://www.officekb.com