• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

multiple dropdown lists with same data

SiegGreeff

New Member
EXCEL2007. I want to create multiple searchable drop down lists, each on a row below the previous one, on the same worksheet but all using data from the same list. (the list of data is an inventory) and the searchable drop down lists , 22 of them, is on the input screen for an invoice.

So the person needs to select an item from the drop down list [the data from the inventory] then input the price. Then the next item in the next row will be the next searchable drop down list getting the data from the same inventory... and so on and so on.

I have managed to set up a searchable drop down list in the first row WHICH WORKS FINE, but when I copy the drop down list to the next row. it is no longer searchable and I can only get a complete list of all the stock in my inventory. I have used the following formulas:
COUNTIF
OFFSET
IFERROR(VLOOKUP(ROWS .....
IF(ISNUMBER(SEARCH ....... MAX
together with data validation to set up the searchable drop down list.
As you may have noticed I do not have much experience with this side of excel.
 

Attachments

  • MASTER4.xlsm
    59.3 KB · Views: 212
Hi SiegGreeff -

I am not totally understanding your problem but you do have a different Data Validation reference for C11 than for the remaining cells - In C11 your're referring to validation_list, and for C12:C22 its looking at Inventory!$B$3:$B$230

validation_list is based on an offset of Inventory!E2 but I'm not getting what you're doing there ..

D
 
Oh, that is most certainly what I was looking for. It seems that my way to program it was a bit back to front. You have helped me tremendously.

Malaysia implemented GST 1April this year and from 1 October no business are allowed to issue handwritten invoices any longer. All invoices have to be printed via computer. So many businesses are going for expensive accounting software such as SQL accounting. We decided to do it with excel .... its mainly for the invoicing. We do the cashbook with excel as well.

I have studied the way you used the formulas ... its great.

Thank you so much.

Kind regards

Siegfried Greeff
 
In regards to your solution, nebu, how did you get K1 to return the row value of the selected cell?
 
@Biigoz, I have used the following code to get the active cell row.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And (Target.Row > 10 And Target.Row < 33) Then
Sheet1.[K1] = ActiveCell.Row
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 3 And (Target.Row > 10 And Target.Row < 33) Then
Sheet1.[K1] = ActiveCell.Row
End If
End Sub
Thanks
 
You truly are an Excel Ninja, Nebu!

I've been working on a spreadsheet for what seems like months and this will finally put the finishing touches on this bad boy!
 
@Biigoz, I have used the following code to get the active cell row.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And (Target.Row > 10 And Target.Row < 33) Then
Sheet1.[K1] = ActiveCell.Row
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 3 And (Target.Row > 10 And Target.Row < 33) Then
Sheet1.[K1] = ActiveCell.Row
End If
End Sub
Thanks


I have a very similar issue. See attached sheet. I cannot seem to get each individual row to have an independent dropdown. Any help would be greatly appreciated.
 

Attachments

  • Sales Tracker 3.xlsm
    29.6 KB · Views: 72
Hi:

Find the attached.

Thanks

Hi,

Thanks so much!

Instead of the analysis drop-down column being in the "Analysis Key" spreadsheet, is there a way to put the searchable drop-down list in the analysis column that is found in the "Inventory" spreadsheet???
 
Dear Nebu,

can you help me for solving the excel formula problem?

I have arround 700 customer and can be more.
I'm using validating data for make drop down list. But how to make it also searchable inside drop down list? i want put it in BELANJA HARIAN tab, BELANJA MINGGUAN tab and BELANJA BULANAN tab.

Please help me
Regards

Here my file
 

Attachments

  • DATA PENJUALAN + RUMUS (MASTER).xlsx
    359.6 KB · Views: 45
Absolutely!

Find the attached.

Thanks

Hi Nebu,

I have added a few more columns before the analysis column... The searchable component of this drop-down list no longer seems to work... I'm not sure which Macro needs to be changed in order for this to work again.
 

Attachments

  • Inventory Sheet- with drop down analysis.xlsm
    158.8 KB · Views: 39
Hi Nebu,

Can you please help; on Sheet 1 I have 2 Columns A and C that I want to populate as it follows:
- A with searchable drop down lists from sheet 2
- C with searchable drop down lists from sheet 3'

Thank you,
Regards!
 

Attachments

  • Template Fisier Retete Test.xlsx
    58.4 KB · Views: 46
Hi Nebu,

I was wondering if I can get some help from the excel Ninja. I've been working on my spreadsheet forever and still can get it to work. I'm trying to make each drop down list to be searchable from a specific name list on a separate sheet. and also trying to make the list to drop down automatically when you start typing. Thanks in advance!
 

Attachments

  • Import Arrivals.xlsx
    113.7 KB · Views: 24
Hi,

I have the problem with the code. I have create the form and 2 searchable drop down lists (1 is client list, another is Category List). If only one drop down lists is works but add more 1 drop down list, both lists didn't work. Would you please help me to find out problem of the vba code, thank you so much!

The vba code is below

Code:
Private Sub ComboBox1_Change()
    ComboBox1.ListFillRange = "ClientList1"
    Me.ComboBox1.DropDown
End Sub

Sub Bind()
ComboBox1.ListFillRange = "DataSource"
ComboBox2.ListFillRange = "DataSource"
End Sub

Private Sub ComboBox2_Change()
ComboBox2.ListFillRange = "LocalSpec2List1"
Me.ComboBox2.DropDown
End Sub
 

Attachments

  • Job set up_closed_amend form_Testing_vba problem.xlsm
    313.2 KB · Views: 31
Last edited by a moderator:
Hi:

Is this what you are looking for.

Thanks

Hi:

This worked well for me but I can only apply it to one column with multiple rows in my worksheet.

Do you have a solution like this if there are multiple columns per row that need to have a searchable drop down list?
 
@Biigoz, I have used the following code to get the active cell row.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And (Target.Row > 10 And Target.Row < 33) Then
Sheet1.[K1] = ActiveCell.Row
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 3 And (Target.Row > 10 And Target.Row < 33) Then
Sheet1.[K1] = ActiveCell.Row
End If
End Sub
Thanks


Try to put =CELL("row") in K1 of Sheet1 instead og using VBA. It works for me.
 
Back
Top