• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Named Ranges

E

Ellasheba

Member
Hi - I posted yesterday regarding named ranges and was kindly informed of index or offset to make a named range dynamic.
However I cant seem to grasp how to make these work within other functions.
I have attached a sheet and am trying to create a dynamic range (that will adjust when rows are added - like a table would do) BUT I cannot seem to make it work with a function I have been using to return the unique number count of a column. I have attached a 'dummy' sheet...
I cannot use a table as the excel workbook keeps getting shared through the Excel share option - which does not support a lot of pre-existing functions in my previous sheet.... Help appreciated please !
 

Attachments

  • Dynamic ranges.xlsx
    10.7 KB · Views: 6
In the attached a dynamic named range called myRng.
A formula in Result sheet cell B1
Note that the dynamic named range uses COUNTA in its formula which counts non-blank cells; if there are blank cells interspersed among non-blank cells in column A of the Data sheeet, the expected range will be short by the number of blank cells therein.
 

Attachments

  • Chandoo45665Dynamic ranges.xlsx
    11.3 KB · Views: 14
p45cal said:
In the attached a dynamic named range called myRng.
A formula in Result sheet cell B1
Note that the dynamic named range uses COUNTA in its formula which counts non-blank cells; if there are blank cells interspersed among non-blank cells in column A of the Data sheeet, the expected range will be short by the number of blank cells therein.
Click to expand...
When I downloaded and opened it it showed the correct count (51) but when I then enable editing (as it displays protected view, that number changes to 1 (1)... Not sure what I have done wrong here????
 
Which XL version are you using?
I think p45cal is using 365 or a recent version with the UNIQUE function
 
If you haven't got UNIQUE available to you then if there are no blank cells the formula:
=SUMPRODUCT((1/COUNTIF(myRng,myRng)))
should be OK.
If there are blanks that will return an error, which can be circumvented with:
=SUMPRODUCT((myRng<>"")/COUNTIF(myRng,myRng&""))

Note that if there are blanks, then it's very likely myRng will be the wrong range and won't extend down far enough. You can check visually by pressing F5 on the keyboard and typing in myRng and click OK which will select myRng.
73190
 
Last edited:
You can also create a Named Range with =($A$2:INDEX($A:$A,MATCH(9.9999E+307,$A:$A))) which will extend down to the last number in the column even with blanks in the range ( and providing there is no other data)
(OFFSET is a volatile function, although in this case it wouldn't make a big deal)
 
You must log in or register to reply here.
Back
Top