Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

dashboards

ResolvedVersion 2010

Dashboards

Use of RIC and Indirect formulas

RE: Dashboards

Hi Khushbu,

Thank you for the forum question.

The Indirect function is very useful for dashboards, if the source data are organised in multiple ranges or in multiple sheets.

The Indirect function will return the reference specified by a text string.

=Indirect("A1"&"!"&"A1:C20")

If you have the word "January" in the cell A1 the Indirect function will refer to the range A1:C20 on he January sheet.

I have attached a workbook with an example. There is a dashboard showing expenses. The source sheet is a single flat list with all the source data. There is 4 sheets where the expenses is broken down for each department.

If you have a look at column B and column F on the Helper sheet you will find I have used the Indirect function together with other function to get the data used on the dashboard.

I hope this can help you.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

indirect.xlsx

Mon 4 Jul 2016: Automatically marked as resolved.

Excel tip:

Printing spreadsheets without opening them first

Here's a fast way of printing a spreadsheet from Windows Explorer/My Computer.

Go to the location where the spreadsheet is saved on your computer, then right-click on the icon next to the document and select Print from the menu.

The spreadsheet will automatically open, print and close itself.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.