Forum Discussion
Excel on Mac - 16.38 - Drop Down doesn't trigger sheet calculation/formula
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.
mathetes It might be a difference between mac and pc as when I click the down arrow any active entry in the cell gets committed (i.e. entered) and hence IF I had arrray functions it would work for me. Apparently on the mac clicking the down arrow isn't committing the entry and hence not updating the filter. Maybe see if there are any advanced option settings?
You can also use a 'helper' cell to perform the filter. For example make col A wider and in A3 say "Filter By:" and then point the filter function on 'MaterData' to B3.
Not perfect but a work around.
https://www.youtube.com/watch?v=Z-h2UER3b_0&list=PLzHi4u388TnW6Gfr5Fz_uBwZwVwt45Dk8&index=12@mtarler
Leila is working on a Windows machine. I have a Mac. Only difference that I can tell.
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
