everydayExcel Times

Use MicrosoftÂ® Excel Differently to Gain a Productive Edge

Issue - Aug 2010

Learn How To Be INDIRECT

One of our participants from the finance department of a well-known
American retailer owns a workbook containing the budget numbers of the
divisions in the company. Just like many others, the budget numbers are
organised with each worksheet storing the budget numbers of a division.
She has to spent 1.5 hour and use a macro to upload the budget numbers
into the Hyperion Financial Management (HFM) System, which then takes
another 1.5 hour to process the budget numbers before the consolidated
numbers can be viewed in the system.

During our course, she asked if there was a faster way to do this
in MS Excel, and a few formulas were recommended to her. One of them
is the INDIRECT formula which is used to convert a text formatted range
into a valid Excel range. INDIRECT looks like a pretty useless formula,
doesn't it?

If you have attended one of our courses, you will know that the
value of the formula is not determined by this definition. Using the
INDIRECT formula, we can present the worksheet name and the range
separately in two different cells and have them combined together using
the INDIRECT formula to become a valid range. With this approach, she
is able to consolidate all the divisions' budget numbers in the
different worksheets into one in a breeze without using Copy and Paste,
and then use the pivot table to present the consolidated numbers. This
set up allows her to consolidate the number in less than 0.5 hour,
less than 1/6 of the time she currently spend using HFM!

Wish to know how this is done? Sign up for our
Dynamic Real-TIme Forecasting with Excel
course and be amazed by our
solution. The pre-requisite for this course is a strong understanding
in VLOOKUP formula and Pivot Table. If you are not ready, attend our
foundational
Excel
course which will show you how we apply the functions
and formulas to real-life business problems.

Subscribe to our newsletter

If you received this newsletter from a friend and would like to be included in our mailing list, please go to our Excel Today's page.

Follow us at Facebook, LinkedIn

If you have a facebook account, you can like us at facebook or linkedln. From facebook or LinkedIn, you will be notified on mini cases and ideas how we use Excel on a day to day basis. These tips may not be published in our newsletter.

Received this newsletter from a friend? You can request to be included in our mailing list by signing up at our Excel Today's page. If you have friends whom you think might be interested in this newsletter, feel free to send it to them.