Single Source Searchable Dropdown on Every Row

[ktg0011]

Hi all,

I cannot seem to get this right, and I've spent too much time on it already. I'm so behind on my work now. I would like for this document to have the following:

• Every row in 'Customer Name' column on Sheet 1 to a searchable dropdown that is populated by the 'Customer Name' column on Sheet 2. Very similar to the spreadsheet posted by Nebu in this thread. (I started there first)

http://forum.chandoo.org/threads/multiple-dropdown-lists-with-same-data.25687/​

Also, any helpful hints you may have for the following:

• Specific Sheet 1 Columns to flow into Columns on Sheet 3. I know how to do this, but I want to ensure it's going to work with non-manually entered data.
• Pivot Table with Pivot Chart on Sheet 4 based on data in Sheet 1 and 3.

Apparently I am not as good with Excel as I thought.
I would truly appreciate any guidance or advice!

-Kate

 

[Nebu]

Hi:

I have attached the solution in the following link
http://forum.chandoo.org/threads/multiple-dropdown-lists-with-same-data.25687/
Thanks

 

[ktg0011]

You are FANTASTIC! Thank you so much

 

[ktg0011]

Hi:

I have attached the solution in the following link
http://forum.chandoo.org/threads/multiple-dropdown-lists-with-same-data.25687/
Thanks

It's no longer working. Once I made the data on Sheet 1 into a table, the drop downs show all names.

 

[p45cal]

It continues to work here after conversion of the data on sheet1 to a Table. Attach your file.

edit post posting: I made a mistake, I hadn't enabled macros.
I think the behaviours you're seeing is by design. You need to start typing something in the cell before using the dropdown.

 

[r2c2]

Excellent solution @Nebu

@ktg0011
Since you are going to use VBA, I suggest checking out Active-x combo box. This offers the auto-suggest behavior by default and can reduce the amount of extra formula based calc. For example, in the attached workbook, you can find something that can work for you. When you double click in the input range, combo box shows up there and gathers user input. Once you finish typing the cell underneath gets updated and combobox becomes invisible. Give it a try.

 

[ktg0011]

Excellent solution @Nebu

@ktg0011
Since you are going to use VBA, I suggest checking out Active-x combo box. This offers the auto-suggest behavior by default and can reduce the amount of extra formula based calc. For example, in the attached workbook, you can find something that can work for you. When you double click in the input range, combo box shows up there and gathers user input. Once you finish typing the cell underneath gets updated and combobox becomes invisible. Give it a try.

Thank you for your suggestion! The auto-suggest feature will be very beneficial. I've attached my revised document, reflecting the intial changes by @Nebu.

I was hoping you may be able to take a look at it to see if there is anything I can do to simplify the flow of data between sheets.

Background:
This spreadsheet will be used by our sales team to report new sales and opportunities to management on a weekly basis.

• Sheet1 will contain all leads, opportunities, and sales from 1/1/16 to present.
• Sheet3 will take the opportunities and sales from the present year and sum the "Amount" and "Invoiced" data based on the Customer Name and Start Date.
• Sheet4 and Sheet5 will compare 2016 vs 2017 sales and Goal vs Actual (Invoiced) Sales, respectively.

Thanks in advance!

 

[Nebu]

Hi:

Sheet 5 where you compare sales done by each individual sales person you can use slicers to select an compare , rather than showing the data for all the sales person at a time.

Thanks

 

[ktg0011]

Hi:

Sheet 5 where you compare sales done by each individual sales person you can use slicers to select an compare , rather than showing the data for all the sales person at a time.

Thanks

Hi! I'd like to track changes made within the sheet; however, I know it isn't possible to do so with a table. Will converting the table on Sheet 1 to range disable the searchable dropdowns? If so, is there a workaround that I can use?

 

[Nebu]

@ktg0011 No, it won't disable the search option. I converted it into a table coz, I was lazy to drag the formulas down

Thanks

 

[ktg0011]

@Nebu haha happens to the best of us. Thanks so much for all of your help