Compile various data from 2 lists into one [SOLVED]

[ganzzu]

Hi,


In a file I am working on I have to add 2 information (which is selected from 2 lists) to a given event name (1 text information before the event name and 1 text information after the event name) and display it in a cell.


For example:


Event name - Christmas

1st information - Schools

2nd information - Parents


The entire text which is supposed to be displayed is: '[Sch]: Christmas - Prnt'


My code looks like this:


If UCase(e.Cells(xee, 5).Value) = "SCHOOLS" And UCase(e.Cells(xee, 10).Value) = "PARENTS" Then

o.Cells((6 * (j - 1) + q), 6).Formula = "[Sch]: " & e.Cells(xee, 3).Value & " - [Prnt]"


The problem is I have many options in those 2 lists so there are a lot of combinations. I can duplicate this code and add each combination, but I'm afraid the code will be way too long and the procedure will become too large (and error).


Example:


ElseIf UCase(e.Cells(xee, 5).Value) = "SCHOOLS" And UCase(e.Cells(xee, 10).Value) = "TEACHERS" Then

o.Cells((6 * (j - 1) + q), 6).Formula = "[Sch]: " & e.Cells(xee, 3).Value & " - [Tch]"

ElseIf UCase(e.Cells(xee, 5).Value) = "COLLEGE" And UCase(e.Cells(xee, 10).Value) = "TEACHERS" Then

o.Cells((6 * (j - 1) + q), 6).Formula = "[Coll]: " & e.Cells(xee, 3).Value & " - [Tch]"

ElseIf UCase(e.Cells(xee, 5).Value) = "COLLEGE" And UCase(e.Cells(xee, 10).Value) = "PARENTS" Then

o.Cells((6 * (j - 1) + q), 6).Formula = "[Coll]: " & e.Cells(xee, 3).Value & " - [Prnt]"


...and so on.


Is there a more simple way to do this?


Thanks in advance!

 

[Debraj]

Hi ganzzu!


Its may be in sequence of your last query..


But if you provide a sample file.. with expected output, or the complete code.. may be we help you better..


Regards,

Deb

 

[Hui]

Ganzzu


You could probably use a Select Case function

eg:

Select Case UCase(e.Cells(xee, 5).Value)+"-"+UCase(e.Cells(xee, 10).Value)+"-"+UCase(e.Cells(xee, 3).Value)

Case "CHRISTMAS-SCHOOL-PARENT"

Do something

Case "EASTER-SCHOOL-PARENT"

Do something else


etc

 

[NARAYANK991]

Hi ,


Have two variables which are declared as Prefix and Suffix as follows :

[pre]

Dim Prefix As String , Suffix As String
Now , have a few IF statements or Select Case statements to define these two variables ; for example if it is a School , then Prefix is "[Sch]: " , if it is a College , then Prefix is "[Coll]: ".


Similarly , if it is Teachers , then Suffix will be " - [Tch]" ; if it is Parents , then Suffix will be " - [Prnt]"


Now have just one output statement as follows :

o.Cells((6 * (j - 1) + q), 6).Formula = Prefix & e.Cells(xee, 3).Value & Suffix

[/pre]
Narayan

 

[jeffreyweir]

THis looks like it could be solved by dynamic cascading dropdowns too. No VBA neccessary. i.e. they pick from the first two lists and the third cell is automatically filled based on what they picked.


I answered a post with similar requirements recently at http://chandoo.org/forums/topic/referenced-table-names-inside-index-formula with such a list setup.


Might need further amendment for your needs, but at least this will let you see if this is something you want to look into further.

Sample file at below link, whereby someone chooses something from the first two lists, and some text populates in the 3rd column as a result. Very simple to maintain, and doesn't need VBA. THat said, my example also has some code in it that clears 'downstream' choices if you later change an 'upstream' choice.

https://docs.google.com/file/d/0B1hgC5lSuLjVT1ZOellIZlJpdjQ/edit?usp=sharing

 

[ganzzu]

Hi,


Thank you all for taking time from yourselves and trying to help.


I tried Narayan's proposal and it worked ok. However, I solved the issue in a different (and easier from the code point of view) manner. I dropped the 2nd text data (he Suffix) from the displaying text string. I will only keep it in the database sheet and it will be used only if necessary via filtering. It seems it hadn't been such an important information after all ...


Thanks again to all of you!