Creating a dashboard which aggregates data from separate worksheets using dropdown list

It would help if you put some (made-up) data in those sheets, which is representative of the type of values that you have in your real file.

Pete

Hi Pete = I have edited to excel file and added it to my previous comment

Thanks,
Alex

I have set this up in the attached file for you.

I have listed the providers in column M of the Dashboard sheet, and this drives the drop-down in cell B2. I've also changed the order of the providers in each of the subsidiary sheets, to show you that the data does not need to be in the same sequence (though it might be useful for you if it is). I've changed the headings in cells C3:F3 to match the names of the sheets.

Then I put this formula in C4:

=IFERROR(INDEX(INDIRECT("'"&C$3&"'!B:K"),MATCH($B$2,INDIRECT("'"&C$3&"'!a:a"),0),ROWS($1:1)),"")

and this can be copied across and down as required. Note that you need to apply a percentage format to rows 8 to 12, and a number format to row 13. Then you can just change the provider in cell B2 and the display will change accordingly. Note that I removed Provider D from the 12/13 sheet (for a bit more variety).

Hope this helps.

Pete

Hi Both, many thanks for your replies, works perfectly, i'll add some rep.

Now I'm a little confused by the formula, I've edited the spreadsheet with additional columns in each FY so more information needs to be pulled to the dashboard.

When selecting the Provider on the dashboard I would like for the Vendor number, the Type, the Spend with that provider and the percentage that spend is of revenue (I've highlighted these yellow on both the dashboard and FY worksheets.

Can you please explain to me how I will edit the formula so that this data will show when selecting a provider from the dropdown? I've been trying to work this out by looking at the formula and manipulating through trial and error but can't work it out! Adding these columns has messed up the previous formula so I would love to know how to edit the formula so I can add columns as and when I like

Big thanks!!
Alex

As you may have some providers missing from some of the sheets, it would make sense to record the Vendor_Number and Type in the table in the Dashboard sheet, so put the vendor numbers in column N and the Type in column O and then you can use this formula in cell C2:

=IFERROR(VLOOKUP($B2,$M:$O,COLUMNS($B:C),0),"")

You can copy this into D2 to get the appropriate Type. You can, of course, put this vendor table in a separate sheet as newdoverman (Ron) had done.

I've moved the categories down one row in column B so that you have room to record the Revenue, and then put this formula in C5:

=IFERROR(INDEX(INDIRECT("'"&C$4&"'!E:N"),MATCH($B$2,INDIRECT("'"&C$4&"'!a:a"),0),ROWS($1:1)),"")

I've coloured the following terms which have changed slightly from the previous formula:

C$4 - this is the sheet name, which is now in row 4 rather than row 3

E:N - this is the range of columns in the individual sheets where you want to get the data from

This formula can be copied across and down as before, making appropriate changes to the formatting for percentages and numbers.

Finally, you can use this formula in cell C16:

=IFERROR(INDEX(INDIRECT("'"&C$4&"'!D:D"),MATCH($B$2,INDIRECT("'"&C$4&"'!a:a"),0)),"")

Note that you want to get the data from column D (green), and because it is only one column you don't need the ROWS(...) term. You can use this formula in C17:

=IFERROR(C16/C5,"")

to get the percentage, and then copy these two formulae across.

Hope this helps.

Pete

Thank you both - very well explained and achieves what I want.

I will need to add providers so what do I do if I want to add another provider, say provider H to the drop down list which does the same and looks for the data in the other worksheets?

Alex

LEGENDS! Thanks for the help guys - going to work through this from start to finish so I can learn it.

I haven't used a dynamic range for Providers, so you would need to amend the "Refers To" box for that name using Name Manager if you add any new providers to the table.

Thanks for the rep - if that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete

EDIT: Ah, I see that you have done now.