Excel Named range using *? in order to count only relevant cells

Question

Would like to use named range in order to find all relevant values in a range. However after several differrent approaches it seems that I cant't progress from here. Using *? in countif formula in order to get all text or values in the range, however it does not count the last cell with a text in the cell for some reason

=OFFSET(DATAMATCH!$I$7;0;0;COUNTIF(DATAMATCH!$I:$I;"*?");1)

OFFSET would be volatile, try: =DATAMATCH!$I$7:INDEX(DATAMATCH!$I:$I;COUNTA(DATAMATCH!$I:$I)+7), I plus 7 because of your header. Allthough I'm unsure what your goal is. Do you want the range to include the "-" values or not? — JvdV
The dynamic range should only include numbers, inother word, no empty cells or formulas which show empty cells or other charecters which is not a value or text. The prupose of all this is to chnage this dynamic range by a combo box and then in the end fill a graph with names and values — Martinswe
So I assume column H is the list of names Karlsson Jan .... Donoso Roberto and you want to get a range that is of the same size but from column I. Is this what you are trying? Are the cells in column H below Donoso Roberto actually empty? — JvdV
no there is formula down to row 35 and then empty cells. However the dynamic range should only extend to the last row with text or values, no formula. I think you formula is counting empty cell with formula in it. — Martinswe
If you have a spare column, you could try =COUNTIF(datamatch!I7,"*?") starting in row 7 and pulled down, to see which rows are selected. — Tom Sharpe

JvdV JvdV · Accepted Answer · 2019-07-01T08:00:21

An assumption:

Your range in column H are all these names
Column I range needs to have the same size as column H
The tricky part is that there are empty cells by formula in column H

So you could try the following (in my test a named range called TestRange2):

=DATAMATCH!$I$7:INDEX(DATAMATCH!$I:$I,MIN(IF(DATAMATCH!$H$7:$H$50="",ROW(DATAMATCH!$H$7:$H$50)))-1)

This way it's also non-volatile (or rather; semi-volatile), which using OFFSET wouldn't be.

Excel Named range using *? in order to count only relevant cells

2 Answers