Tag Archives: Data Validation

Dependent Dropdown menu using OFFSET in #Excel

Posted on December 16, 2023 by MF

Quite a long time ago, I wrote about two different approaches of preparing dependent dropdown menu in Excel. The first approach was for non Excel365 users, while the second approach was for Excel365 users. You may refer to the post … Continue reading →

Rate this:

Posted in Formula | Tagged , , , , , , , | Leave a comment

How to create a dependent dropdown list in #Excel

Posted on June 27, 2021 by MF

…with and without Dynamic Arrays (which is available to Microsoft 365 users only) Got a question from a friend recently: Is it possible to show a different set of questions in a dropdown list depending on the answer input from … Continue reading →

Rate this:

Posted in Excel in Action | Tagged , , , | 2 Comments

The power of Dynamic Arrays in #Excel 365

Posted on October 26, 2019 by MF

… in creating dynamic drop-down with conditions Making a dynamic drop-down list is not difficult.  It may require multiple steps, helper formula and/or helper tables though.  And the number of helper tables depends on the complexity of the requirement.  I … Continue reading →

Rate this:

Posted in Excel in Action, Excel Tips | Tagged , , , , | Leave a comment

Highlighting Top X values with Conditional Formatting in #Excel

Posted on December 15, 2018 by MF

Well… think this topic is too simple?  How about doing this with a dropdown menu to make the Top X a dynamic one? with Icon Set? You may download a Sample File to follow along. Let’s start with the basics … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , , , , | Leave a comment

Dynamic Shrinking Dropdown with Dynamic Arrays in #Excel 365

Posted on November 14, 2018 by MF

The recent HOT topics about Excel should be the new Dynamic Arrays!   After watching all the amazing demonstrations about the new Dynamic Arrays, I’ve decided to sign up to the Office Insider (Fast) programme (for Office 365 subscriber only).  … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , , , , | Leave a comment

Data Validation to restrict user from inputting to future days

Posted on September 22, 2017 by MF

The situation We need to input certain data on a daily basis.  For example, daily sales.  That is supposed to be an easy and routine task for many people.  BUT… it happened that we still have a chance to input … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , | 2 Comments

How to limit time interval input in a single cell?

Posted on December 19, 2015 by MF

Got the following question from a reader: Under data validation, is it possible for me to restrict the time duration (in a cell) to be 30mins or less? Example: 9.00 – 9.30 (accepted) 10.15 – 10.50 (rejected) Obviously, the answer … Continue reading →

Rate this:

Posted in Excel Tips, Formula | Tagged , | 4 Comments

Move the Input Message Box out of the way

Posted on January 17, 2015 by MF

Did you know that there is Input Message box which look like a comment box? The main difference is your don’t see any “indicator” on the cell.  The Input Message box pops up when the cell is selected.  To do … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

In-cell dropdown by Data Validation – Allow input NOT from a list

Posted on January 10, 2015 by MF

This is about a trick of using named range that includes a blank cell; which allows any input even the cell is with Data Validation that allows list. When you attempt to input anything other than what is restricted by Data … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , | 6 Comments

Allow time input at 15-min intervals only – Data Validation

Posted on August 16, 2014 by MF

How to limit user to input time at 15-min intervals, i.e. 00:15, 00:30, 00:45, 01:00, etc.? We talked about how to limit user to input Time in the previous post.  Let’s go one step further with Custom Data Validation, where you … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , , | Leave a comment

Data Validation – TIME

Posted on August 10, 2014 by MF

How to limit user to input TIME only? Sometime, actually too many times, users may not input cell content in a way you want them to.  As a result, the formula you set for calculation may go wrong; and you … Continue reading →

Rate this:

Posted in Excel Tips | Tagged | 1 Comment

Dynamic Dependent Dropdown by Data Validation

Posted on March 15, 2014 by MF

So many D. 😀 This is a continuation of the previous post: Not all teachers are eligible to all classes.  e.g. only Iris and David are eligible to deliver Class F, would it be feasible to have only Iris and … Continue reading →

Rate this:

Posted in Excel Tips | Tagged , , | Leave a comment

Dynamic Dropdown – Least hours assigned always on the top

Posted on March 7, 2014 by MF

How to use Data Validation to show a dynamic drop-down list to facilitate job allocation problem? I learned about using Data Validation long time ago.  It’s quite handy and useful when you want to restrict user to enter items only … Continue reading →

Rate this:

Posted in Excel Tips | Tagged | 2 Comments