VBA Help with Data Validation take two

Canadianexcel

New Member
Joined
Dec 23, 2010
Messages
37
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.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

cooper645

Well-known Member
Joined
Nov 16, 2013
Messages
555
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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)
 

Canadianexcel

New Member
Joined
Dec 23, 2010
Messages
37
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.
 

cooper645

Well-known Member
Joined
Nov 16, 2013
Messages
555
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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:

Canadianexcel

New Member
Joined
Dec 23, 2010
Messages
37

ADVERTISEMENT

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
 

cooper645

Well-known Member
Joined
Nov 16, 2013
Messages
555
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
as in your list in the dropdown for the data validation is contained within a named range?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,420
Messages
5,528,675
Members
409,829
Latest member
CFreeamaz

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top