data validation (list)

  1. I

    Data Validation - clarification wanted

    Hi all, I'm simply looking for some clarity on how the Ignore Blanks option works within Data Validation. It was always my belief that unticking this would prevent the user from leaving the relevant cell blank. For instance, if the user deleted the contents of a validated cell but did not...
  2. TheWaterDog

    Defined Name as Cell Reference

    Forgive me if this has been answered somewhere; I've looked and can't seem to find a solution on the board. I'm using dependent dropdowns in Sheet 1 [Main] via an indirect() in Data Validation from named ranges in Sheet 2 [DVS]. So, you choose the "Parent_Choice1" in Cell A...and then... Cell...
  3. V

    How to exceed character limit for Data Validation List

    Hi all! I am running multiple offset functions with nested ifs to return a drop down list dependant on 3 variables (A,B,C). Is there a way I can avoid the character limit for data validation? I don't know how I can simplify this formula. Variabe A = Ability Level (0,1,2,3) (Purple) B3 Variable...
  4. J


    Experts, I have exhausted my search for a good solution that combines my three needs. Please help. I'm using a Data Validation List. I want to be able to do three things: 1) Start typing just ONE letter in a cell and the drop down window opens for me to make a choice. For example: Type...
  5. E

    Data Validation Dropdown OR manual entry specific text

    Hello I have the following Data Validation formula that creates a dependent list dropdown. It's complicated because it's designed to remove blank spaces. =OFFSET(Backup!$AK$1,1,MATCH($C$9,Backup!$AK$1:$EC$1,0)-1,COUNTIF(OFFSET(Backup!$AK$1,1,MATCH($C$9,Backup!$AK$1:$EC$1,0)-1,100,1),"?*"),1)...
  6. D

    Create hyperlink using data validation text as the "friendly name"

    Hi team, I am looking to have a column of hyperlinks (in say Row B), and have Column A be the cells that have the firendly name, which will be pulled from a list on a different sheet. EG: Cell A1=Data validation value. of "In Progress" and "Done" (The text here is not important, I...
  7. L

    Moving down a data validation list(drop down)one by one and save as a new workbook

    Hi All, I have created a drop down list like below. How can I move down the list one by one and then save it as a new tab in a new workbook? I tried recording moving the cursor moving from Name 1 to Name2,all the code shows is just Here is what I am trying to do: 1. Select Name1 it...
  8. life_in_picture_format

    VBA Code to Hide/Unhide Separate Named Tables Based on Drop Down Menu

    For this example we have 3 agents (Agent 1, Agent 2, Agent 3) listed drop-down menu. When their name is selected from the drop-down the monthly table (the green one) will auto populate their info. Below this there are 3 additional tables: One for each Agent showing their stats month by month. I...
  9. J

    New behavior with Data Validation Drop Down lists in Insider Edition!

    The latest version of the Insider Edition of Excel (Version 2202 Build 16.0.14907.20000 64-bit) has a new behavior for Drop Down lists that I suspect people will LOVE! One can now start typing in the cell with the List, and a Tool Tip style entry from the list that matches what is typed will...
  10. R

    VBA Help for Hiding rows from a data validation list choice.

    Running Excel 365. Windows 10 machine. So I think I have a few issues to sort out, I am working on a spreadsheet that will change the rows available to show (fill in) by 2 variables. I'm creating an Address Matrix for a programming file on another type of system and there are 2 versions of...
  11. O

    Look up a table, check availability then create a drop down list.

    Hi Everyone, I have some tables that show information and availability of products. e.g., List of colours then columns that show what finishes they are available in. (Pic 01) Firstly I am not sure if I am doing this in the best practice, but this is what I have so far. I then have another table...
  12. R

    Dynamic Table References for Data Validation Lists

    I've been tinkering for a while, but just can't seem to get this to work as desired. I have multiple tables in a workbook: TblFuel TblVehicles TblPlant_Machinery TblMaterials TblLabour I'm creating a quote page where the initial selection is for one of these item types(Fuel, Vehicle...
  13. R

    IFERROR in Data Validation Formula

    Hi there, I would like to provide a dropdown list based on product categories for specific customers. Each customer has its own table, and if a customer doesn't have a specific table set up, formulas in the worksheet refer to a table "StandardTable". I've tested the following formulas in Data...
  14. P

    Automated data validation

    Hi Everyone I am trying to make a drop down list automatically update based on a set cell value. what I have is the following: > 30 cells with dropdown lists > work schedule of fortnightly, monthly, quarterly and annually > 10 different sites > Dependent of the schedule being completed...
  15. L

    Array Formula in Data Validation

    I have a difficult project i cant seem to figure out. My goal is to make an order form for people to fill out based on a bid i put out for vendors to bid on clothing. They bid on the price of the shirt or short and then they also gave the prices for a 1 color imprint, 2 color imprint.... My...
  16. N

    Can Excel be a front end to the data base ?

    Hey experts In our org, we have a big problem people enter all kinds of data wrong like name with spaces, name in short and ID wrong Until we figure out the solution, we are non profit, I am asked to come with a immediate solution and long term which I suggested is front end ( another story, I...
  17. B

    Limit in excel data validation source code

    I have an excel file with two sheets. The second sheet (Report) contains data validation cells based on the first sheet (Data). From the second sheet, the drop-down list that displays in the Select XXX depends on the selection in the Generate Report. When the Generate Report is set to anything...
  18. M

    How to write a macro that will open, copy and paste data from a separate workbook

    Hello! I have one tab in my workbook that contains all of the 'lookup data' I use in the rest of my model. It is mainly used for data validation lists. Occasionally new line items will need to be added to these lists. Eventually I will have three different models for 3 different teams but they...
  19. C

    Data validation formula to return list of orders based on vendor

    Good day, I am trying to setup a formula-driven data validation list that will give me the option to select order numbers based on the vendor. The problem I have is that the maximum limit of character-input for a formula is 256 characters, this creates a big problem in my sample, as I am using...
  20. S

    Highlight Cells Outside of Data Validation List

    Hi! I have a spreadsheet with a Data Validation column referencing a range on a separate sheet. What I'd like to happen is to highlight any cells not meeting that criteria. I know it throws the Data Validation error, but I'm having trouble getting people to understand I can't work with data...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back