Data Validation Control based on another Cell.

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,831
Office Version
  1. 365
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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
This question is not clear. Can you show us the data and the IF statement you are using?
 
Upvote 0
=if(h4="8500100","12-31-17","")
 
Upvote 0
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:
Upvote 0
So make a new column for the dates. This is called a "helper column". You can hide it if you want.
 
Upvote 0
This is a template any changes will prevent it to upload in Oracle.
 
Last edited:
Upvote 0
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.
 
Upvote 0
Is VBA an option?
You could make those changes to column M via VBA code, if that is an option for you.
 
Upvote 0
Joe,
Thank you. unfortunately VBA option is not available.
and as you know that copy and paste defeat the purpose of data validation.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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
Back
Top