You thought of summarizing the details using Excel so exported the data in the form of excel file. Basically INDIRECT function treated a text string in cell A1 as cell reference i.e. In other words, in our example: =INDIRECT(A1) is equal to =B3 In above example things are simple.
My initial impression was that customer’s name will be in one table on one worksheet. We just put B3 in one cell and used INDIRECT function to convert that text to real cell reference.
In this case, the workbook calculation was set to Automatic on all the machines – that’s the first thing we checked.
To check the setting, click the Formulas tab on the Excel Ribbon, and click the Calculate Options drop down.
If you’re a complete nerd, constantly looking for the more elegant solution, you can use the COLUMN function.
A workbook of mine that worked fine for several years, when using Excel 2003, suddenly refused to update all the formulas, after a switch to Excel 2010. When someone tells you that formulas aren’t calculating, it’s probably because the Calculation setting has been changed to Manual, instead of Automatic.
This is the feeling you are likely to get when manually adjusting VLOOKUP formulas.
The Problem: In this example, I am trying to get some information on each Product ID.
Most of the time businesses are managed using specialized software but when it comes to a final touch in reporting the data is exported to Excel where calculations and everything is done much more easily.
I have a mapping table that will get me the Description, Segment and Price for each Product ID. If you look in the formula bar in this screenshot, you’ll notice I have a standard VLOOKUP formula.
This is because I need to change the column index in the VLOOKUP in order to reference a different part of my mapping table.
Pivot tables will not only save us from writing different formulas but also make it dynamic and we can extract different types of reports and not stuck with just one format.
In short, pivot tables make almost everything crazy fast!