Best Of

@mahmadi

I use the following method to achieve this in the Core App only.

1. Sheet for display
   1. Auto number Row ID column.
   2. Read-only columns and editable columns for 20, say 40 people. (in my demo).
   3. 20 columns for 20 people to display the URL of the form created in step 2.
   4. Add formulas that retrieve the latest data from the sheet of the form created in 2 by INDEX (MATCH or COLUMN) using the row ID as a key.
2. Sheet for forms to update the sheet for display
   1. Create a form for every 20 users that adds a new row with a row ID column and editable columns.
   2. For Row ID, use a URL query string to prefill default values on a form, using 1. Sheet for display's Row ID.
      1. https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values
3. Set up the form so that new inputs are added to the top of the sheet. (To make it easier to get the latest additions by the form in INDEX 1)
4. A report for each person that pulls only the relevant columns from the sheet and displays them
5. Create a dashboard for each person, add published reports in 3, and share the dashboard with view-only permissions.
6. Steps 1 to 4 will allow the person with view-only access to the dashboard to view and edit some columns of the sheet in 1 without having access to the above sheet.

Below is a demo dashboard I created using this method so you can try it out.

(Originally, this would only be shared with those who can view and edit as in 4, but for the sake of demonstration, we are making the dashboard available to the public.)

Hi @Erica Lozano ,

I'm not sure if this is your issue but, since you are allowing multiple values in the drop drown, if there are more than 1 matches, the formula will resolve to the first, which is "Red" in your case.

For example, if you choose "OK: Sold" and also "Needs Attention:...", it will resolve to "Red"

Hope this helps,

Dave

Hi @tina.hutchinson52776,

On your Master List, you'll need to add a column for the rank in the category. This would have the formula:

=IFERROR(RANKEQ([2023]@row, COLLECT([2023]:[2023], Category:Category, Category@row)), "")

On your 2023 Top 5 Popular Courses per Category you can then use some INDEX/COLLECT functions to get the course names and numbers (where the cross sheet references refer to the relevant columns).

For course names:

=IFERROR(INDEX(COLLECT({Master List Official Course Name}, {Master List Rank in category}, [Popularity No.]@row, {Master List Category}, Category@row), 1), "")

For course numbers:

=IFERROR(INDEX(COLLECT({Master List 2023}, {Master List Rank in category}, [Popularity No.]@row, {Master List Category}, Category@row), 1), "")

The IFERROR portion of this is just so you can use these as column formulas and not have #INVALID V

Some very simplified & basic sheets (with top 3 in 2 categories) just to show the working:

Master List

Ranking sheet using cross sheet formulas:

Some things that I can see which may throw a slight spanner in the works are:

Your 7 stated categories does not include First Aid, but if this is just an oversight and there are actually 8 categories then all should be well!

If you have 2 courses with the same numbers in 2023 in the same category they will end up ranked the same which will throw things out. In this case I would suggest sorting data by category & rank and deciding which to rank over the other which would be double by making one a decimal and adding the ROUND function into the course number lookup.

Hope this helps, but if you've any problems or questions then just ask! 😊

Hi @Ipshita ,

Thank you so much for your time and response and yes, you understood correctly. Fortunately, I was given guidance on this question elsewhere recently and was able to figure out the solution to my problem. The formula I had used was:

IF(COUNTIF(CHILDREN()), 1) / COUNT(CHILDREN()) = 1, 1, 0).

This worked flawlessly for both the CHILD row and PARENT row as it helped automate the checking process.