data validation

  1. R

    VBA: Macro to delete range runs normally, gives error 1004 if I click on a cell with data validation

    This is my first post here, so sorry if I made any mistakes (this forum has helped me a lot before tho, thanks!). I'm trying to make a macro that will delete everything within a range of cells, but I'm running into a weird issue. There's a couple of checkboxes and a picture that don't get...
  2. A

    Data validation dependent

    Hi, I'm making a sheet to easily click together invoices/offers. Therefor i've got a data-sheet which has all products on it (per row), with also colums for each 'color' of this product, and the prices of these combinations. Description Yellow Blue Red Table $ 10,00 $ 15,00 $ 20,00...
  3. D

    Requiring a user to give a response with VBA and Data Validation

    I have a data validation set in 3 columns so that the answers will remain consistent, and I don’t want this to change. I want cell D to be required if cell B has an answer selected (which will always be “Yes” because of my data validation restriction). But I don’t want cell D to be required if...
  4. A

    Power Query Table Consolidation & Data Validation List Values

    I have a workbook with several sheets. I have a power query that consolidates the values from those disparate sheets into one consolidated view. On each of those sheets is a column "Pass / Fail", and the values of that cell are derived from a dropdown data validation list. When the query runs...
  5. H

    Allow Zero or a greater then a cell value in data validation in excel 2019

    Hi all, I have to enter data validation rule in a cell (i.e. Cell A1) that it allows value 0 (Zero) or number value greater then in cell B1. Data must be in decimal points. The whole idea is i want cell A1 value not less then cell B1. But it can be zero (Cell A1= 0 or greater then B1) help...
  6. 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...
  7. D

    How to verification on a calculator field

    Hello All, I do hope that someone can help me with this problem I am having... What I am trying to do is set a "Whole Number" verification on a calculator field..... The Calculation is on the "Number of Boxes" which is "Qty Ordered" / "Box Qty" So what I am looking to do is set a...
  8. T

    Multi Column Data Validation in Excel Table

    Hi Everyone, I have an Excel table. I am trying to perform a multi-column data validation. I am trying to use the COUNTIFS function like so: =COUNTIFS([Supplier],C2,[Supplier_Code],D2)<=1 The goal is to ensure that the data entered is not a duplicate. The combination of [Supplier] and...
  9. A

    Locking Data Validation List

    I was wondering is it possible to lockdown a data validation list. i.e Make a dropdown list available for cells A2:A100. Then protect the sheet so that A2:A100 can use the dropdown available but that list to be locked so no one can either take the data validation off or change the list...
  10. S

    Date formula

    Hi, I am hoping you could assist me please. I have two (2) queries I need some help with. The first query is that I need selected cells to auto populate based on the Nomination Date (K14). I need Outcome Date (K15) to auto populate based on the date populated in K14 and plus 28 days; I also...
  11. D

    Data Validation List - Default Value

    Hello, I have been trying to find a solution to creating a Data Validation List that shows a default value (the top item in the list). I found a solution on this board that sort of works. Is it Possible for a Drop Down Menu to have Default Selections? Post #3 seems to work....but only to a...
  12. E

    Dynamic Dropdown List Error

    Hi All, First time poster, long time reader. I have been having an issue with a formula I am inputting into the data validation widget to create a dynamic dependant dropdown error. The formula is as follows: =OFFSET(Setup[[#Headers],[Savings]],1,MATCH([@[Expense...
  13. L

    mm:ss formatting

    Does anyone have a solution to formatting mm:ss without the AM/PM after it. I'm creating a spreadsheet where I need to put time as an input, say for a race where something like 10:30 would be 10 minutes and 30 seconds. There are then some calculations done on the time which says if time is below...
  14. W

    Locking cells in a row based on another cell's content

    I am trying to figure out a way in VBA to lock cells in columns A:C if the corresponding cell for that row in column D reads "Complete". So If D2 reads "Complete", A2:C2 will be locked, if D3 reads "Complete" then A3:C3 will be locked, and so on. Similarly, if D2 reads "Open" or is blank, cells...
  15. C

    Data Validation CAPS and LIMIT

    Is there a way to set a Data Validation to CAPS and LIMIT meaning I'm using Excel, Data Validation, Custom, =AND(EXACT(A3,UPPER(A3)),ISTEXT(A3)) this makes everything CAP. But I also want to limit how many charters allowed in this area meaning 10 or less... so far I figured out how to do one...
  16. S

    Removed Blanks in Data Validation - Office 365

    Hi, As the title says I'd like to remove blanks in a data validation list, I've followed the article below but the way Office 365 works is after effectively changing the formula below it expands out the cell for the full list, this means when I reference the cell in name manager the cells are...
  17. X

    Can’t skip cells, no dupes, no formulas

    Hi, I have a list of 20 names in column B. Everyday I need to select 6-8 of these names and have them appear in col J without skipping cells, without duplicates and without using a formula in J. If cells get skipped it will break the formula on another sheet that is the purpose of the workbook...
  18. D

    Medical Procedure Database

    Please excuse any wrong terms I may use when attempting to ask my question - I'm new to the community :) My goal is to set up an excel spreadsheet that can serve as a database from where I can pull information, in any combination, to perform research, evaluate procedures, etc. Many of these...
  19. M

    Validation dependent lists

    Hello everyone, This is my first post here. I've been working with Excel for quite a few years now but now stumbled upon this challenge, for which I can't seem to find a solution. If it's even supported in Excel 2016. Please note I'm not using Office 365 so I can't use all function that Office...
  20. J


    Hi i am used to using data validation. Recently I was typing a formula and it seemed as it was too long due that I was unable to keep writting it. The following formula is : =IF(AND(A32="BIO ORIENTADO ";A35="BAJA");'BASE DE DATOS'!$AA$2:$AA$5;IF(AND(A32="BIO ORIENTADO ";A35="ALTA ");'BASE DE...

Watch MrExcel Video

This Week's Hot Topics

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