Automatically Sort Fields Across a Row: Is there a formula?

[londonparisrome]

I am working on a solution for our school district PTA. I have 5 student names on one "family" record row, with hundreds of rows in the sheet. I would like to sort the student names alphabetically, and most people have listed the students in grade order.

For instance, the record may have cells A2-E2 with Larry, Bobby, Erin, Kim, Sam, and I need it to automatically sort to Bobby, Erin, Kim, Larry and Sam in columns F2-J2.

Is that possible? The end users of the spreadsheet I am creating are not very tech-savvy, so I was hoping to have am automated process.

 

[Tom.Jones]

Translate the English function COLUMNS() into your local Excel version language?

I use office 365 in English but with the European regional settings, which uses ";" in formulas and "," as a decimal point.
I tried that UDF with a new file, but without success.
Same result ...#value.

 

[Peter_SSs]

In G2 what does this produce?

=SortedNames(A2:E2;1)

If still a problem can you upload the sample file to a public file-share site & provide a link to it?

 

[Tom.Jones]

I have already tried, replacing COLUMNS ($ G2: G2) with 1, 2, 3 and 4 but I got the same result #Value
I can't upload any file.
OK. Leave it that way.
Thank you for trying to help me.

 

[Peter_SSs]

OK, can't think what else it might be unless maybe something to do with regional settings.

 

[Tom.Jones]

The file is here:

<label for="add_files_btn"> <!-- -->




<!-- -->

</label>

<tbody></tbody>

https://quickfileshare.org/2eDt/Horizontal_sort.xlsm

 

[Peter_SSs]

The file is here:


https://quickfileshare.org/2eDt/Horizontal_sort.xlsm

When I open the workbook the formulas are returning #VALUE !
However, if I recalculate the worksheet (Alt + Ctrl+ F9) or re-confirm the formulas, or copy the formula to new rows then they return the correct results.

 

[Tom.Jones]

None of the suggested variants work for me.

Thanks anyway.