Return text value based on looking up multiple text values in split ranges

[tpye314]

Hello Ladies and Gents, I am trying to create an automatically filled text box based on the text value of two other cells.

Please see the example below. I have on another sheet two drop down lists, one to select the customer, the second recognises which customer has been selected and then offers the list of customer sites. What I want is for a third cell to recognise the value of the first two and enter the ab, af, bd etc. values.

I can get this to work with continuous lists but not when the data is split up like this.

Please can you help with my problem?

Thanks!

[table="width: 500"]
[tr]
[td]Customer_1[/td]
[td][/td]
[td]Customer_2[/td]
[td][/td]
[td].....[/td]
[/tr]
[tr]
[td]Customer_1_Site_1[/td]
[td]ab[/td]
[td]Customer_2_Site_1[/td]
[td]ba[/td]
[td]....[/td]
[/tr]
[tr]
[td]Customer_1_Site_2[/td]
[td]ac[/td]
[td]Customer_2_Site_2[/td]
[td]bb[/td]
[td]....[/td]
[/tr]
[tr]
[td]Customer_1_Site_3[/td]
[td]ad[/td]
[td]Customer_2_Site_3[/td]
[td]bc[/td]
[td].....[/td]
[/tr]
[tr]
[td]Customer_1_Site_4[/td]
[td]ae[/td]
[td]Customer_2_Site_4[/td]
[td]bd[/td]
[td].....[/td]
[/tr]
[tr]
[td]Customer_1_Site_5[/td]
[td]af[/td]
[td]Customer_2_Site_5[/td]
[td]be[/td]
[td].....[/td]
[/tr]
[tr]
[td]Customer_1_Site_6[/td]
[td]ag[/td]
[td]Customer_2_Site_6[/td]
[td]bf[/td]
[td].....[/td]
[/tr]
[/table]

 

[Enigmus345]

Hi tpye314,
I am still learning VBA, so apologies for that firstly. However, I'm not sure I understand what you are asking. My initial thoughts to solve this issue would be to place code on the second listbox, something like

Private Sub ComboBox2_Change()
if combobox2.value = "Customer_1_Site_1" then
textbox1.value = "ab"
elseif
combobox2.value = "Customer_1_Site_2" then
textbox1.value = "ac"
...etc
End Sub

It's rather long winded and not very pretty (kinda like my reply), but without any extra information and with my limited knowledge, that's the best I could come up with.
Perhaps if you could clarify where you would like the ab ac information to go, i.e an active x textbox on a spread sheet, or a particular cell on the sheet there might be a simpler solution to this.
But basically, I would think that you only need to look at one lot of text, that being the text in the second combobox (or drop-down box), as the value in this box is dependent on what is selected in the first, which in my mind, makes looking at the value in the first box to populate the the third redundant. Sorry, does that make sense? Or am I making a complete mess of things? Again sincere apologies if I have made a mess of things. My fingers are crossed that I helped you, even if it's just a little!

 

[tpye314]

Hi Enigmus345,

Thank you for your response. Your solutions would work but it would mean that each time I add a new customer I would have to add in a lot a code.

You are correct in that just using the second list could work. Using both would just give a bit of extra stability as in the odd rare occasion the second value could be the same for two customer (the example I gave is very simplistic)

Let me try to explain what I want a bit better.

In "Sheet 1" I have columns A B and C. In column A I have a drop down list that references a list of customers in "Sheet 2". Column B of "Sheet 1" recognises which customer has been selected and then gives a dropdown list of that customers different sites, referenced from a second list in "Sheet 2". I then want column C of "Sheet 1" to recognise these and automatically fill in a piece of information about that customer in that location, taken from a 3rd set of date on "Sheet 2".

I hope this makes more sense now?

The problem that I have is the layout means that the reference sets of data are split up into multiple groups of data.

 

[Enigmus345]

Yeah that does make a bit more sense to me now, but unfortunately I still feel I must be misunderstanding you somewhere, because now I have no idea how to go about achieving this! I'm very sorry. Automating the code when you add new data is something that still very much eludes me, especially when working on an excel sheet using VBA. I personally prefer using userforms to perform all the hard work and just using the spread sheet to store the data. Yeah man, I don't know hey. I feel like the solution to your problem will require solutions to other issues, like automating adding a new customer to the sheet. You know what I mean? I'm hoping someone with more experience and know-how will see this post and weigh in, providing you with exactly what you need to do to fix this and thus rendering me a VBA hack! lol. I mean I'm more than happy to keep trying to help, but my concern would be my lack of knowledge would just be frustrating to you. I just try and help people with the easy stuff, or stuff that sounds similar to things I've done as well, that hasn't had any replies yet because I know how frustrating it can be to not get any replies at all on posts.