Data Validation Control based on another Cell.

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,824
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi all,

I copy and paste dates in column, “M”, I want a data validation that if a corresponding cell in column, “H” house, “8500100”, the date should be 12-31-2017, otherwise leave it as is, whatever is copied.

Please advise.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

HackSlash

Active Member
Joined
Nov 18, 2016
Messages
360
This question is not clear. Can you show us the data and the IF statement you are using?
 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,824
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
=if(h4="8500100","12-31-17","")
 

HackSlash

Active Member
Joined
Nov 18, 2016
Messages
360
That says "if H4 is not the number then cell is blank". That second part of the equation needs to point to the dates in the M column:

Code:
=IF(H4="8500100","12-31-17",M4)

Now it will show your hardcoded date or the date from the M column.
 
Last edited:

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,824
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

The validation is in column M
 

HackSlash

Active Member
Joined
Nov 18, 2016
Messages
360
So make a new column for the dates. This is called a "helper column". You can hide it if you want.
 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,824
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

This is a template any changes will prevent it to upload in Oracle.
 
Last edited:

HackSlash

Active Member
Joined
Nov 18, 2016
Messages
360
So delete the helper column before you upload.

Your question is "How do I move a rug while I am standing on it?" Get off the rug. Move it. Then you can stand on it again.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,142
Office Version
  1. 365
Platform
  1. Windows
Is VBA an option?
You could make those changes to column M via VBA code, if that is an option for you.
 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,824
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Joe,
Thank you. unfortunately VBA option is not available.
and as you know that copy and paste defeat the purpose of data validation.
 

Forum statistics

Threads
1,148,294
Messages
5,745,944
Members
423,985
Latest member
sayed manzar

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 MrExcel.com.
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 "mrexcel.com".
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
Top