• 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.

offset and (maybe) indirect

fatherofmany

fatherofmany

New Member
Hello,
I have attached a spreadsheet I am working on to create a searchable drop down menu. I do have the searchable information on another tab and it is able to pull over to the drop down menu (FMR L7). The problem I am having is that I would like to have different databases on different tabs and be able to choose which database to pull from (FMR K7). Below are the formulas I am using currently to get the searchable drop down menu.

=IF(ISNUMBER(SEARCH($E$2,PVC!B2)),MAX($A$1:A1)+1,0)
=IFERROR(VLOOKUP(ROWS($D$2:D2),$A$2:$B$1239,2,0),"")
=OFFSET($D$2,,,COUNTIF($D$2:$D$1245,"?*"))

The offset formula is a named range (plastic) but I am not able to use the indirect function and I would assume that it would be because I am seeking a value and not a location. Any help or advice would be greatly appreciated. I am very new to utilizing forums in this manner so if I have made a faux pas, please forgive me. Thank you in advance for any assistance.

TK
 

Attachments

  • ! Test Field Material Request 4.18.16 Deleting.xlsx
    165.6 KB · Views: 8
Thank you Hui for your response. I will look at your suggested thread. I did try to perform a search but was not quite finding what I needed. I appreciate you taking the time and I appreciate this resource.
 
Don't be afraid to start a conversation with Jeff Weir if you get stuck
 
For this challenge, I'd probably use the CHOOSE function combined with an INDEX/MATCH combo to feed it the right choices, and some Excel Tables to keep everything dynamic.

Here's how the formula that feeds the DV would look:
=CHOOSE(INDEX(Materials[Order],MATCH(FMR!K7,tblMaterials,0)),PVC,CPVC,PVDF_FLARE,Carbon_Steel,Hardware)

See attached. I don't have time to explain this I'm afraid, but suggest you google CHOOSE and also check out my comment on CHOOSE here:

http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/#comment-472410

Also, I wrote some guest posts over at CHandoo's blog some time back that might be of interest.

The first shows how to use VBA to reset downstream choices if the upstream ones change:
http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/

The second shows how to stop users changing the upstream choice unless they have manually erased the downstream choice, which is probabyl a better way to go:
http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/

Both those articles are based around the situation where all your subchoices are in one sheet, rather than in different sheets like you have.
 

Attachments

  • CHOOSE used in dynamic dropdown_20160427.xlsx
    71.5 KB · Views: 5
Thank you Jeff. I appreciate your feedback and I am working through all the information to update my spreadsheet as you suggested.
 
Hello Jeff,
I would like to ask a follow-up question on whether I could utilize any type of searching function in the drop down list. I had originally started going that route because each of my "table" lists could be in the hundreds and a few over a thousand. If there is not a reasonable way to incorporate searches then perhaps I could add a "Size" column to help shrink the results list to a more manageable number. I will start to look at my options but any feedback you could provide would be greatly appreciated.

Thank you,
TK
 
You must log in or register to reply here.
Back
Top