Apache OpenOffice Community Forum

I was reading on how to use a spreadsheet as a simple flat database. Excel seems to have this covered pretty good and simple to do. You simply highlight the range you want, press CTRL+T and viola, a database that expands as you add records.

Does Calc have anything like this? If so, how do you invoke it or use it. I have not found anything similar to that other than Pivot Tables, which is not really what I want. I just want to create a simple database that can expand as information is added and be able to perform simple database functions, calculations and so on.

I guess what Im a little confused on is whether or not this is anything similar to defining a range, making a pivot table or anything like that. In Excel, from what Ive read and from a few videos Ive watched it seems like a different function and feature all together and I want to be able to do that In LibreOffice Calc as it seems like the perfect solution to some template work I am doing. Thanks in advance for any assistance.

I have OpenOffice. To expand references when new rows or columns are 'inserted' there is Tools > Options > Calc > General. IIRC, you can insert a new row or column above, left,below and right of a range. I use code to expand a range when I append a row.

As well as Named ranges there are Database ranges and associated functions such as DMAX (Help under database functions). Data > Define range. LibreOffice may differ.

EDIT: After reading Villeroy's link, I see that expansion by insertion is only possible at the right or below a range.

So if I understand correctly, then, what Excel is touting as defining a database range is nothing more than defining a named range? Actually it looks like a cross between defining a named range and a pivot table as it automatically gives you the drop down arrows that you can use to get options to sort data and so on. I know when you define a range in Calc, the dialog box says Define Database Range, butt its just a named range. But you can still perform the database functions against that range, correct? If so then I just over complicated things when I saw the video about making databases with Excel.

Also, kingfisher, can you give me an example of the code you use to expand a range? I know about the OFFSET with the COUNTA function that will do that but even so you still have to define the area, so if you go beyond that area then thing break or dont work properly. I would much rather use some kind of function or code to do that automatically if you wouldnt mind doing that for me.