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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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,561
Messages
6,114,316
Members
448,564
Latest member
ED38

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