I’m back in the kitchen with another episode of Excel Hash. If you’ve no idea what I’m

talking about don’t worry. It’s just the second

episode in over a year. So Excel Hash is a challenge between me and my online MVP colleagues. Each of us needs to make a video that includes four ingredients. We’re free to use other

ingredients to get the job done, but these four have to be integrated. The ingredients are at least one icon, at least one Excel dynamic array function, Excel’s XOR function, and linked picture. Quite interesting. I think they’re going to

make a nice Excel dashboard. Let’s try it. Let’s assume I’m holding

an Excel conference and a masterclass. I have the list of

people in an Excel table called Names that are attending the masterclass and the conference, and I track them using a

flag of ones and zeros. So if a person has two ones

it means they’re attending both the conference and the masterclass. And when you create an

interactive dashboard that allows me to see who’s

attending the masterclass, the conference, just one of these sessions or both of these sessions. Let’s use the XOR function to figure out who is attending either of these. (bubble sound effect) We get a list of true and false values, so we get a true if we just

have a one on either side. We get a false if we have a one for both. Now if you’re familiar

with Excel’s logical test you know that any

non-zero number is a true. So in this case we don’t

need to say equals one. To turn these into ones and zeros let’s add a negation here. Let’s use a similar logic to calculate who’s attending both sessions. Let’s go and set up our dashboard. On the dashboard page I

already have a dropdown for the different sessions

that I’d like to track. Let’s use the filter function together with the XLOOKUP function

to get the list of people attending the session selected. My array is the list of

names from the data tab. For the include argument

I’m going to use xlookup. Lookup value is the value

selected in the dropdown. Lookup array are the headers in the table. And the return array is a dataset here. Remember, any non-zero value is a true. Filter is going to return the true values, close bracket two times,

second ingredient is used. A dynamic array function, and we’ve even combined it

with the XLOOKUP function. Let’s add an index column here. (bubble sound effect) I’m going to use the sequence

function to get this done. Now if I change the selection to both this is a list of people who

are attending both sessions. Now let’s add an icon for

each of these sessions. Under calculation tab

here I’ve already added the icon or the picture

I would like to have. So this one would be for the conference. This one should show up if I just select one from the dropdown. Now let’s add an icon for the masterclass. Let’s use whatever ingredients, insert icons. To make this look better

let’s covert to shape. Let’s give the series a different color. We have one ingredient left. It’s the linked picture. Let’s create that now. I’m going to copy the sell,

go to the dashboard side, paste, linked picture. The picture currently is not dynamic. It’s just showing me what is in the cell. So in cell A4 I have

that conference picture. In cell A5 I have award art. I want to make this dynamic,

so I need to work with names. Let’s go back to the calculation sheet. I’ve already given names to these cells. You can see them in the name box here. The name has to be identical to my dropdown that I have here. If you’re using spaces you can

use underscores in the names. You just have to work with

the substitute function later on in name manager. As a last step to make this dynamic, let’s go to name manager

and create a dynamic name. We’re going to use indirect. Since our names are identical we’re going to reference

our dropdown cell. Now on the dashboard side

instead of referencing to a specific cell we’re

going to use the new name. If I switch this to one the icon changes. Both, these are the

people that are attending both the conference and the masterclass. That would also be nice

to add a chart to this. Let’s add the calculations

we need for it right here. This is the total number

of people attending. And this is the number of

people attending the session we’ve selected from the dropdown. Let’s finalize the chart. Now if you switch our view to conference, this is the number of people

attending the conference. And the number of people

attending just one session is 29. So there it is. All ingredients together

in an integrated solution. We even made it spicer by

adding in a dash of XLOOKUP. Now here’s where I need a favor from you. Watch all the videos in

the Excel Hash play list. You’ll find the link below

and in the cards provided. Then vote for your favorite. I’ll see you in the next video. (upbeat music)

Here is the first Like even before watching because I know that it is gonna be great as usual. Thanks. 👍

Next level

wowwwww

Excel became so much easier with Dynamic Arrays

Hello,

I'd like to ask you about your nationality

If it is still available.

Many thanks for your great videos. But I have been struggling with something in Excel sheet and until know I couldn't get a solution and I am doing it manually. Let me give you and example. In the excel sheet I got so many activities. Let's say five activities, but the there are 30 description of the same of those five activities. So basically I want to sperate or get each one activity according their description. Such as;

Description Activity

1_ good A1

2- good

3- bad. A2

4- bad

Drag or put each activity according to the different description of the activity.

Thanks in advance. If you didn't get my point. I will like to inform you in the Skype what I mean exactly

Thanks my best teacher

Beauty with Brain, is reality after watching your videos. Thank you! God Bless you!

Wonderful..!! Great idea… 💡

Thanks for sharing !

Waooo interesting and innovative

Convert-to-Shape, Named Range, Doulbe-Unary … Brilliant solution to all these weird ingredients.

Soooo nice! Thanks for playing Excel Hash!

Very cool Leila! I love the use of XLOOKUP in FILTER. Awesome! 🙌

Great video Leila!!!! Sorry I could not do it this time : ( I can't wait till next year!

Feel free to download the workbook from the description of the video and don't forget to vote. Thank you.

This is an action- and feature-packed quick snack with an exotic touch. Love it!

Can’t wait for the dynamic array functions (new calc engine) to come to us O365 Outsiders!

That’s my New Years wish list no. 1 item, right there.

Very very good the winner

perfect as always 🙂

Convert to shape was really cool. Thanks, Excel hash is really challenging and fun. Thanks from you and all of MVPs member in this challenge.

How can we know the result of votes?

love you!

Love the use of XLOOKUP, Leila!

very simple and clean presentation dashboard, as usual love the use of formulas…

Thanks 🙏🙏🙏

I think Leila is trying to sway the judges by dropping XLOOKUP in there 🤣. Good stuff

😍

Leila you're great

Nice teaching mam, you are best

Thanks Leila! Excel Hash 2019. Time flies.

. Sweet & Simple

What you cooked is delicious

Very nice indeed. Thanks so much. But when are we lesser mortals (with regular 365) getting the FILTER function and XLOOKUP????

Wow, great video Leila. The kitchen suits you :). Seriously very professional, well explained and appreciated!

Hi Leila.. I like it.. very clean and neat.. useful too. Thanks for sharing your tips and tricks (convert icon to shape, FILTER with XLOOKUP, INDIRECT in a named range to make the image link dynamic).. all great. I give it a thumbs up!

I am totally confused. I just updated my Excel on Iphone and most of new formulas are there unique filter inex. I also ran update on my desktop 365 montly and STILL no new formulas? Can ANYONE please give me an update on what Microsoft is doing?

Beautiful Dashboard … Thanks for the share.

These above functions will work on 2016??????

Very nicely done. Great job!

Crikey! A whirlwind of Excel goodness.

Seems the link to vote is not working. Pls check it out. Thanks

So many ovens in this PRO-kitchen 😋

Each Excel-project should be rewarded with a delicious meal ✨

Amazing…its so clear…you make it seem easy and flawless…5*

**for you. I have been struggling with something though..its a little bit off this topic. Is it really possible to print a document containing unique code/numbers for future reference in Excel without having to use VBAGreat video Leila!

Awesome 🙂 Thank you, Leila!

Can someone explain me what’s going on with the SEQUENCE(COUNTA(#)) function ? Can’t reproduce it

Too fast

fantastic

Awesome video. Really helpful. Thank you for preparing such kind of videos

💚💛💜💙

Nice video 👍

Great ideas! Thanks for sharing. Maybe a sort function before filter would make the data even better displayed, in alphabetical order

How to hide The Digits After decimal in a cell with the help of format cell. For Example, if I have a value of 45.56 Now I want to hide only .56 How it will be possible.

marry me and i will be your dish-washer

Ms gharani, you added a minus before XOR. That makes it just an OR. Should we consider it cheating? In your defense, there is not much practical application to a XOR function. Lets see what Bill Jelen comes up with. He seems like a guy with tricks.

(But you are a better instructor anyway!)

Great Video Leila, thank you 🤗

Just looked at my Excel and the dynamic array functions are finally there. Hurray! 🥳 Now to wait for XLOOKUP…

Luv u leila. R u married?

Unfortunately, some of the function presented here are not available to most O365 users. I am talking about XLOOKUP and FILTER, this should have been clarified at the beginning.

Lovely.. I Love the use of XLOOKUP

Thanks so much Leila. Beautiful and very intelligent teacher.

i suddenly realized my own inadequacy…

Nice and Clean 🙂

although all other videos are made with the same concept , but yours is the most elegant, simple and efficient. … have voted for you.

Great tutorial Leila ! if the filter and xlookup is not available in older version, is there any useful formula can be used to replace in this case to index the name of attendance ? Or we just do it by an "old traditional" way ? Thanks !

Liked the way you illustrate. And Voted !!