Search

# All articles with 'INDIRECT()' Tag

## Rescue oddly shaped data – Battle between Formulas, VBA and Power Query

Published on Apr 11, 2018 in Learn Excel, Power Query, VBA Macros

Let’s say you have data like this in a spreadsheet. Don’t roll your eyes, I am 102% sure, right at this moment, someone is (ab)using Excel to create similar messy data.

How do you reshape it to one column?

You could use formulas, VBA or Power Query. Let’s examine all these methods to see what is best. All these methods assume your data is in a range aptly named myrange.

Continue »

## Formula Forensics 042: Reverse Text – A Formulaic Solution

Published on Jan 4, 2017 in Formula Forensics, Huis, Posts by Hui

Use the new Textjoin() function to reverse a string of characters suing a formulaic solution.

Continue »

## Formula Forensics No. 041 – Convert a Roman Numeral to a Number

Published on Sep 14, 2016 in Excel Howtos, Formula Forensics, Huis, Posts by Hui

Learn how to convert a Roman Numeral to a Number using this nifty formula. No VBA required.

Continue »

## Formula Forensics No. 039 – Find the Cell Address for a value (2D & 3D Reverse Lookup)

Published on Oct 15, 2015 in Formula Forensics, Huis, Posts by Hui

Find the Cell Address for a value from a 2D or 3D Range
(2D & 3D Reverse Lookup)

Continue »

## Formula Forensics No. 038 – Find Which Worksheet a Max or Min Value is located on

Published on Oct 14, 2015 in Formula Forensics, Huis, Posts by Hui

Learn how to find which worksheet a max or min value occurs on using this neat formula

Continue »

## Looking up when the data won’t co-operate (case study)

Published on Nov 4, 2014 in Excel Challenges, Excel Howtos

Occasionally we deal with data that is so uncooperative that we might as well give up and go back to calculators & ledger books.

Recently I found myself in such a situation and learned something new.

Introducing … data that won’t play nice

Drum roll please. Here is a data-set that I got from somewhere.

The problem – build a lookup formula

And the problem. Oh, simple. Write a lookup formula to find how many customer walk-ins we have on any given day.

But how?

Continue »

## 3D Max Formula for Excel

Published on Sep 9, 2014 in Excel Howtos, Learn Excel

We all know about the MAX formula. But do you know about 3D Max?

Lets say you are the sales analyst at ACME Inc. Your job involves drinking copious amounts of coffee, creating awesome reports & helping ACME Inc. beat competition.

For one of the reports, you need to find out the maximum transactions by any customer across months.

But there is a twist in the story.

Your data is not in one sheet. It is in multiple sheets, one per month.

Continue »

Published on Jan 2, 2014 in Learn Excel

Here is a New year gift to all our readers – free 2014 Excel Calendar & daily planner Template.

This calender has,

• One page full calendar with notes, in 4 different color schemes
• Daily event planner & tracker
• 1 Mini calendar
• Monthly calendar (prints to 12 pages)
• Works for any year, just change year in Full tab.
Continue »

## Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 2.)

Published on Jul 17, 2013 in Formula Challenges, Posts by Jeff

Today we look at the third place winner from our inaugural Formula Challenge, a neat solution provided by Sajan.

Continue »

## Excel Links – from USA edition

Published on May 8, 2013 in excel links

Hello there,

My travel to US was fantastic and fun. Many thanks to Jocelyn & Rob Collie, who found a nice apartment for us and made sure we are settled down in almost no time. Weather has been excellent so far too with lots of sunshine and blue skies. We went to a nearby park the other day and kids really loved walking on trials and exploring.

### Gentle reminder

Today is last day to sign-up for my USA Masterclass

Today is last day to join my USA Advanced Excel & Dashboards masterclass with early bird discount. If you live near Chicago, Washington DC or Columbus and love Chandoo.org style of teaching, then you will benefit alot from my masterclass. We have very few spots left in Chicago & Columbus. So go ahead and book yours today and save \$200.

Continue »

Published on Dec 26, 2012 in Learn Excel

Here is a New year gift to all our readers – free 2013 Excel Calendar Template.

This calender has,

• One page full calendar with notes, in 4 different color schemes
• Daily event planner & tracker
• 1 Mini calendar
• Monthly calendar (prints to 12 pages)
• Works for any year, just change year in Full tab.
Continue »

## Formula Forensic No. 021 – Find the 4th Slash !

Published on May 17, 2012 in Formula Forensics, Huis, Posts by Hui

No, Not that Slash !

How do I find the 4th Slash in a text string?
Today at Formula Forensics we take a look at 4 different methods.

Continue »

Published on Dec 27, 2011 in Learn Excel

Here is a new year gift to all our readers – free 2012 Excel Calendar Template.

This calender has,

» One page full calendar with notes, in 4 different color schemes
» 1 Mini calendar
» Monthly calendar (prints to 12 pages)
» Works for any year, just change year in Full tab.

Continue »

Published on Dec 17, 2010 in Learn Excel

Here is a 2011 new year gift to all our readers – a free 2011 calendar template.

(a little secret: just change the year in worksheet “Full” from 2011 to 2012 to get the next year’s calendar. It works all the way up to year 9999)

You can add notes to individual dates or complete month using the excel template very easily. There are 6 different calendar templates in the download file,

* 4 Yearly Calendar Templates with different color schemes.
* 1 Mini Calendar
* 1 Monthly Calendar (prints in 12 pages)

Continue »

## How to write 2 Way Lookup Formulas in Excel?

Published on Nov 9, 2010 in Learn Excel

Situation

So far we have seen what VLOOKUP formula is and how to put it to some nifty uses. Today, we will go one step further and learn how to do 2 Way Lookups.

What is a 2 Way Lookup?

Lookup is when you find a value in one column and get the corresponding element from other columns. 2 Way Lookup is when you lookup value at the interesection corresponding to a given row & column values.

For example, assuming you have data like below, and you want to findout how much sales Joseph made in month of March, you are essentially doing a 2 way lookup.

Read more to find how to solve this.

Continue »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.