multiple dropdown lists with same data

[SiegGreeff]

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.

 

[David Evans]

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

 

[Nebu]

Hi:

Is this what you are looking for.

Thanks

 

[SiegGreeff]

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

 

[Biigoz]

In regards to your solution, nebu, how did you get K1 to return the row value of the selected cell?

 

[Nebu]

@Biigoz, I have used the following code to get the active cell row.

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

 

[Biigoz]

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!

 

[nehagudi]

Hi:

Is this what you are looking for.

Thanks

How can the drop down list be made searchable?

 

[ktg0011]

@Biigoz, I have used the following code to get the active cell row.

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.

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[breeckipp]

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???

 

[Nebu]

Absolutely!

Find the attached.

Thanks

 

[Indra9914]

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

 

[breeckipp]

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.

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[deny]

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!

 

[Philip Jacob]

Hi:

Is this what you are looking for.

Thanks

Nebu, You are a hero.

 

[Nebu]

Hi:

Please find the attached.

Thanks

 

[HFX1279]

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!

 

[HFX1279]

Dear Nebu,

Hope you can help me, really need the spreadsheet, thanks for your time again

 

[Jinali]

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

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

 

[vletm]

Jinali
You should open a new case.
( and use those code-tags as written )

 

[Robyn1]

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?

 

[emmanuelalbar]

@Biigoz, I have used the following code to get the active cell row.

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.