Solved: UserForms: Passing inputs from one to another

[daymaker]

I'm creating an worksheet in excel 2007. I need to display the results in column J using this formula
Quote:
==IF(A2="","",IF(SUMPRODUCT(--($A$2:$A2&$F$2:$F2=A2&F2))=1,(20-H2)/20,(20-SUMIF($F$2:$F2,F2,$H$2:$H2))/20))

This calculation is dependent on several columns entries. I want to show it in a specific format. In column F I've created a user form with several items. If I select more than an option in column F, the results has to be displayed accordingly both in column I & J accordingly.

Lets say I select the entries abc, xyz in F2. Then after the calculation in column I & J, I want the results to be displayed in the following format:

In I2:
abc: 50% ; xyz = 50%;

In J2:

abc : 50% ; xyz = 50%;

I believe I need to create user forms in column I & J and then pass the inputs based on the selected entries in the user form in column F to the user forms in column I & J. Also, please note that these new user forms in I & J should also use the formula entered in the respective cells.


Could you please help me create these user forms? I've attached the excel for reference.

[Bob Phillips]

I don't understand why you need another form. Can you explain again?

[daymaker]

I don't understand why you need another form. Can you explain again?

Hello xld,
I'm not sure if we need another userform. In tester column F, when we select more than 1 tester, the results don't get displayed properly in column I & J. I need to display the result in the format stated above in column I & J. So lets say I select Havva & Soumya in the column F, then the results in the column I & J should should be displayed as :

In Column I:
Havva : 50% ; Soumya : 50%

In Column J:
Havva : 50% ; Soumya : 50%

The values 50% would be based on the calculation in column I & J. So I've just 50 over as an example and not based on calculation using the formulas in the columns.

How do I achieve this?

[Bob Phillips]

Sorry, I don't get it. I select Havva in F2 and I see 50% in I2 and J2. Ditto Soumya.

[daymaker]

hi..I'm talking about selecting multiple entries in a single cell F2 and then getting the results displayed in a specific format.
Just try selecting both Havva and Soumya in F2.

[daymaker]

hi..I'm talking about selecting multiple entries in a single cell F2 and then getting the results displayed in a specific format.
Just try selecting both Havva and Soumya in F2.

Attached a sample excel sheet to show how the results should be displayed in case of more than 1 tester in F2.
However, in I2 & J2 I've entered the result manually. I want it to be displayed in the similar format automatically.

[Bob Phillips]

How doi you decide the 25/75 split between I and J?

[daymaker]

That is just based on the manual calculation. However, I want the results to be displayed in the same format, which is my requirement. For the calculation part, you can refer the same formula in the abc2.xls attached. My requirement is to display the results in the format which I've mentioned in abc1.xls automatically.

[Bob Phillips]

I am thinking that you will need a UDF, which is simple enough, but the UDF needs the rules for calculating that percentage. Display is simple, but the rules are required.

[daymaker]

The rules for calculating percentage is fine as I've it already. I just need help in terms of displaying the result.

[Bob Phillips]

Well, as I said, a simple UDF will do it

[vba]

Public Function SplitNames(NamesCell As Range)
Dim vecNames As Variant
Dim cell As Variant
Dim yourPcge As String
Dim tmp As String

vecNames = Split(NamesCell.Value, ",")
For Each cell In vecNames

yourPcge = "xx" ' replace with code to calculate percentage
tmp = tmp & cell & " : " & yourPcge & " ; "
Next cell

SplitNames = Left$(tmp, Len(tmp) - 3)
End Function
[/vba]