Dynamic Dropdowns needed in OnlyOffice but can't include certain Excel Functions

In the Data Entry sheet, I need a drop down list in the Employee Name column, Cells C2-C5, that changes based on what is selected in the Data Entry sheet, Branch Name column, Cells A2-A5 by looking at the Employees Master List.

BUT! To get there, for immutable reasons (cross compatibility with OnlyOffice required), I absolutely cannot use the Excel Functions TRANSPOSE, OFFSET, SORT or FILTER.

Is this a non-starter?

If there' some other way, please demonstrate in the least complex way possible given the limitations.

Please try DV list

=OFFSET('Employees Master List'!$A$1,MATCH('Data Entry'!$A2,'Employees Master List'!$C$2:$C$9,),,COUNTIFS('Employees Master List'!$C$2:$C$9,'Data Entry'!$A2))


or without OFFSET
Range Name EmName

=INDEX('Employees Master List'!$A$2:$A$9,MATCH($A2,'Employees Master List'!$C$2:$C$9,)):INDEX('Employees Master List'!$A$2:$A$9,MATCH($A2,'Employees Master List'!$C$2:$C$9,)+COUNTIFS('Employees Master List'!$C$2:$C$9,$A2)-1)

DV list =EmName

Will your employees on Employees Master List always be grouped together by branch as in your example, or will they be interdispersed?

@Bo_Ry

Ugh. INDEX appears to be a no-go as well. I have to provide cross-compatibility with this awful, very limited Excel-imitator online spreadsheet tool, thus the exclusion of all these standard Excel functions.

@JohnTopley

This seems viable, but in reality I am dealing with 150 Branches and a total of 2,500 Employees. Branches are stable, but the Employee List is subject to monthly additions and deletions. This data entry tool is provided to users once a month. Even if I were willing to create 150 Named Ranges as a one time thing, there's no way I could monthly reset those ranges based on whatever the current data set was. Would require automated creation of named ranges based on the Master List. Is that even possible do you think? I'd probably need to pay for that kind of Excel development work, but you're on the right track.

Hence the need to know the name of the tool, pop that into the thread title and move this to the Other Platforms section (I will do the latter right now).

Thanks, Kyle.

I presume you have asked for help on their forum? i would not normally point you away from here, but on this occasion, I think it might help.

https://cloud.onlyoffice.org/

TRANSPOSE and INDIRECT are both listed on the OnlyOffice help centre for the Spreadsheet Editor:

https://helpcenter.onlyoffice.com/on...transpose.aspx

https://helpcenter.onlyoffice.com/ON.../indirect.aspx

what software are you wanting cross compatibility with that doesnt support those functions?

Thanks for letting us know.