I need help with Excel dropdown limitations in OutSystems. I'm creating an Excel file with a dropdown list containing supplier names from our database. Since the list keeps growing, I'm having an error stating that dropdowns cannot exceed 255 characters. I've already tried using the solution suggested in the OutSystems forum regarding Advanced Excel dropdowns ( https://www.outsystems.com/forums/discussion/74795/advanced-excel-dropdowns-working-around-the-255-character-limit-by-using-a-dat/ ), but it didn't resolve the issue.Has anyone found a workaround for this limitation? Any suggestions would be appreciated.
hello @Denise Soares Luiz ,
This is a known Excel limitation rather than an OutSystems issue. Excel data validation dropdowns that use inline lists are restricted to 255 characters, so when supplier names grow, the validation fails. Even some “advanced dropdown” approaches can still hit this limit if Excel ends up treating the list as an inline value.
The most reliable workaround is to avoid inline lists completely. Instead, write the supplier names into a separate (hidden) worksheet, one supplier per row, and then reference that range in the data validation rule (for example, =Suppliers!$A$1:$A$1000 or a named range). Excel does not apply the 255-character limit when the dropdown is based on a cell range, so this scales well even with hundreds or thousands of suppliers.
Thank you, Jayaprakash, I tried again and it is working perfectly!Here is the solution implemented: A second sheet was created using the Suppliers table:
In the AddDropdown, I included the ItemsAddress. After that, I used the WorkSheet_HIde_Show to hide the temporary sheet.
@Denise Soares Luiz : Based on the documentation provided, you are required to set isList = False and specify the appropriate range within the sheet that contains the supplier list. Please confirm that you are following these steps. You may share a screenshot of the Worksheet_AddDropdown properties.
Hi Siya, thank you for your support! I don't have the IsLIst property in the AddDropdown, as you can see in the printscreen below:
Anyway, I tried the solution again after Jayaprakash suggestion and it is working perfectly now.
Glad to hear the issue is resolved. I noticed that the component referenced here is different from the one mentioned in the shared article, which explains the parameter difference. In both cases, you need to reference a range instead of a list to override the 255 character limitation.
Firstly, I don't use the function Worksheet_AddDropdown yet. In my perspective, I think to reuse the excel file, the data should be kept inline for the trace back data issue purpose. By this way, whenever the file is passed to others, it is understandable and readable.
Secondly, the Microsoft excel has the limitation itself for the data validation part, specifically:
In conclusion, I think you should put your dropdown data list in the other sheet within your excel file. Then, you use the formula refer to the data value instead of the text itself (e.g, "=A2", "=B2", etc)
It's a good idea!
Usually, the data for a dropdown is retrieved from sheets named "master_data". if function Worksheet_AddDropdown limit 256 chars then we only truncate before save or implement the method that Shingo mentioned.