Excel on Mac - 16.38 - Drop Down doesn't trigger sheet calculation/formula

[Jeff_Sturgeon]

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.

[#2]

@Jeff_Sturgeon 

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.

[#3]

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

@mathetes 

[#4]

@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

:)

[#5]

Nope.  Nothing like that.  

=FILTER(MasterData!B2:B32,MasterData!A2:A32=Report!B5,"")

This is what populates the formula bar.

@mtarler 

[#6]

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

[#7]

@Jeff_Sturgeon 

I'm mystified myself....we'll have to wait for a greater expert to come along.

[#8]

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.

@mtarler 

[#9]

@mtarler 

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.

[#10]

@mathetes It products the same issue.  Must click outside the data validation cell and back into it for the sheet calculation to trigger.

Thanks for looking at this.  If you have the exact same issue I have then it might be contributed to the Mac version.  The issue doesn't seem to exist on the Windows version.  

[#11]

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

[#12]

@Jeff_Sturgeon 

And I use that functionality in my own expense (budget) tracking sheet, and have never noticed it as a drawback...."when you don't know what you're missing, you don't miss it."