Formula for extracting data to dashboard tab

[Wouter Simons]

Hi there,

In the attached Excel file you will see my issue:

1. In tab "Dashboard" some of the variables are given: Customer, Period & Subject.

2. The domain & sales are not given (I am looking for the formula to automate this)

3. In tab "Test" you will find the data.

4. I would like to get the formula to extract the corresponding data (corresponding with the variables given) from the "test" tab to the "dashboard" tab.

Please let me know whether the example is clear enough.

With kind regards,
Wouter

[sandy666]

you can try with PivotTable

btw. your result for filter: CustomerX/Januari/C is not correct

[Wouter Simons]

Hi Sandy666,

Thanks for your quick reply. The pivot table can be an solution but I prefer an formula like asked in the example excel.

For example when deselecting all variables in your Excel sheet I still only get two domains?

With kind regards,
Wouter

[Wouter Simons]

Sorry I didn't deselect all, so your pivot table is working. But for example I would like to see the sum of sales from high to low. This is something that I can't get done.

[sandy666]

If you prefer formula(s) that's ok.

btw.
example:
Filters:

• Segment: All
• Periode: All
• Subject: C

right click inside Sum of Sales column and Sort - Largest to Smallest

[Wouter Simons]

Hi Sandy,

That's what I've tried but I get the #N/B notice. Even if I try it in your pivot table.

Kind regards,
Wouter

[sandy666]

Here is example file
where I did exactly what I said above

If you got N/B (I assume this is #N/A) you did something different than sort)

sortPT.jpg

maybe read this: Excel Pivot Table Sorting

[Wouter Simons]

Hi Sandy666,

I think this is the solution I was looking for. Thanks so much, you've made my day!

I'll test the solution further tonight when I have some more time to see whether it holds its function after some use.

Kind regards,
Wouter Simons

[rpinxt]

Hi Wouter,

How about enclosed with formulas.

Be aware this only works if your data is unique.
So your variable (Customer X, Januari and C) must lead to only 1 unique sales outcome.
Not with relation to finding the sales amount (sumifs will take care of that).
But seen as domain outcome can only be 1 result.

And indeed there is no outcome for 'www.google.com, Customer X period Januari subject C and sales of 175.

[Wouter Simons]

Hi rpinxt,

Thanks for your answer. The dataset will contain much more urls en sales so this is not the right solution for us.

For sure I will keep this is mind when exploring other opportunities.

Thanks!

Wouter