Can Data Validation be imported and used in a separate sheet?

[Ronderbecke]

The answer to this may be just a flat no!, but I am looking for a way to allow a data validation to be imported to another sheet and be edited by that sheet. Here is what I am looking at:

Cell A is a reps name, Cell B is a customers name, Cell C is the result.

Each rep has their own sheet that queries/pulls only their data for cells A, B, and C. When it pulls over cell C which is data validation, it just ends up as a blank cell on their sheet. I need it to allow the new sheet to select the result with that customer so that it will feed back to the original sheet! Is that even possible?

 

[onlyadrafter]

Hello.

It isn't totally clear what you expect, but I think you may want to look into INDIRECT formula in the data validation.

 

[AxonFantaxon]

I would create a named range in the main sheet and use the INDIRECT formula as onlyadrafter said but have the cells for the named range linking directly into the reps sheet and use update links when opening the book to keep them up to date. Apologies if I've misunderstood entirely

 

[Ronderbecke]

I don't know that I explained this very well. I am trying to see how I could potentially make this work properly with an indirect formula but the concern would be on the original document. Here is what happens: Google forms puts the data in a spreadsheet by time/date submission. To keep itas simple as possible I created a sample data from 2 separate worksheets. The 1st one is the main page and the 2nd one is the reps page.
Main Page:
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 129px"><col width="132"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD]Time Stamp[/TD]
[TD]Customer Name[/TD]
[TD]Rep Name[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2019 12:42:52[/TD]
[TD]Customer 1[/TD]
[TD]Sales Rep 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7/25/2019 20:29:19[/TD]
[TD]Customer 2[/TD]
[TD]Sales Rep 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7/25/2019 20:29:57[/TD]
[TD]Customer 3[/TD]
[TD]Sales Rep 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7/26/2019 10:57:55[/TD]
[TD]Customer 4[/TD]
[TD]Sales Rep 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7/26/2019 17:06:56[/TD]
[TD]Customer 5[/TD]
[TD]Sales Rep 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7/26/2019 19:10:30[/TD]
[TD]Customer 6[/TD]
[TD]Sales Rep 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7/26/2019 19:32:18[/TD]
[TD]Customer 7[/TD]
[TD]Sales Rep 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7/29/2019 18:05:23[/TD]
[TD]Customer 8[/TD]
[TD]Sales Rep 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7/29/2019 18:05:46[/TD]
[TD]Customer 9[/TD]
[TD]Sales Rep 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7/29/2019 19:38:51[/TD]
[TD]Customer 10[/TD]
[TD]Sales Rep 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7/30/2019 16:47:09[/TD]
[TD]Customer 11[/TD]
[TD]Sales Rep 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7/30/2019 17:04:57[/TD]
[TD]Customer 12[/TD]
[TD]Sales Rep 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7/30/2019 17:05:01[/TD]
[TD]Customer 13[/TD]
[TD]Sales Rep 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4/22/2019 19:01:11[/TD]
[TD]Customer 14[/TD]
[TD]Sales Rep 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4/22/2019 19:22:25[/TD]
[TD]Customer 15[/TD]
[TD]Sales Rep 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11/27/2018 20:20:11[/TD]
[TD]Customer 16[/TD]
[TD]Sales Rep 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11/30/2018 23:06:44[/TD]
[TD]Customer 17[/TD]
[TD]Sales Rep 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2018 11:34:44[/TD]
[TD]Customer 18[/TD]
[TD]Sales Rep 2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Rep Page:
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 119px"><col width="100"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD]Time Stamp[/TD]
[TD]Customer Name[/TD]
[TD]Rep Name[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="align: right"]7/25/2019 20:29:57[/TD]
[TD]Customer 3[/TD]
[TD]Sales Rep 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7/29/2019 18:05:23[/TD]
[TD]Customer 8[/TD]
[TD]Sales Rep 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7/29/2019 19:38:51[/TD]
[TD]Customer 10[/TD]
[TD]Sales Rep 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7/30/2019 17:05:01[/TD]
[TD]Customer 13[/TD]
[TD]Sales Rep 3[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]


What happens is the page for the rep only pulls their customers. I want the Result to be able to be edited on the rep page and show up on the main page. The hard part is, if the rep is every changed, it switches pages, and since the whole document is going by timestamp, it would mess up both rep pages and all of the customers below what was edited if it pulled directly back from the rep page. I need a way to make sure the result sticks with the customer (regardless of what rep holds it). Is this example helping? Essentially I need Column D4 from the main page to be able to be edited by Sales Rep 3 page and show up in D4.

 

[mikerickson]

It sounds like you have a main sheet and a rep sheet that is essentially a filtering of the main sheet.

You want the Result column cells on the rep sheet to be linked to the Result column sheet on the main sheet.

Yes, that is possible with VBA. This assumes that you have a sheet named "Main" and one for each sales rep, named "Sales Rep 1" etc, i.e. an exact match for the entry in the Rep Name column of Main. And that every Sales Rep has a sheet.
It also assumes that the time stamp is in column A and that the data layout is the same in the Main sheet and the Rep sheet.
It also assumes that the time-stamps on the main sheet are unique, no two sales reps have a row with the same time stamp. (Additional checks can be added if there are duplicates)
There is error checking that could be added, but this will give you a start.
If these assumptions aren't the case, there is additional code that can be added to adjust.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim DestinationRange As Range
    Dim TimeStamp As Variant
    
    If Target.Cells.Count = 1 Then

    If Sh.Name = "Main" Then
        If Target.EntireColumn.Cells(1, 1) = "Result" Then
            TimeStamp = Target.EntireRow.Cells(1, 1).Value
            With Sheets(Target.Offset(0, -1).Value)
                Application.EnableEvents = False
                .Cells.Find(TimeStamp).EntireRow.Range("D1").Value = Target.Value
                Application.EnableEvents = True
            End With
        End If
    ElseIf Sh.Name Like "Sales Rep*" Then
        If Target.EntireColumn.Cells(1, 1) = "Result" Then
            TimeStamp = Target.EntireRow.Cells(1, 1).Value
            With Sheets("Main")
                Application.EnableEvents = False
                .Cells.Find(TimeStamp).EntireRow.Range("D1").Value = Target.Value
                Application.EnableEvents = True
            End With
        End If
    Else
    
    End If
    End If
End Sub

 

[Ronderbecke]

I greatly appreciate the help, and was hoping you could help me understand what is happening here? Unfortunately, I haven't really used any VBA code at all so its completely new to me. There are no 2 matching sheets and all timestamps go by the second so there is no way they would double up so the two circumstances that have to be true, are. With this code, do you simply put data validation in the D column on Master and it allows all the rep sheets to select the result? Is this a 1 time code that goes in the sheet and will handle as many reps that are added? It looks as though its maintaining the order of rep pages and results based on the timestamp column, is that correct? Would it be possible to use indirect like the other responses mentioned to accomplish what this VBA code does? Thanks so much for your response, and I apologize if I am not quite at that level of google sheets.

It sounds like you have a main sheet and a rep sheet that is essentially a filtering of the main sheet.

You want the Result column cells on the rep sheet to be linked to the Result column sheet on the main sheet.

Yes, that is possible with VBA. This assumes that you have a sheet named "Main" and one for each sales rep, named "Sales Rep 1" etc, i.e. an exact match for the entry in the Rep Name column of Main. And that every Sales Rep has a sheet.
It also assumes that the time stamp is in column A and that the data layout is the same in the Main sheet and the Rep sheet.
It also assumes that the time-stamps on the main sheet are unique, no two sales reps have a row with the same time stamp. (Additional checks can be added if there are duplicates)
There is error checking that could be added, but this will give you a start.
If these assumptions aren't the case, there is additional code that can be added to adjust.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim DestinationRange As Range
    Dim TimeStamp As Variant
    
    If Target.Cells.Count = 1 Then

    If Sh.Name = "Main" Then
        If Target.EntireColumn.Cells(1, 1) = "Result" Then
            TimeStamp = Target.EntireRow.Cells(1, 1).Value
            With Sheets(Target.Offset(0, -1).Value)
                Application.EnableEvents = False
                .Cells.Find(TimeStamp).EntireRow.Range("D1").Value = Target.Value
                Application.EnableEvents = True
            End With
        End If
    ElseIf Sh.Name Like "Sales Rep*" Then
        If Target.EntireColumn.Cells(1, 1) = "Result" Then
            TimeStamp = Target.EntireRow.Cells(1, 1).Value
            With Sheets("Main")
                Application.EnableEvents = False
                .Cells.Find(TimeStamp).EntireRow.Range("D1").Value = Target.Value
                Application.EnableEvents = True
            End With
        End If
    Else
    
    End If
    End If
End Sub

 

[mikerickson]

If you want a change on the Main sheet also reflected on the Rep sheet, and vice versa, you are trying to set up a circular reference, which can not be done with formulas.

That code gets put in the ThisWorkbook code module.

The Workbook_SheetChange event will run every time any cell is changed in the workbook. It first looks to see if the changed cell was on Main!, if it was test if the cell is in the Result column, if it is, find the matching cell in the worksheet for that Rep and change the Result value for that row.

If the sheet is a sales rep sheet, then it puts the value in Main.