Dynamic Arrays and Lookup Tables

Подписаться 90 тыс.

Просмотров 9 тыс.

50% 1

It's important to make your Excel applications more robust and Dynamic Array formulas and Tables can help
File I used:
aasolutions.sharepoint.com/:f...
Free Great 48 Access Analytic Toolbar
accessanalytic.com.au/toolbar/
Link to my Autocorrect video:
• Faster Excel formula w...
Connect with me
wyn.bio.link/
accessanalytic.com.au/blog/

Хобби

Опубликовано:

11 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 36

@GrainneDuggan_Excel 2 года назад

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

@AccessAnalytic 2 года назад

You’re welcome Grainne

@ziggle314 2 года назад

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

@AccessAnalytic 2 года назад

Cheers ziggle314

@davenicholls892 2 года назад

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!

@AccessAnalytic 2 года назад

Good stuff Dave!

@danjarupath 2 года назад

What is that "ACCESS ANALYTICS RIBBON 🎀🎀"!!!!????? AMAZING 🤩🤩🤩🤩

@AccessAnalytic 2 года назад

Grab yourself a free copy 😁

@user-jp5lb1kq3x 2 года назад

really enjoy this video

@AccessAnalytic 2 года назад

Thank you

@naiduvikas202 2 года назад

Great stuff 👏👏👏👏👏

@AccessAnalytic 2 года назад

Thanks

@FRANKWHITE1996 2 года назад

great content

@AccessAnalytic 2 года назад

Cheers

@heshamfouad8886 2 года назад

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

@AccessAnalytic 2 года назад

I used the custom ui tool: it was 10 years ago so things may have changed 😆 rlbcontractor.com/using-a-microsoft-office-custom-ribbon-ui-editor

@stephanweaver1960 2 года назад

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

@AccessAnalytic 2 года назад

Not sure on the “nasty” but thanks I think 😀

@DeputyBurbank 2 года назад

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.

@AccessAnalytic 2 года назад

I’d suggest posting your question here aka.ms/excelcommunity Sounds a bit like using Solver: support.microsoft.com/en-us/office/define-and-solve-a-problem-by-using-solver-5d1a388f-079d-43ac-a7eb-f63e45925040

@jorisvanh.9274 Год назад

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)

@AccessAnalytic Год назад

In Power Query you could automatically append any missing codes to the dimension table but I’m not sure it would help as you still wouldn’t have any description of what that code was.

@teoxengineer 2 года назад

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

@AccessAnalytic 2 года назад

Sure can Emre, I have a video here ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-dNXGODP0oOM.html

@RonDavidowicz 2 года назад

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.

@AccessAnalytic 2 года назад

Here’s my autocorrect video ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-3xK_e7HQgSE.html I prefer to link to the actual table, that way it doesn’t matter where the table is moved to. Liking to a cell can break if someone deletes the row or column the hyperlink references

@RonDavidowicz 2 года назад

@@AccessAnalytic thanks for the link to your previous video.

@AccessAnalytic 2 года назад

No worries Ron

@drsteele4749 2 года назад

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.

@AccessAnalytic 2 года назад

Yep indeed. I have a video planned to look at a workaround