how to get the value from multiple dropdowns using ListFillRange

[lui de sagun]

Hi,

It's me again. I still have a problem regarding the excel that I am working on. In my "view" sheet, I have a dropdown (cmbMandaysAll). Using ListFillRange command:

========================
Private Sub cmbProducts_Change()
If cmbProducts.Text = "Service Desk" Then
Me.cmbTickets.Enabled = True
ElseIf cmbProducts.Text = "Service Delivery" Then
Me.cmbClass.Enabled = False
Me.cmbTickets.Enabled = False
Me.cmbMandaysAll.ListFillRange = "ServiceDelivery!A2:A13"
ElseIf cmbProducts.Text = "Service Management" Then
Me.cmbMandaysAll.ListFillRange = "ServiceManagement!A2:A4"
Me.cmbClass.Enabled = True
ElseIf cmbProducts.Text = "Software Design and Delivery" Then
Me.cmbMandaysAll.ListFillRange = "ServiceDesign!A2:A13"
Else
Me.cmbMandaysAll.Enabled = True
End If
End Sub
========================

I was able to create a macro wherein its value varies every time I change the value of "cmbProducts" dropdown.

My problem now is that I am getting all the values of "Cost" (from "ServiceDelivery", "ServiceManagement" and "ServiceDesign" sheets) value from all of my ListFillRange. How can I be able to extract the actual value of the "Cost" based from what I selected from "cmbProducts"?

Please help. I attached my file for your reference.

 

[kchiba]

I think you need another = sign in you statement

Me.cmbMandaysAll.ListFillRange = "=ServiceDelivery!A2:A13"

try this

 

[lui de sagun]

I think you need another = sign in you statement

Me.cmbMandaysAll.ListFillRange = "=ServiceDelivery!A2:A13"

try this

Hi

 

[lui de sagun]

HI Kanti,

I tried applying but I still get all the results

Private Sub cmbProducts_Change()
If cmbProducts.Text = "Service Desk" Then
Me.cmbTickets.Enabled = True
ElseIf cmbProducts.Text = "Service Delivery" Then
Me.cmbClass.Enabled = False
Me.cmbTickets.Enabled = False
Me.cmbMandaysAll.ListFillRange = "=ServiceDelivery!A2:A13"
ElseIf cmbProducts.Text = "Service Management" Then
Me.cmbMandaysAll.ListFillRange = "=ServiceManagement!A2:A4"
Me.cmbClass.Enabled = True
ElseIf cmbProducts.Text = "=Software Design and Delivery" Then
Me.cmbMandaysAll.ListFillRange = "=ServiceDesign!A2:A13"
Else
Me.cmbMandaysAll.Enabled = True
End If
End Sub

 

[kchiba]

Hi Lui,

sorry i do not understand what you want, please give an example of what your input is and your expected result.

 

[lui de sagun]

Apologies, Kanti. Let me re-phrase what I want:

In the same file, aside "data" and "view" sheets, I have 3 more namely "Service Delivery", Service Design" and Service Management". In the last 3 sheets, I have separate data consist of "Mandays" and "Cost". I was able to combine all the "Mandays" in one dropdown ("cmbMandaysAll") in "view" sheet using ListFillRange macro (as stated above). I also was able to retrieve the correct "Mandays" based on the "cmbProducts" dropdown in "view" sheet. However, I could not get the value of "Cost" from the correct sheet. For example: From cmbProducts dropdown in "view" sheet, I selected Service Delivery, then selected "3" from cmbMandaysAll dropdown (in "view" sheet). The value of the "Cost" that I need to get should be "900" (as seen from Service Delivery sheet, Cost Column (row4). I hope I was able to explain what I mean.

 

[kchiba]

Hi Lui,

I am not sure, when I select 3 for mandays, i get:

SM = 1200 SDD 900 and SD 1350

is this not correct?

 

[lui de sagun]

Hi Lui,

I am not sure, when I select 3 for mandays, i get:

SM = 1200 SDD 900 and SD 1350

is this not correct?

Hi Kanti,

I also get the same results but I only need only 1 value - Meaning if I selected "Service Management" from cmbProducts, I only want to get 1200. else, if I selected "Service Delivery" from cmbProducts, I only want to get 900 and so on.

 

[lui de sagun]

Hi Kanti,

Let me re-phrase this once again - sorry, I am really not good at this:

I also get the same results but I only need only 1 value and not all of them at same time - Meaning if I selected "Service Management" from cmbProducts, I only want to get 1200 and the "Cost" in column Q row13 in "view" sheet will only be 1200. else, if I selected "Service Delivery" from cmbProducts, I only want to get 900, thus the value that will appear in "Cost" should only be 900 and so on.

 

[kchiba]

OK got it,

Look at the attached file, I linked the combobox to Q1 and changed the 3 formulas to suit

 

[lui de sagun]

Thanks very much, again, Kanti! You're the best

 

[kchiba]

Cheers Lui, glad to help