Dynamic Arrays and Lookup Tables

It's important to make your Excel applications more robust and Dynamic Array formulas and Tables can help

File I used:

Free Great 48 Access Analytic Toolbar

Link to my Autocorrect video:

Connect with me

[GrainneDuggan_Excel]

Wow, Wyn so many tips packed in this video. Thanks

[ziggle]

I love "best practices" videos. This one is a gold mine. Thanks Wyn!

[davenicholls]

This is my kind of Excelling, spot on 👏🏼 never thought of using autocorrect for fast formula entry, genius! Off to watch the other video now!

[danjarupath]

What is that "ACCESS ANALYTICS RIBBON

AMAZING 🤩🤩🤩🤩

[heshamfouad]

Nice Video As usual Wyn, keep them going,
In the future may you can have a video on how did you create the Toolbar, as I really want to create one for myself
Thanks in advance

[YouGotPropofol]

Ok so seems to be all experts here and a good place to ask: I have 3 cells, A, B, and C. C=A/B. Simple. However, I'd like A or B to be also solved for any input I put into C. Is there some Excel method where this type of entering 2 parameters solves for a 3rd can be done? I do not know VBA.

[stephanweaver]

nasty. someone else do exactly what I do. You're the you're the first I've seen. I also use the colored font like blue to designate any calculations in a column as opposed to normal black for static or external data. except for the MMM thing - neat

[jorisvanh.]

Thank you for the information. Any chance how to automate adding the new roles to the lookup table? If that lookup table is the one being loaded into PowerPivot, it needs to be up to date because it is the chain between two data tables (who would have an impossible many-to-many relationship without this lookup table)

[teoxengineer]

Can we create a drop down list in single cell to navigate sheets or different tables by using hyperlink function?

[vishnu]

what did you do after clicking "dddl"..the formula doesn't pop-up for me?

[RonDavidowicz]

Great video as always.

Couple of things: 1. can you go over the countif formulas that you brought up with the shortcuts.

2. I think you over complicated the hyperlink formula, why not just refer to the cell in 2nd sheet above the table.

[drsteele]

Wyn, that's a wynner. It's really too bad Tables lack the ability to have spilling in them. It would be easy to just have UNIQUE extract all the Roles or WOs from the Query table and it would be dynamic.