Guidance on creating a form needed (newbie on this subject)

Apologies for the silly question but I need to do the following:
I have a lot of data than I need to enter on a spreadsheet. It is a repetitive task, so I thought of getting a form to help me out.
What I would have is a list of entries (call it apples, apricots, bananas, plums and kiwis) and for each of them I then need to enter how many I have, how much they weigh, etc., so ideally I would have a form where I can enter a few characters and will show me the closest matches (say, I type “a” and shows apples and apricots; I type “app” and it shows only apples) so I can choose the item I am examining, and from there I can fill the form with all the relevant information which will then be automatically input into the spreadsheet.

I have had a look at a few tutorials, but I am getting confused, so I hope that the hive mind will be able to direct me to the relevant page/video.
TBH, I can’t even create a form in calc… the libreoffice page seems out of date.

I am on a MacM1 with 11.6 and I have just updated LO to

I was having a look at this
but all I can do is enter a list of terms in column 1, one per cell, and that is it.

I don’t have the actual data yet. I am still waiting for it, but I was trying to get organized in the meantime.

perhaps update you question with details of the OS in use and the version of LO Calc together with what you did try and any error messages you got. Even upload a subset of your data in a sample spreadsheet so we can see where you are coming from. Help the community to help you!
You can upload your sheet by clicking on image in the edit pane.

Done (as well as I can)

1 Like

Lots of data need to be entered into a database form. You must not enter lots of data into an arithmetic calculator. Details depend totally on the type of data to be entered, particularly the repetitive parts.

1 Like

Yes, indeed. The problem is that I don’t know yet the format…

I started by creating the data base structure:
I created a simple sheet with 3 columns: Type, Qty, Weight in row 1
I then created 3 rows apples, oranges, bananas in rows 2, 3 & 4 respectively.
I then went to Data -> Form
I was then presented with the data form starting with the first record apples.
I can then cycle through the items completing the details as I go.

fruit.ods (8.6 KB)

If your spreadsheet needs to be more complex than that, I would suggest Sheet1 (named as appropriate) as your basic data and construct more complex arrangements in following Sheets referencing back to the data in Sheet1 as required.

1 Like


This is what I see:
Screenshot 2021-10-21 at 11.03.48
Column A was entered using the form. row 1 was entered manually, but they don’t seem to talk to each other.

as with most database operations, set up the structure first then go to Data -> Form in a new sheet. Setting up column A with the form is the wrong approach.

We have just researched the question:

There is a lot of useful information in this thread.
But such a simple table can be maintained in Calc. I prefer to use the Advanced Filter with a minimum of macro programming to automate its work.

1 Like

Yes @GrahamLees , it dawned on me that I entered the headings incorrectly, so I closed the sheet and reopened it, reentered the headings manually, but when I go on data/form i still see the same thing.

Thanks, @eeigor. Will have a look at that.

Don’t use the same sheet… it’s already compromised. Start a new sheet following the steps I outlined (ie start afresh) to be on the safe side.

1 Like

Yes, indeed.
After some experimenting I have understood that I need to put the cursor on one of the items in line 1, and only then I get the expected for.
Hardly obvious (for me), but at least I am getting somewhere now. :wink:

@arkadian, think & tell us what real fields you need for accounting.

apologies, I missed that point in my solution but it was there in the example sheet I uploaded which should have provided the clue.
I’m sure you agree that learning to walk before learning to run is a necessary evil and you should take it a step at a time to understand what is going on.
If the data base is not contained in the top corner of the sheet, you can highlight the headers if they are contained elsewhere in the sheet and that wil achieve the same objective when you select Data -> Form
If this answers your question, please consider marking it as the solution and upvoting it for future reference by someone in the future with the same question. Thanks.

1 Like

Calc’s form tool is inacceptable. First of all, it does not expand any references as you enter new records. This is vital for any spreadsheet that is supposed to do more than just storing data in an arbitrary rectangle of cells. A dynamic db-range linked to some database record set inserts and deletes rows as the record set grows and shrinks keeping all spreadsheet references intact (formulas, names, charts, conditional formatting, validity etc.).

I would rather say “limited” as opposed to unacceptable. It performs by simply populating a basic table of data although I do accept that there are alternatives more suited to heavier weight and complex workflow requirements.

Such as, @Villeroy and @GrahamLees ? Database perhaps? That is a bit too advanced for me at this stage, I feel. With a bit of luck tomorrow I will know more about this job.

You could use LibreOffice Base for this.
This question with 4 answers helps explain.
If your original question is answered, please consider marking it a the solution. Thank you. The question of linking databases is a different question and should not detract from your original but be subject to a different topic if the link doesn’t initially help.

1 Like

@arkadian it would be better to mark the answer as the solution rather than a comment as that will mark your original question as solved. Sorry to be pedantic.

1 Like