Field "position" in view to stay the same

Leonard Eldić

Hi guys,

I'm a new Smartsheet partner trying to develop a custom solution for a client.

So the client requests Dashboard with up to date leaderboard on it. I've made the leaderboard and connected it to other data so it calculates victories, losses, goal diff and everything, and as far as i understand, best way to sort the data is through view.

And views can be then added to Dashboards.

Problem is, when i sort the view by for example, points, the position also changes. So if a team is first, it may have different number in "position" (In the screenshot its primary column). And since it has to be a view to be on a dashboard I cannot think of a solution except doing it graphically on dashboard which doesn't really look good.

This is the screenshot:

Can you please help me out? Maybe some function instead of sorting?

Genevieve P.

Hi Leo,

Ah, thank you! I think I understand now. You want all the rows with the data to be Sorted, but you need the very first column, the Primary column, to automatically update based on the top score.

To do this you'd actually need to create a formula in that first, Primary column to make the numbers in this column update. (Otherwise they stay static and associated with that row, like how the Team Names stay with that row.)

I presume that the TOTAL column at the end is how you tell who's number 1... is that correct? (Apologies! I'm not that good with sports haha). If so, we can build the formula based on what row has the highest total.

To do this, you would use the LARGE function. For example, if you said this:

=LARGE(Total:Total, 1)

it would return the score in 1st place. But we don't want the score, we just want the number 1, so we can say this:

=IF(Total@row = LARGE(Total:Total, 1), 1)

IF the total in this row is the total that is in first place, return 1. Now what about all the other numbers? Well we just need to say the exact same thing... if the total is in 2nd place, return the number 2, etc. You can nest these together in an IF statement so that you can return

Full Formula

=IF(Total@row = LARGE(Total:Total, 1), 1, IF(Total@row = LARGE(Total:Total, 2), 2, IF(Total@row = LARGE(Total:Total, 3), 3, IF(Total@row = LARGE(Total:Total, 4), 4, IF(Total@row = LARGE(Total:Total, 5), 5, IF(Total@row = LARGE(Total:Total, 6), 6, IF(Total@row = LARGE(Total:Total, 7), 7, IF(Total@row = LARGE(Total:Total, 8), 8))))))))

Let me know if the makes sense and works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Leonard Eldić

I'm not sure I understand what you mean... it sounds like you've used a Published Link of this sheet to embed this into your Dashboard as a Web Content Widget. Is that correct?

If so, this will show a window into this specific sheet, exactly as it is organized. That means if you use SORT to update the order of the rows, this will display in the Dashboard.

If you wanted two different views (one with the sheet in an order you want, and one in the Dashboard, with a set order), you could potentially create a Report from this sheet, sorting the Report to have one view and using this Sheet to have another view. Then you could use that Report either as a Report Widget or publish this and embed it with the Web Content Widget.

Let me know if this makes sense! If I've misunderstood your question, it would be useful to see how you have the sheet displayed in the dashboard, and an example of it in an incorrect order.

Cheers,

Genevieve

Leonard Eldić

HI Genevieve,

thanks for the quick answer!

Unfortunately i cannot easily share the dashboard because of an NDA I've signed but let me explain a little bit further

So i have this data set:

1 Team 1

2 Team 2

3 Team 3

4 Team 4

So that's the sheet. To pull into a dashboard I'm using a view of the sheet which sorts the sheet in a way so that team with the most score ends up on top. My problem is that i still need the left column to stay unchanged.

Example:

1 Team 3

2 Team 4

3 Team 2

4 Team 1

Does this add more clarity to it? My sorting does this:

3 Team 3

4 Team 4

2 Team 2

3 Team 3

When i get to a proper computer maybe i can recreate it in smartsheet to further illustrate.

Thanks for your help!

Leo

Genevieve P.

Hi Leo,

Ah, thank you! I think I understand now. You want all the rows with the data to be Sorted, but you need the very first column, the Primary column, to automatically update based on the top score.

To do this you'd actually need to create a formula in that first, Primary column to make the numbers in this column update. (Otherwise they stay static and associated with that row, like how the Team Names stay with that row.)

I presume that the TOTAL column at the end is how you tell who's number 1... is that correct? (Apologies! I'm not that good with sports haha). If so, we can build the formula based on what row has the highest total.

To do this, you would use the LARGE function. For example, if you said this:

=LARGE(Total:Total, 1)

it would return the score in 1st place. But we don't want the score, we just want the number 1, so we can say this:

=IF(Total@row = LARGE(Total:Total, 1), 1)

IF the total in this row is the total that is in first place, return 1. Now what about all the other numbers? Well we just need to say the exact same thing... if the total is in 2nd place, return the number 2, etc. You can nest these together in an IF statement so that you can return

Full Formula

=IF(Total@row = LARGE(Total:Total, 1), 1, IF(Total@row = LARGE(Total:Total, 2), 2, IF(Total@row = LARGE(Total:Total, 3), 3, IF(Total@row = LARGE(Total:Total, 4), 4, IF(Total@row = LARGE(Total:Total, 5), 5, IF(Total@row = LARGE(Total:Total, 6), 6, IF(Total@row = LARGE(Total:Total, 7), 7, IF(Total@row = LARGE(Total:Total, 8), 8))))))))

Let me know if the makes sense and works for you!

Cheers,

Genevieve

Leonard Eldić

Hi Genevieve, this is great!

Thank you so much!

Leo

Genevieve P.

Great!! I'm so glad it works for you 😊