×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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

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

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

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

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.

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

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...

=INDEX(SHEET!COLUMN,ROW-NUMBER)

...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.

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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.

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

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

CODE

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, Sheet2.Range("C:C"), 0)
        'turn off EVENTS
        Application.EnableEvents = False
        'replace lookup value with formula with lookup row
        Target.Formula = "=INDEX(" & Sheet2.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 Sheet2 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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

3
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

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

Jeff

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
Thanks for clearing that up!

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

Jeff

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

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.

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

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

CODE

If Not Intersect(Target, Range("T_BOM[Part Number]")) Is Nothing Then
'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

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

@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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

@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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
Skip,

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

Thanks for your help!
Jeff

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

@jmarkus, like your work!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

CODE

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

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

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

CODE

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue


IGNORE!

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue


IGNORE!

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

(OP)
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

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

Try this...

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue


IGNORE!

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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

CODE

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

I get a 'Type Mismatch' error.

Jeff

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

I guess I'll keep plugging away...

Thanks,
Jeff

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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:

CODE

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

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

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close