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
Rescue oddly shaped data – Battle between Formulas, VBA and Power Query

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
Formula Forensics 042: Reverse Text – A Formulaic Solution

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
Formula Forensics No. 041 – Convert a Roman Numeral to a Number

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
Formula Forensics No. 039 – Find the Cell Address for a value (2D & 3D Reverse Lookup)

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
Formula Forensics No. 038 – Find Which Worksheet a Max or Min Value is located on

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
Looking up when the data won’t co-operate (case study)

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
3D Max Formula for Excel

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

Sounds intriguing? Read on.

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 »

Free 2014 Calendar, daily planner templates [download]

Published on Jan 2, 2014 in Learn Excel
Free 2014 Calendar, daily planner templates [download]

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.

Click here to know more and join us.

Continue »

2013 Calendar – Excel Template [Downloads]

Published on Dec 26, 2012 in Learn Excel
2013 Calendar – Excel Template [Downloads]

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
Formula Forensic No. 021 – Find the 4th Slash !

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 »

2012 Calendar – Excel Template [Downloads]

Published on Dec 27, 2011 in Learn Excel
2012 Calendar – Excel Template [Downloads]

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.

Go ahead and download the FREE 2012 Calendar and Enjoy.

Continue »

2011 Calendar – Excel Template [Downloads]

Published on Dec 17, 2010 in Learn Excel
2011 Calendar – Excel Template [Downloads]

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)

Go ahead and download the calendar files. Enjoy.

Continue »

How to write 2 Way Lookup Formulas in Excel?

Published on Nov 9, 2010 in Learn Excel
How to write 2 Way Lookup Formulas in 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 »