Problem creating dynamic named range

[sharky12345]

Can someone please show me how to convert this named range into a dynamic one?

I know it's probably fairly easy but everything I tried does not work - is it because the cells are merged?

=SETUP!$B$15:$C$24

 

[sharky12345]

OK, got this sort of working, but the cells have formulas in them and this is counting the blank cells, (those where the formula produces a blank result), if anyone can help?

=OFFSET(SETUP!$B$15,0,0,COUNTIF(SETUP!$B$15:$B$24,"<>"&""),1)

 

[Aladin Akyurek]

What kind of data do you have in column B and column C?

Are you wanting a dynamic named range for the whole or also for the two ranges A:B houses?

 

[sharky12345]

Morning!

It's a combination of text and numeric and not sure what you mean by the 2nd question.....

Each row is a merged cell of B:C, from B15 to C24. My 2nd post works so I guess that I could exclude the reference to column C?

 

[Aladin Akyurek]

Morning!

It's a combination of text and numeric and not sure what you mean by the 2nd question.....

Each row is a merged cell of B:C, from B15 to C24. My 2nd post works so I guess that I could exclude the reference to column C?

What do you mean by merged? Don't you have date in two columns?

 

[sharky12345]

The text in each row from row 15 to 24 is in a merged cell across columns B and C.

 

[Aladin Akyurek]

The text in each row from row 15 to 24 is in a merged cell across columns B and C.

Is it one set of values or two sets of values? If just one, why are you merging?

 

[sharky12345]

Just one - I think I can get away without merging if I need to

 

[Aladin Akyurek]

Just one - I think I can get away without merging if I need to

Ok. What follows is based on the assumption that you have formulas in the B range which returns blanks. And you don't want to include blanks that occur after the last non-blank data point in the dynamic named range...

Unmarge the data area and define Data (or anything more descriptive) in Formulas | Name Manager as referring to:

=OFFSET(SETUP!$B$15,0,0,MATCH(9.99999999999999E+307,SEARCH("?*",SETUP!$B:$B))-MIN(ROW(SETUP!$B$15))+1)

 

[sharky12345]

Thank you!