Excel Forum

Sorting stock dashboard by Market value instead of Alphabetical

mark lettang

Active Member

Members

Forum Posts: 4

Member Since:
February 25, 2023

Offline

February 25, 2023 - 3:58 am

Hello,

Love the stock dashboard.

This array formula in the dashboard sorts the stock dashboard alphabetically:

=SORT(UNIQUE(FILTER(Ledger[Stock],SUMIF(Ledger[Stock],Ledger[Stock],Ledger[Units])<>0)))

Is there a simple modification that would sort it by Market Value, instead? (descending)

Thanks

Mark

mark lettang

Active Member

Members

Forum Posts: 4

Member Since:
February 25, 2023

Offline

February 25, 2023 - 2:28 pm

Hello,

I figured it out and would like to share. Two changes to accomplish this:

1. Remove the Sort from the Stock column. It becomes:

=UNIQUE(FILTER(Ledger[Stock],SUMIF(Ledger[Stock],Ledger[Stock],Ledger[Units])<>0))

2. Add sort function to the Market Value column. It becomes:

=SORT(D3#*E3#,,-1)

note: there are some optional arguments left blank for the sort function. the -1 is for descending

Thanks

Mark

mark lettang

Active Member

Members

Forum Posts: 4

Member Since:
February 25, 2023

Offline

February 26, 2023 - 5:16 am

I spoke too soon. The above modifications sorts the market value column according by market value, but it doesn't sort the other columns (and so the Stock columns don't match-up properly with the market value column).rn rnAny help as to how to sort entire dashboard list by market value would be appreciated!rn rnThanksMark

Mynda Treacy

Admin

Forum Posts: 4620

Member Since:
July 16, 2010

Offline

February 28, 2023 - 10:49 am

Hi Mark,

The best way is to create the 'Current Holdings' table in a separate workings sheet and then use the SORT function to bring the table into your dashboard sorted by the market value column. e.g.:

=SORT(B6:F11,5,-1)

Where B6:F11 contains the following columns: