Need to create drop down list of calculated data for each name from data in two sheets

RoseR12

New Member
Joined
Oct 12, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I am new to this forum and to Excel so am definitely a beginner.

As my example shows I currently have two sheets, one for spend and one for balances.

The first (in the format of the pictures) contains rows of a persons name, the date, and the spend on that date by that person. It is in order of date, everyday, from 15/06/2020 to 27/09/2020 and each person appears everyday they spend. So in the example, Elizabeth spends on both the 18/09/2020 and the 27/09/2020. The real sheet has a long list of names appearing most days with their stake for that day.

The second contains the balances of each person on each day (as shown in pictures). It has a row of each person along the top and a column of every day on the left hand side. On each day from 14/06/2020 to 27/09/2020 the balance of each person is shown.

I want to create a drop down list for each name. Within each drop down list I want to be able to calculate the following: the top 1%, 5% and 10% percentile spends, the average value of their spends above those percentile values, the number of days they spend over that percentile and the difference between the 'average above the percentile' and the 'percentile value'. I can easily do formulas to compute all these individually with the data for the persons spend and balances on each day all in the one sheet. However, I don't want to have to make a new sheet for each person and copy and paste each of their spends and balances into a new table within that sheet in order to calculate this data.

Does anyone have any suggestions for how I should go about this? Or is there a way to write a formula to take the data for each name and each date from the two seperate sheets (e.g. compare balance and spend on the same date/by the same name even though they are in different sheets) and use them in the new sheet to calculate the new data for each of these people in an expandable drop down list form. I appreciate any help you can offer, and it would be great if you could also explain any formula you use/suggest so that I know how to adapt it myself.

Thank you
 

Attachments

  • Example 1.PNG
    Example 1.PNG
    9.9 KB · Views: 1
  • Example 2.PNG
    Example 2.PNG
    12.6 KB · Views: 1
  • Example 3.PNG
    Example 3.PNG
    2.2 KB · Views: 1

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Watch MrExcel Video

Forum statistics

Threads
1,123,280
Messages
5,600,702
Members
414,400
Latest member
Damocles2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top