Master worksheet automatically updating other sorted worksheets.

[TB Frank]

I haven't managed to find this exact query elsewhere although I'd be surprised others haven't asked something similar - so apologies if it's already been answered.
I want to provide non-tech users with a suite of sorted/subtotalled/charted etc views of their data in different worksheets, which will be updated automatically whenever they use 'get data' to update a master worksheet. I know something about referencing data from another worksheet - but I'm not managing to display data sourced from a master worksheet automatically sorted differently.
Here's a simplified example - suppose a user uses Get Data to populate the first (master) worksheet with these two columns:

Name Score
Eve 8
Ann 6
Tom 9
Dave 7

I want to build a 2nd worksheet which will immediately and automatically display this data sorted by score (without the user needing to click on any column heading or sort function)

Name Score
Tom 9
Eve 8
Dave 7
Ann 6

The user can replace the master data with a new upload, and the 2nd worksheet will show the new data sorted by score.

I'm hoping there's a strategy or trick for doing this which I can then apply to a range of layouts and charts etc.

Thanks in advance for help with this.

 

[kevin9999]

Welcome to the forum

You indicate you have 365, so the following should work for you (updates automatically - I went as far as row 31, you go as far down as you think you'll ever need)

Book1
	A	B
1	Name	Score
2	Eve	9
3	Ann	8
4	Tom	7
5	Dave	6
Sheet2

Cell Formulas
Range	Formula
A2:B5	A2	=SORT(FILTER(Sheet1!A2:B31,Sheet1!A2:A31<>"",""),2,-1)
Dynamic array formulas.

 

[TB Frank]

Welcome to the forum

You indicate you have 365, so the following should work for you (updates automatically - I went as far as row 31, you go as far down as you think you'll ever need)

Book1
	A	B
1	Name	Score
2	Eve	9
3	Ann	8
4	Tom	7
5	Dave	6
Sheet2

Cell Formulas
Range	Formula
A2:B5	A2	=SORT(FILTER(Sheet1!A2:B31,Sheet1!A2:A31<>"",""),2,-1)
Dynamic array formulas.

Hi Kevin - thanks so much for that - yes it worked for me and will help a great deal.
I've been looking through Dynamic Array functions for something on subtotalling (columns) - presuming any subtotalling I would want to add to the Sort would have be done within the formula - but is that at all possible?
TB

 

[kevin9999]

Glad it worked for you and thanks for the feedback
Regarding the subtotal, a simple Sum() formula on sheet 2 on column B will give you the total score of the filtered/sorted values on sheet 2 - is that what you were looking for?

 

[TB Frank]

Glad it worked for you and thanks for the feedback
Regarding the subtotal, a simple Sum() formula on sheet 2 on column B will give you the total score of the filtered/sorted values on sheet 2 - is that what you were looking for?

Hi. Thanks again. I don't think a simple Sum gets me the subtotals? The requirement is better explained with this sample of source data.

Name Score
Eve 8
Ann 6
Tom 9
Dave 7
Tom 4
Ann 2

I want to build a 3rd worksheet which will immediately and automatically display this data sorted by name, (so I'm sorting on column A rather than B) but with with subtotals for each name. (again without the user needing to click on any column heading or subtotal function). Ideally with a means to include either only subtotals, or detail rows with subtotal.

(sorted by name with detail rows and subtotals)

Name Score
Ann 6
Ann 2
Subtotal for Ann: 8
Dave 7
Subtotal for Dave: 7
Eve 8
Subtotal for Eve: 8
Tom 9
Tom 4
Subtotal for Tom: 13


(sorted by name with subtotals only)

Name Total Score
Ann 8
Dave 7
Eve 8
Tom 13

 

[kevin9999]

I suspect what you're now asking for is possible, but (other than a pivot table) I can't suggest anything useful as formulas are not really my area. Hopefully someone else on the forum can come forward with a solution

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2	Eve	8		Ann	6		Ann	8
3	Ann	6		Ann	2		Dave	7
4	Tom	9		Subtotal for Ann	8		Eve	8
5	Dave	7		Dave	7		Tom	13
6	Tom	4		Subtotal for Dave	7
7	Ann	2		Eve	8
8				Subtotal for Eve	8
9				Tom	9
10				Tom	4
11				Subtotal for Tom	13
12
Sheet5

Cell Formulas
Range	Formula
D2:E11	D2	=LET(f,SORT(FILTER(A2:B100,A2:A100<>"")),u,UNIQUE(INDEX(f,,1)),SORTBY(VSTACK(f,HSTACK("Subtotal for " & u,SUMIFS(B:B,A:A,u))),VSTACK(SEQUENCE(ROWS(f)),XMATCH(u,INDEX(f,,1),0,-1)+0.5)))
G2:H5	G2	=LET(u,UNIQUE(FILTER(A2:A100,A2:A100<>"")),SORT(HSTACK(u,SUMIFS(B:B,A:A,u))))
Dynamic array formulas.

 

[TB Frank]

Thank you Fluff - great stuff. These (and Kevin's) formulae will serve me well - I'll spend some time getting my head around the syntax.

Just one more question - might be pushing it - but can charts (bar, pie etc.) be configured and triggered from within a formula?

 

[Fluff]

I know nothing about charts, so cannot help with that.