VBA Help with Data Validation take two

Canadianexcel

New Member
Joined
Dec 23, 2010
Messages
39
Good Morning,

Need a bit of help to get my thinking straight. I have been working on a small project and need a little help to work through the logic. This is what I want to accomplish

- Sheet 1 I have a few Data validation list range D1:D5
- Sheet 2 I want to copy the values from D1:D5 but the ability to do an override through a data validation list which I have.

I know I can do a =Sheet1!D1 on Sheet 2 D1 however that formula will disappear once the override is done.

Any ideas would help.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
to be clear, is the override information in another data validation list, or completely open to user choice?

If the over ride is in another list, could you not use a =IF(override list = "", Sheet1!D1, override list value)
 
Upvote 0
So Sheet 1 D1 is a Yes or No. Say I select yes on Sheet 1 but decide I want to change the answer in Sheet 2 D1 to No but keep the answer to a Yes in Sheet 1. Essentially Sheets 1 and 2 are the same but for a history standpoint the person wants the ability to override on Sheet 2.
 
Upvote 0
I have just tested the following and it works fine,

I set a Yes,No data validation list in Sheet1

Then in Sheet 2 I set another Data validation list in D1 with the following: Yes,No,=Sheet1!D1

So you can select an override or the formula to get the value from sheet 1

This can be drag and copied down which will lookup the value in the corresponding row also :)
 
Last edited:
Upvote 0
I tried that and unfortunately not exactly what I am looking for atleast it did not work for me. I want to copy the cell value from Sheet1 D1 to Sheets 2 D1 and have the my data list validation have name ranges. I hope I am explaining myself o.k
 
Upvote 0
as in your list in the dropdown for the data validation is contained within a named range?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
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