วีดีโอ

I feel your pain! I've imported some pretty large datasets into MySQL and it's unbearable sometimes 😒 Nice trick though, I'll have to try that plugin

@@sheetskunk I can't take credit for it. I found it on a forum, maybe stack overflow, while searching for a solution. But it was easy to do even for a relative SQL beginner like me.

@jasongins either way, it sounds pretty useful 👍

Yikes, that’s frustrating. Have you tried any of the options I listed? Safe mode, DDE option? Or does it freeze before you can even attempt?

Hmm, I think I understand what you're asking. I'd probably have to look at it to really give you better advice, but you could apply some conditional formatting. There's a lot of ways you could do it. For instance, you could include a column which references the status of a transfer (with checkboxes or True/False). If you transferred an employee from the old sheet to the new sheet, you can check off the box in the cell for that row. Then set up conditional formatting rules for the appropriate fields to automatically remove colors when the box is unchecked, representing new employee data. Does that make sense?

Easier? Maybe. It depends on the situation. In my example, CHOOSE would work well and be less lengthy. SWITCH is just another option for when CHOOSE can't do the same alone. You're right, that you could wrap CHOOSE in an IFERROR as a catch-all. But CHOOSE also relies on an index number to determine the return. So if I didn't end up using a five-star rating, or the employee tier, and instead used some kind of text alternative, then you'd have to include something like a MATCH function for the returns. At that point, I'd probably prefer to go with SWITCH. But great point!

In a way, yes. But it is very limited! The values/result in your array would have to match exactly to the expression to return the results in a single column. However, you can get around it with other functions, but Switch isn't necessarily built for it. In most cases where I've used it, Switch only correctly evaluates the first element of the array. I could be wrong, but I can't think of a way to make it work without using some combination of alternative functions.

No problem!

Thank you!

Great point! It's a nice added feature, but shouldn't be seen as a replacement to pivot tables. An example of its true benefit is as you suggested.

Hmm, that's strange. Well, instead of shift or control, you can select the range from a single tab, then enter a comma. After the comma, select the range from another tab. For more tabs, just repeat the process.

Absolutely! If you're referring to the function selection, one way to do it would be to set up a drop-down list. So, if your drop-down selection is "Average", the corresponding cell would produce the number 1. When you create your SUBTOTAL formula, instead of inputting a static number, simply reference the cell that is dependent on the drop-down. If you meant making the actual range dynamic, you can do that as well. However, it depends on the situation. You could set up a table if you're just talking about including new rows to the data. Or if it's separate column indexes, you could do something similar to the example of using a drop-down. But instead of selecting a different function, you could select a different table column to subtotal. It all depends on what you're trying to do. But it is possible.

Nothing wrong with that! It’s nice having options 😀

Hi! Yes, you can do this. I have another video that somewhat breaks this down with one sheet, but you can adjust the formula to include multiple. The video also tells you how to make it filter down and change the results as you type. Here's the video 👉 th-cam.com/video/89QAQG9kR3M/w-d-xo.htmlsi=mJa7u6U8LeGJAl7R But as a quick example, to filter on multiple sets of data, your formula would look something like this: =FILTER(VSTACK(Table1,Table2),ISNUMBER(SEARCH(Sheet2!$B$1,CONCATENATE(INDEX(VSTACK(Table1,Table2),0,1),INDEX(VSTACK(Table1,Table2),0,2),INDEX(VSTACK(Table1,Table2),0,3))))) Although in this formula, I'm only combining 2 sheets (or tables), and specifying the first 3 columns to search on. And cell B1 is where I would enter the text to search on. Hope that helps!

@@sheetskunk That formula did the trick. Now searching across 3 pages. Thank you so much.

That’s great! Glad it worked out 💪

I appreciate it! Thank you 😊

Preach. I tell people that if you're not using tables to manage your data, you're doing it wrong. Structured references are so much better to use in formulas.

Hi, I'm not sure if I'm understanding your question. If it's a simple problem of multiplying 5 and 7 in a single cell, you would just type =5*7 and your output would show the number 35. Does this answer your question?

OK, inexplicably, my insider subscription was not active on my primary computer - enablement resolved this...thanks!

Yeah the scan function is better served in other situations. I just chose a simple addition example to show how the scan/lambda functions are formatted and operate.

Thanks!

Glad I could help!

Thank you so much! 😊. It really is a feature I’ve been begging for and so glad it’s finally here.

I’m so glad you like it! 👍

I'm sorry about that! I've been trying to be more conscious of how well everyone can see what I'm doing through each video. Unfortunately, TH-cam Editor doesn't give me the option to zoom in after it's been published. But if you need any clarification on any of the steps, just ask! ** I've considered posting a video update with the worksheet steps zoomed in. So if I do that, I'll be sure to update the description to include the link.

Good catch! That would certainly make it faster 👍

Sorry about that. I’ll keep that in mind for future videos. Thanks for letting me know!

I’m pretty sure it’s a specific feature to Excel on the web. I’ve tried activating it on desktop but it doesn’t look the same

Hi! Unfortunately, stand alone licenses of Office that didn’t include LAMBDA at the time of purchase, don’t get updated with new functions/tools. That’s why MS pushes the 365 subscription so any new updates are added. But you can use Excel on the web for free. Otherwise, you can use a different set of functions depending on what you’re trying to do.