Tag Archives: INDIRECT

[Video] How to create a dependent dropdown list in #Excel

This is the follow up post from my previous post – How to create a dependent dropdown list in¬†#Excel. As mentioned, it could be easier to illustrate the concept using a video, especially for the use of Dynamic Arrays. Here … Continue reading

Rate this:

Posted in Excel in Action | Tagged , , , , , | Leave a comment

How to create a dependent dropdown list in #Excel

…with and without Dynamic Arrays (which is available to Microsoft 365 users only) Got a question from a friend recently: Is it possible to show a different set of questions in a dropdown list depending on the answer input from … Continue reading

Rate this:

Posted in Excel in Action | Tagged , , , | 2 Comments

#Excel INDIRECT function explained with a use case

There are many #Excel functions that you may not find them useful when you first learn them. Sometimes you may even doubt why there are such functions. INDIRECT should be one of them. ūü§Ē What INDIRECT does? It returns the … Continue reading

Rate this:

Posted in Formula | Tagged | Leave a comment

Extract FirstLast names in CamelCase with #Excel formula

In the previous blog post, we see how Flash Fill extracts First Name and Last Name from an email address in a format shown above.¬† I’ve also recorded a video for that post. Flash Fill is so smart to detect … Continue reading

Rate this:

Posted in Formula | Tagged , , , , , , | Leave a comment

The INDIRECT trick of using Structured Reference in Conditional Formatting

When setting up conditions for Conditional Formatting in Excel, have you ever tried to set a formula that refers to a Excel Table, but failed?

First of all, why we want to refer to Excel Table in Conditional Formatting? There is one simple answer: To accept new data automatically. 
To use Structure Reference in Conditional Formatting, we need an INDIRECT approach which is discussed here. Continue reading

Rate this:

Posted in Excel Tips | Tagged , , , | 7 Comments

Has Space or NoSpace in worksheet name?

Have you received workbook from others that carries underscore _¬†as if a space in their worksheet names?¬† Did you wonder why people use underscore¬†when we can actually use space in worksheet name? Well… did¬†you know… long long time ago, #Excel … Continue reading

Rate this:

Posted in Excel Tips, Formula | Tagged , | Leave a comment

The making of an interactive CV in #Excel

What to do with Linked Picture to create something interesting in #Excel? How about an Interactive CV to show off your Excel skills!? First, why? Once upon a time when I updated my CV, I was thinking… Everyone states something … Continue reading

Rate this:

Posted in Excel Tips | Tagged , , , , , , | 5 Comments

How Excel formula can save your time?

Extract number from¬†a text string This post is about two things: How a poor system set up could drive you crazy? Why being good in Excel could save you lots of time in workplace? Another real work example. ¬†I was … Continue reading

Rate this:

Posted in Formula | Tagged , , , , , , | 4 Comments

3D VLOOKUP – Perform VLOOKUP from more than one table

Pls have your 3D glasses ready… only if you are going to watch a 3D movie. ¬†You don’t need it for a 3D VLOOKUP. We talked about how to do a 2D VLOOKUP¬†by using MATCH together with VLOOKUP. ¬†With that … Continue reading

Rate this:

Posted in Formula | Tagged , , , | 3 Comments

Calculate number of a specific day between two dates

Answer: =SUMPRODUCT(–(TEXT(ROW(INDIRECT(B1&”:”&B2)),”DDDD”)=”Sunday”)) ‘where B1 is start date; B2 is end date. Excuse me?¬†What it says?

Rate this:

Posted in Excel Tips, Formula | Tagged , , , | 16 Comments