How to do Dynamic Dropdown without blanks in Excel step by step guide

As I am revising my excel template for Retail Inventory, I realized there might be some people out there needing help or guide on how to use a dynamic dropdown, but do not want to include the blanks.

Sample Problem Description 1:

I want to make a dropdown list in excel using Setup!$C$15:$C$44 range but do not include the blanks

Solution 1:
=OFFSET(Setup!$C$15,0,0,COUNTA(Setup!$C$15:$C$44))

This formula uses the OFFSET function to select the range starting from Setup!$C$15, with a height equal to the count of non-blank cells in the range Setup!$C$15:$C$44.

Here's the step by step guide on how to make a dynamic dropdown:

Step 2. Click on the "Data Validation" button in the "Data" tab of the ribbon.

Step 4. In the "Source" field, enter the range Setup!$C$15:$C$44.

Your dropdown list should now only show non-blank values from the Setup!$C$15:$C$44 range.

Sample Problem Description 2:

I want to make a dropdown list in excel using the range named "ProductDescription" but do not include the blanks.

more details:
ProductDescription is a named range that refers to 'Item Details'!$C$4:$C$103

Solution 2:
=OFFSET('Item Details'!$C$4,0,0,COUNTA(ProductDescription))

You can follow the same instructions written above, except for the step 5 where you will put this solution 2 instead.

Still need help with Excel and Google Sheets?

Get Instant Expert Help in Fiverr or PeoplePerHour platforms. Click one icon below.

We can also accept LEO, HIVE, HBD tokens as payment if we transact directly here in HIVE network.

This post is designated as a reviewer for Excel trainings I conduct.

Feel free to share with anyone. 😉

Earnings from Upvotes on this post are used to continue sharing Excel knowledge.
If you also want to earn HIVE in 7 days while blogging your favorite topics, join us here at Peakd.