VBA Help with copying from sheet to Sheet with Validating list

[Canadianexcel]

So I have hit a brick wall with my project I am working on in Excel. Here is my scenario for example I have a worksheet with a list of 17 questions with answers based on a validation list. My client would like to have the answers copied over to Sheet2 which is not a big deal however they want the answers copied over and the ability to do an override on Sheet 2. I have tried several ways but no success. I am able to build the validation list and copy the values sure but I was using the Worksheet activate and everytime I leave the sheet and back again I get the copied value over I have tried just doing a =Sheet1:A1 type of thing which sort of worked but in also increase the file size. I am not even sure it is possible withot a button to override. Any suggestions would be appreciated.

 

[DanteAmor]

You can explain with data of example, what data you have in the sheet1.
I guess after filling the 17 questions, you want to pass the answers from sheet1 to sheet2, but where on the sheet2 you want to put the answers.
If you could explain everything using example data, it would be easier to understand.

I also suppose that the answers will be filled again, on this second occasion, I suppose you also want to pass the answers from sheet1 to sheet2, but now on this occasion where you want to put the answers.
Remember to explain with examples.

 

[Canadianexcel]

Sorry I am not sure how to attach a file so hopefully I can explain further.

- Sheet 1 A2 to A18 has the question
- Sheet 1 B2 to B18 have a validation list Yes No N/A
- Requirement copy Sheet 1 B2 to B18 to Sheet 2 B2 to B18
- next requirement have the ability to change the value on Sheet 2 example B2 from a Yes to a No (Override).

I do have a working validation list in VBA for Sheet 2 but it is not copying the value from Sheet1

 

[DanteAmor]

Do you want the same data from sheet1 to be on sheet2?
And if you put on sheet2 in cell B2 the following formula:

=sheet1!B2

Copy the formula down to B18

 

[Canadianexcel]

This is a way I did do it however it increased the file size by three mb and what if they changed there mind and want to revert the answer back. I really don't see this without a button

 

[DanteAmor]

The same I did in my test file and increased to 36kb.
Your sheet must have something more.


With this formula, what you put on sheet1 automatically will be on sheet2, on sheet2 you do not need to have validation lists.

If you want I can look at your file and see what happens.

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.