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

 

[SkipVought]

And BTW, I'd insert a BREAK and STEP thru the statements you posted and observe & verify the data in each statement. Use the Watch Window to observe the values.

Skip,

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

IGNORE!

 

[jmarkus]

Skip,

The line you are referring to simply reads the value from a cell in one workbook and worksheet and copies it to "ThisWorkboot.Worksheets("BOM"). Which works fine. My issue is in the line with "lLookupROW=" where I try to find the row reference. I was thinking that Excel might be looking for rg in the wrong workbook, so I wanted to somehow 'fully qualify' the inputs on that line.

Jeff

 

[SkipVought]

Try this...

[s]lLookupROW = Application.Match(Tgt.Value, [T_PARTS[Part Number]], 0)[/s]

Skip,

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

IGNORE!

 

[SkipVought]

Woa!

You're updating cells in the Data Validation range?

Yes?
So why are you doing all these handstands & cartwheels??? The Event does it all!!!

Skip,

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

 

[jmarkus]

No. This is not being used in the Worksheet_Change event (that problem was solved and works as you helped me above).

I have another subroutine which I wanted to call when data is being imported from another spreadsheet and I want the cell to be directly populated by the reference to the value (in the current workbook), instead of the value itself.

When I try

lLookupROW = Application.Match(Tgt.Value, [T_PARTS[Part Number]], 0)

I get a 'Type Mismatch' error.

Jeff

 

[SkipVought]

But you're using a Data Validation reference. Is your Tgt referencing a Data Validation cell?

Skip,

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

 

[SkipVought]

So in ThisWorkbook, you don't have a Structured Table named T_PARTS with a column headed Part Number? (no leading or trailing spaces)

Skip,

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

 

[jmarkus]

I do have the table and column, that's why I don't understand.

As for the data validation question, that is just generating the INDIRECT("T_PARTS[Part Number") portion being used in the final Tgt.Formula (so I guess that's redudant too, like Range(Cells(x,y).Address ).

This is probably some oversight on my part, but I just can't figure this out...

Jeff

 

[SkipVought]

Have you tried using the Watch Window and stepping thru a cycle or when you get an error, hit the DEBUG button and the use the Watch Window to see the state and value of objects and variables.

How to use the Watch Window:

https://www.tek-tips.com/faqs.cfm?fid=4594

Skip,

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

 

[jmarkus]

Yes, I've been using the watch window and stepping through the debugging...

I guess I'll keep plugging away...

Thanks,
Jeff

 

[SkipVought]

Let us know if and how you solved your problem.

Its seems to me that you have a Data Validation/Event in column D and column E. You could include other logic for the Event in column E, which is what you're trying to do in this modification that's not working. I don't think you gave us enough info to suggest an extended Event solution.

Skip,

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

 

[jmarkus]

Got it!
Also had an issue with the table replicating the formula down the rows when I didn't want to, so here is what I ended up with:

Application.AutoCorrect.AutoFillFormulasInLists = False

For r = (NewBOMLastRow + NextRow) To (NewBOMLastRow + BOMLastRow)
    ThisWorkbook.Worksheets("BOM").Cells(r, 5).Value = Trim(src.Worksheets(1).Range(Header(ImpCol)).Offset(2 + (r - NewBOMLastRow - NextRow), 0).Value)
    Set Tgt = ThisWorkbook.Worksheets("BOM").Cells(r, 5)
    lLookupROW = Application.Match(CStr(Tgt.Value), ThisWorkbook.Sheets("PARTS").ListObjects("T_PARTS").ListColumns("Part Number").DataBodyRange, 0)
    Tgt = "=INDEX(T_PARTS[Part Number]," & lLookupROW & ")"
Next

Application.AutoCorrect.AutoFillFormulasInLists = True

Thanks for pushing me to figure it out!

Jeff

 

[SkipVought]

had an issue with the table replicating the formula down the rows when I didn't want to

Automatic formula replication: a blessing when you want it and a curse when you don't. Fortunately it can be disabled.

Skip,

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

 

[SkipVought]

BTW, in some cases, you may deal with a multiple-cell Target range, in which case the entire Target range must be looped thru...

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

Skip,

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