How do I add a list or formula to a combo box?

I have started to try and convert an old excel spreadsheet over to LibreOffice. The sheets themselves were created in or about 1999. I have run into a few issues in particular the combo boxes are being populated with different data than what they were originally programmed with. Upon much investigation I was able to learn how to edit and create combo boxes but I have not been able to figure out how to add data to the combo box.
In Design Mode when I right click on the combo box and select “Control” and go to the data tab there is a field called “Source cell range”. In that field is listed the cell range where the current data for the combo box is coming from, and it is the incorrect cell ranges for that combo box. I originally had a formula in that field (in Excel it was different) that referenced a range of cells in another sheet. I have not been able to figure out how to add the same formula to the “Source cell range” field or even if that is the correct field for the formula to be placed.

In summary I am trying to figure out how to add a formula to a combo box so it will fill the combo box with data from a range of cells. It would be preferable if it would take the data from another spreadsheet.


With combo box on Sheet1 and data on Sheet2 in cells B3 through B7, then in Source cell range of combo box enter:


I tried that and it isn’t working for me.
My source sheet is Tables.xls and these are the codes I tried:


I also tried it like this:


Fyi, by another sheet I am talking another spreadsheet entirely, not another page within the same spreadsheet.

Come to think of it I also need the page of the other spreadsheet…lol

Do not think you can insert directly into Source cell range a location from a different document. Would need to link to cells from other document and then attach to Source cell range.

That will probably work. There is a tables sheet that has all the tables and there are the extensions sheets that has all the data and formulas that reference the tables sheet. I created the tables sheet so it was easier to do updates. With it I could update just one sheet and the others would reference that one with the updates. It was also originally to reduce file size because back in those days many were limited to just the floppy disks…LOL.

Works on LO… (Nov 4th 2023)
Data(Toolbar) > Validity… > In the “Allow” field, choose “Cell range”. Then in corresponding “Source” field, enter your data range as such: