Need linkedcell of text box to be result of vlookup formula

[jmitchells5w]

I have a dropdown list where a series of company names can be selected. I then have a text box where I would like the linkedcell property to be equal to the result of a vlookup formula triggered by the drop down. I like the feature that you can edit text in a text box and it will also edit the cell it is linked to.

Example: the user selects "Company A" from a dropdown list (using data validation). Using the vlookup formula and "Company A" I would then pull data from Column D in my array and display it in the text box. If the user edits the text in the text box then it should also edit the contents of whatever cell was the result of the vlookup formula. The goal is to allow the user to edit the contents of the cell in Column D without actually going to my array (in another worksheet).

Thank you in advance for any advice you can give!

 

[jmitchells5w]

Well, I have figured out how to retreived the cell address of a vlookup result. I basically use a combination of the ADRESS and MATCH functions. Example: =ADDRESS(MATCH(C3,Vendors,0)+2,20). This formula looks for the selection displayed in C3 in my table/array called "Vendors" (located 2 rows from the top of my spreadsheet). The Match function returns the row number of that result and since I already know which column the result is in I use the Address function to put the two together. I now have the referenced cell in D37. Now, how do I tell a textbox to pull the text from the reference cell displayed in D37? Is there a way in VBA to set the linkedcell property to be equal to the reference/value displayed in D37? Note: I do not want to link D37 as that only contains the cell reference. The goal is the have the cell that is referenced in D37 update with text that is typed into the textbox.

 

[jmitchells5w]

OK, I figured this out. I have a cell (D37 - hidden behind my text box) which displays the address of the result from a vlook up formula. I did this by using a combination of the ADRESS and MATCH functions. The vlookup is taking the value (company name) in C3 (I used data validation here) and is looking up the company info located on another worksheet in a large table of several vendors with various columns of information. I used the Worksheet Change event in VBA to call a macro when a new value is selected in cell C3. My macro basically takes the sheet name where my info table is located, adds the cell address from D37 and inputs this info into the linkedcell property of my textbox. Now, not only does the textbox update when a new company name is selected, but if you were to click the text box and make any changes to the text, it is automatically changed/updated on my table located in the other worksheet. My goal was to have a "dashboard" for users to pull up info on a certain company but be able to make changes to the info without having to find their way through my large table of Company name and info.