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