Forum Discussion
Excel on Mac - 16.38 - Drop Down doesn't trigger sheet calculation/formula
Looking for assistance. Just updated to 365 16.38 from 2011 on a Mac. I have multiple drop down boxes on a sheet that rely on formulas to allow them to search for characters typed inside them and return a dynamic list based on characters typed inside the box. When clicked, the arrow button on the box is suppose to trigger a sheet calculation to run the formula. My version is not doing this, however, my online research shows it should. Calculation is set to full Auto. Any suggestions?
A sample of my issue is attached. It was written by Leila Gharani and downloaded from YouTube with permission.
11 Replies
Jeff_Sturgeon do you have the new array functions available?? I see that the functions are calling some of those new functions and if they aren't enabled in your version of Excel then it won't work. Simply click on the "Pet Feed" cell (C5) and if the formula starts with something like _xlfn._xlws then it won't work for you. Don't worry, this functionality is still getting rolled out and will get to you soon (and hopefully to me too
NVM - I just saw your video and that isn't the problem
🙂
Nope. Nothing like that.
=FILTER(MasterData!B2:B32,MasterData!A2:A32=Report!B5,"")
This is what populates the formula bar.
Sorry -- but I'm not following what the "issue" is, especially from a spreadsheet that isn't the one actually giving you trouble (or am I misinterpreting?).
Are you saying you couldn't get Leila Gharani's teaching example to work on your system? It does work on mine.
Please explain further. Or perhaps post also the copy of your own spreadsheet.
mathetes What I believe Jeff_Sturgeon is having a problem with is that as he types in the cell a partial name and then clicks the down arrow it isn't filtering to that partial entry and only filters AFTER he hits enter. I don't know of any functionality that would do that. I think you have to ENTER the data in the cell 1st and then the filter will get applied. I would be curious to see this youtube.
What's interesting here is that I actually had downloaded a week or two ago that very same sample worksheet. If you go to YouTube and search for Leila's name (plus Excel, perhaps) you should find it.
In my using the spreadsheet--the one I downloaded as well as the one Jeff posted--I notice the same phenomenon. The filtering isn't as "live" as I would have expected it to be. It works, but only "kinda works"..
I too, like Jeff_Sturgeon , am on a Mac, so maybe that is a factor.
So I'm attaching a spreadsheet of my own creation, one intended to illustrate how a secondary data validation cell can be created based on what's entered in the first....all using the most recent Dynamic Array functions. There's a table of first and last names that can be extended to your heart's content, and you'll see how both the primary and the secondary entry screens have their lists of acceptable answers grow. I don't know if this is a solution for you, Jeff_Sturgeon , but take a look.
I appreciate your time on this. I have attached a 30 sec video of me trying the file I attached earlier.
I have a large spreadsheet that uses the exact concept Leila produced here. I sent you her copy to make it easier for folks to see.
The drop down button (arrow attached to the cell) for the Data Validation used to trigger a sheet calculation, which would run my formula to conduct the search. Now, I can’t get the Data Validation button to trigger a calculation.I have attached a small video screen shot of the issue…Calculation is set to Automatic
