How to autopopulate if cell says "yes"

I converted both data ranges into tables. Tables have many advantages: they know how big they are, they copy down formulas and formatting automatically and they are easy to reference in formulas.

Here is more information on tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

I added data validation to column H so only Yes or No is an acceptable answer. You may consider adding data validation to column B so that it only accepts dates, Column E (a whole number greater than or equal to zero) and column F (a decimal between 0.0 and 1.0). Copy down the validation or formulas to the column the first time and it will remember them ever after. When you add a new row, the formulas, etc. copy down automatically.

Here is more information on data validation: http://www.utteraccess.com/wiki/Data_Validation

Other than that, when you answer yes in the last column, that row is copied to the Relationship sheet and deleted from the Outreach sheet. Once it is done, it's done. I could put in an "Are you sure?" message box if you desire.

The relationship sheet is also a table, you have options for formulas and data validation here as well.

I noticed one thing. You have a notes column way out in column Q. Is that the same notes as on the outreach sheet? If so, I need to modify my code a bit.

The duplicate records in the relations sheet are a result of testing.

I can copy over the notes.

As for recalling - I can do at least one thing: when the user clicks yes, I can bring up a dialog box with a message that says, "You are about to move this record. Do you want to do this?" To actually recall a record after it has been moved: I can definitely do this for the very last record entered because I know where it is.

As for other records, I would need to know what makes a record unique. Is it the combination of Influencer and date of outreach? What I could do is provide a recall button, This will bring up a form where the user can enter an influencer (and maybe a date range) and it will being up a lost of records. The user selects the one wanted and it will be moved back. This will take a fair bit of "fun" coding.

I am waiting for some data to download in the real world which is why I have time to respond now. When that completes I will be busy, but should have enough spare time to be able to complete this by Friday.

Let me know your thoughts on how you would like the recall to work.

I'll put the notes in the correct column. That's not an issue.

I'll keep the record on the first sheet and only copy it to the next sheet.

Now that I know that the influencer is unique, I can use that as a key. So if someone makes a mistake and changes the Yes back to a no, I will remove the record from the second sheet.

I think I have enough to go on. You'll have a prototype soon.

Here is the prototype. It has sample data in it. You can remove the sample data (make sure you keep the headings). The program makes use of Excel Tables. If you are interested, here is more information on tables: http://www.utteraccess.com/wiki/Tables_in_Excel. All you have to know about tables is that they know how big they are and remember formulas, formats and validations. So even if you remove all the information, it's still there. Also as you add data in the row immediately below the table, it becomes part of the table and it inherits the formulas, formats, etc.

Column A is free format. Type in any name you want.

Column B has data validation on it to make sure what is entered is a valid date, and later than January 1, 2018. If you need to go back further let me know.

Column C has data validation for Campaigns. It is run from a table on the Lookups Sheet. This sheet can be hidden after you set it up. Clear out the test data and replace it with real data. This is a table, so as you add data, it becomes part of the table and part of the drop-down values. You can sort the values in the table if you want.

Column D has data validation for Consumer Segment - manage it the same way as Campaigns.

Column E has data validation for a whole number greater than or equal to zero

Column F has data validation to limit it to 0% to 100%

Column G is free entry.

Column J has data validation to Yes or No. Yes will add the record to the Relationship table. No will remove it if it is already there.

Hidden hint: If you change the data for a row, you can make that change show up by going to Column J and re-selecting YES. You can also do the same thing by selecting NO, making the changes and selecting YES again.

The display to the right of the table is a slicer. It acts like a filter on the table. So if you want to see who has already agreed to terms, select Yes (This should mimic what is on the Relationship Sheet). If you want to see who has not agreed to terms select No. If there are blank cells, it will show as an option too. You can make multiple selections with the CTRL Key and clear the filters by clicking on the funnel icon.

Believe it or not, most of this is "standard" Excel. I did very little programming.

I made the changes. B is any valid date after New Year's Day 1900 (that's as far back as Excel goes ).

Columns C and D are free-form.

The main reason I do this on the forum is to "pay back" all those who have helped me through the years. Also it helps me get some practice. I plan to "retire" in about a year, and go on to contracting which I might have to do remotely, so working with "customers" now gets me thinking about what kinds of questions I should be asking to nail down the requirements.

The two main reasons I took on this issue were:
1. You included a sample workbook - many people don't and they wonder why their questions don't get answers.
2. To me, the solution was obvious and there was a lot of "low hanging fruit" such as tables, data validation and slicers that are "standard" Excel and easy to implement. I knew I could give you a lot with little work. Now you have some examples for future work in Excel.

Sometimes I take on cases because they interest me either as a brain teaser or I know they will force me to look up some obscure corner of Excel that I hadn't worked in before.