Excel selecting worksheets in a form/dashboard | Excel forum

Resolved · Low Priority · Version 2010

Thu 16 Feb 2017, 09:28 Delegate Ian said...

Ian has attended:
Excel Advanced course

Excel selecting worksheets in a form/dashboard

I am setting up a personal budget/finance management workbook, I have multiple sheets, 1 for each seperate account and a front sheet which I want to setup as a dashboard.

on the dashboard I would like to setup a form and bring up pivot tables/charts acording to a single field identifying the sheet (e.g. if I select sheet 1 then the form and pivot chart draws data from sheet 1 only, sheet 2 draws data from sheet 2 only and so on.)

I would also like to add to and make changes to the selected sheet through this dashboard.

Please advise how I can make the necessary connections.

Kind Regards
Ian

Thu 16 Feb 2017, 09:43 Edited on Thu 16 Feb 2017, 09:44 Trainer Jens said...

RE: Excel selecting worksheets in a form/dashboard

Hi Ian,

Thank you for the forum question.

It is not a easy task. Excel likes one list and not multiple sheets.

You can create dashboards from multiple sheets.

I am running STL's Dashboard & Business Intelligence course and Excel Advanced Dashboard course and have experience building many dashboards.

If you have the source data in many sheets you have a couple of options. You can write a union query in MS Query, but then you will need knowledge of SQL or you can lookup the data in the multiple sheets using a combination of the Indirect, Address, Index & Match functions.

If you create one list with the source data everything is much more simple and your dashboard will be much faster and easier to update.

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

Thu 16 Feb 2017, 10:59 Delegate Ian said...

RE: Excel selecting worksheets in a form/dashboard

Hi Jens

Thats great, thanks for your reply.

I have thought of combining all of the sheets and adding a column stating the account the transaction is applicable to, I have used offset and vlookups to fill a summary box for current balances, (using a count formula to set the row to which the offset command refers) how do I then find the correct row for a single account where I cant simply count down the list?

This is my first attempt at a dashboard, its proving to be a very interesting academic challenge for me.

Kind Regards
Ian

Thu 16 Feb 2017, 11:16 Edited on Thu 16 Feb 2017, 13:51 Trainer Jens said...

RE: Excel selecting worksheets in a form/dashboard

Hi Ian,

To create a dashboard can be a challenging, creative and fun task in Excel.

If you use the Offset you can use the Match function to find the row number for a specific account. In the Offset function instead of a specific row number type the Match function.

The Match function needs 3 information. Lookup_value which is a unique ID for your accounts (it could just be the name of the account, if all your accounts are unique).

The Lookup_array is the whole range, where you have your account names. In match_type type 0. The zero will tell the match function that you are looking for a perfect match.

I hope this can help you in the right direction.

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