MS Excel - Drop Down Lists that result in links to original list cells 4

[jmarkus]

I would like to create a drop down list which is based on a column in a table, but when the item (Cell D5) is selected from the list(Table MyList, Column C) it should be a link to the item so that when I change the item in the original table (MyList) it also updates in the cell D5 that was selected through drop down.

Hopefully that makes sense...and can be done.

Thanks,
Jeff

 

[Geoff13]

If I understand ...

Go to cell D5. Choose on the ribbon Data ... Data Validation.

Change Validation Criteria to be "List" and for the Source select the choices in Column C.

Hopefully that's what you asked, or at least close enough you can figure out what you want.

Geoff

 

[MintJulep]

Maybe possible with INDIRECT(), OFFSET(), LOOKUP() or the new XLOOKUP() and a helper column or two.

Or it might create a black hole.

I suggest that you tell us what you are trying to achieve so we can offer alternate solutions.

 

[SkipVought]

So the value that is fundamental is the row number of the table you are using in your drop-down. So the formula that you could use is...
[tt]
=INDEX(SHEET!COLUMN,ROW-NUMBER)
[/tt]
...in your case column C on whatever sheet.

But the question is, how to generate from a dropdown.

Once you pick your dropdown value, you'd need something to happen--an EVENT process, to substitute the formula for the value.

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim lRow As Long
   If Not Intersect(Target, Range("D:D")) Is Nothing Then
      lRow = Application.Match(Targer.Value, SomeSheet.Range("C:C"),0)
      Application.EnableEvents = False 
      Target.Formula ="=INDEX(SomeSheet.Value & "!C:C",lRow)"
      Application.EnableEvents = True 
   End If
End Sub

I did this without the VB Editor, so there could be errors in my memory. So check it out well. I think it should work.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Aesur]

Another simpler solution is create a new range of cells using vlookup functions that change based on D5 input and then have your data validation list link to this new range. By doing this the data validation list is always linked to the same column of cells, but that column can be dynamic based on the D5 input. You may also have to go into options and turn on circular formulas.

 

[SkipVought]

Finally got an opportunity to put my code into Excel VBA. My code had a few problems, so this is tested.

Option Explicit
'SkipVought 2020 Aug 16
'Data Validation LOOKUP that
' replaces the LOOKUP VALUE for a LOOKUP FORMULA
' that uses the LOOKUP VALUE ROW OFFSET

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lRow As Long
   
   'Worksheet_Change in Column D only
    If Not Intersect(Target, Range("D:D")) Is Nothing Then
        'lookup row
        lRow = Application.Match(Target.Value, [highlight #8AE234]Sheet2[/highlight].Range("C:C"), 0)
        'turn off EVENTS
        Application.EnableEvents = False
        'replace lookup value with formula with lookup row
        Target.Formula = "=INDEX(" & [highlight #8AE234]Sheet2[/highlight].Name & "!C:C," & lRow & ")"
        'turn on EVENTS
        Application.EnableEvents = True
    End If
End Sub

Copy this code. To get this code into the right place...
1) Activate the sheet containing your DataValidation Lookup
2) Right-Click the SHEET TAB and Select View Code from the popup
3) Paste into the empty code window
4) Where I have [highlight #8AE234]Sheet2[/highlight] you must substitute the CodeName for your lookup sheet. The CodeName is found in the Project Explorer ctrl+R where you will see the workbook and sheet objects.

I've also uploaded my sample workbook so you can see how it works and is assembled.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Here's a version that
ASSUMES:
1) your column C range is a Workbook domain NAMED RANGE (as opposed to a Worksheet domain)
2) the DataValidation LIST range is your NAMED RANGE

Option Explicit
'SkipVought 2020 Aug 16
'Data Validation LOOKUP that
' replaces the LOOKUP VALUE for a LOOKUP FORMULA
' and uses the LOOKUP VALUE ROW OFFSET

'NOTE: Make sure that your lookup range is a NAMED RANGE
' and that NAMED RANGE is used in the DataValidation LIST spec.
' That way you need not be concerned for any sheet name.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lLookupROW As Long, sLookupRANGE As String, rg As Range
   
   'Worksheet_Change in Column D only
    If Not Intersect(Target, Range("D:D")) Is Nothing Then
        'DataValidation FORMULA
        sLookupRANGE = Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1)
        'convert string to Range Object
        Set rg = Evaluate(sLookupRANGE)
        'lookup row
        lLookupROW = Application.Match(Target.Value, rg, 0)
        
        'turn off EVENTS
        Application.EnableEvents = False
        'replace lookup value with FORMULA: LookupRange & Lookup ROW
        Target.Formula = "=INDEX(" & sLookupRANGE & "," & lLookupROW & ")"
        'turn on EVENTS
        Application.EnableEvents = True
    End If
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[jmarkus]

Sorry, looks like my notifications weren't working so I didn't see any of these answers until now.

Skip,
From what I see here is that the event which triggers substitution of the value with the formula is changing worksheet tabs, is that correct?

Is there another event that I could use as a trigger which wouldn't involve clicking on another worksheet and then back again?

Thanks,
Jeff

 

[SkipVought]

Nothing is changing worksheet tabs. I'm not sure what you're referring to. It's designed to work seamlessly.

Be sure to use the latest upload.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[jmarkus]

Sorry, I thought the event "Private Sub Worksheet_Change" was triggered by changing tabs. I guess I'm wrong about that.

Jeff

 

[SkipVought]

The Worksheet_Change event fires when any change of DATA occurs on any sheet.
The Worksheet_Activate event fires whenever a worksheet is activated.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[jmarkus]

Thanks for clearing that up!

I'll play with your code some more to make sure I understand it all.

Jeff

 

[JG2828]

Nice Job Skip. I was wondering how you were going to solve that one.
The only (possible) issue I see is that if you have duplicates in the range, and select the second duplicate in the drop down, it links to the first duplicate, not the second one. So changing the second one doesn't change the selected item in the dropdown.
Not an issue as long as you don't have duplicates in your range, so this probably isn't a problem in most cases. jmarkus would need to make sure there are no duplicates in his use case.

 

[jmarkus]

I implemented it for my Table on the tab. Here is what I ended up with:

[indent]If Not Intersect(Target, Range("T_BOM[Part Number]")) Is Nothing Then[/indent]
        'DataValidation FORMULA
        sLookupRANGE = Right(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1)
        'convert string to Range Object
        Set rg = Sheets("PARTS").ListObjects("T_PARTS").ListColumns(1).DataBodyRange
        'lookup row
        lLookupROW = Application.Match(Target.Value, rg, 0)
        
        'turn off EVENTS
        Application.EnableEvents = False
        'replace lookup value with FORMULA: LookupRange & Lookup ROW
        Target.Formula = "=INDEX(" & sLookupRANGE & "," & lLookupROW & ")"
        'turn on EVENTS
        Application.EnableEvents = True
    End If

Thanks again!
Jeff

 

[SkipVought]

@JG2828, Well in my world, a "selection list" is by definition a list of unique values. Yes, it may take some effort to assure that definition.

And the reason for a DataValidation Drop-Down is to assure valid values.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

@Jeff, is there a question or just FYI?

BTW, glad to see that you're using Structured Tables! 'Ata boy! Such a powerful, self-documenting tool. I can see that you have a Bill-of-Material Table and a Parts Table or something akin, just because you 1) use Structured Tables and 2) use a naming convention that is meaningful.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[jmarkus]

Skip,

No question. Just showing you what I ended up with.

Thanks for your help!
Jeff

 

[SkipVought]

@jmarkus, like your work!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[jmarkus]

Hi, I'm back. I liked Skip's method so much that I decided to use it in another routine. The problem here is I am working with 2 different workbooks, since I am copying data from one workbook to another.

I think my code is confused because my ranges aren't fully qualified. It always bugs-out on the lLookupRow line. In "ThisWorkbook" rg lives on the "PARTS" sheet and Tgt lives on the "BOM" sheet. I also have another workbook open "src" which I think may be confusing it.

For r = (NewBOMLastRow + NextRow) To (NewBOMLastRow + BOMLastRow)
    ThisWorkbook.Worksheets("BOM").Range(Cells(r, 5).Address).Value = Trim(src.Worksheets(1).Range(Header(ImpCol)).Offset(2 + (r - NewBOMLastRow - NextRow), 0).Value)
    Set Tgt = ThisWorkbook.Worksheets("BOM").Cells(r, 5)
    sLookupRANGE = Right(Tgt.Validation.Formula1, Len(Tgt.Validation.Formula1) - 1)
    Set rg = ThisWorkbook.Worksheets("PARTS").ListObjects("T_PARTS").ListColumns("Part Number").DataBodyRange
    lLookupROW = Application.Match(Tgt.Value, rg, 0)
    Tgt.Formula = "=INDEX(" & sLookupRANGE & "," & lLookupROW & ")"
Next

I tried different ways I thought I could fully qualify 2 ranges, but I guess I didn't do them right, cause nothing seemed to work.

Any ideas?

Thanks,
Jeff

 

[SkipVought]

You don't need Range or Address in this assignment.

[s]ThisWorkbook.Worksheets("BOM").Cells(r, 5).Value = Trim(src.Worksheets(1).Range(Header(ImpCol)).Offset(2 + (r - NewBOMLastRow - NextRow), 0).Value)[/s]

I have no idea what you're trying to do here. How does NewBOMLastRow In Thisworkbook have anything to do with NewBOMLastRow in some other workbook.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

IGNORE!