How to generate a list of custom cells that are variable in length and content

So we are trying to make some sort of label generator to keep track of our mushroom bags (we are a mushroom farm and grow mushrooms in bags.
Currently we have a calc sheet that updates a database table that writer uses to make the labels
but we need a faster way of populating the calc sheet!

Each label needs to have 7 pieces of data


Some of the data are consecutive numbers, some are fixed info

Is it possible to make some sort of form or code where we can enter the species , the quantity, its other attributes and it will generate a list of all the cells we need.

For example we want to enter:
Species = Shiitake, Date=1/1/23, Source= GS1234, Rate=1.2oz, Quantity= 50, Starting ID=25.
Species = Lions Mane, Date=1/1/23, Source= GS1236, Rate=1.5oz, Quantity= 20, .

and it will produce 70 cells with the above info and IDs ranging from 25-94.

If anyone could let me know whatever tools i need to learn to make this happen, i would greatly appreciate it.

Can that help?

Printing Address Labels

Address labels in Writer

Unfortunately no, we already do that.

We are trying to find a way to generate a list of cells that we then use to make labels.

Questions like this are usually answered with yes :wink:
With a spreadsheet my guess would be you will have to create a macro. For example going down your sheet. When we find a line with quantity 50 (or n), we copy the row, and insert it below the current row with quantity 49 (or n-1). Repeat do next line until quantity is 1.
In a database i would think of a generated series or a common-table-expression like in the linked article.
But while one series doesn’t look to complicated, im not sure how to generate a fitting series for all rows with quantity > 1

Any semi-professional solution involves a database. A real database rather than a spreadsheet connected to a database. Yes a spreadsheet macro can do this particular task but it will be a spreadsheet macro which is contrary to a professional, long lasting, fool proof solution. A spreadsheet dealing with record set is a cheap plastic toy, no matter how much Basic code you attach to it.

A quick draft with limited data.
The query named “qryLables” would be the source of your label print out.
A query is a kind of “formula” which returns a whole record set instead of a single value.
multiple_items.odb (21.0 KB)

1 Like

Basically this is a task of creating combinations. It clearly asks for a sequential solution primarily, and therefore may be solved best with a database (if input data are to be taken from one anyway) or with a specialized program. Such a program may be written in Basic, and may take the few lines of input from a sheet and also deliver the results to a “prettyprint” sheet.

As a rather simple task of the kind, this one, however, can also be solved in a spreadsheet without special code with the help of standard formulas. If such a solution is sufficiently efficient can only be decided based on scaling information. If it is reliable and safe enough in such a case may not be clear from a simple test run! Anyway it can only be explained to and discussed with somebody having spreadsheet experience.
I now attach an example. Errors expected!! No special support!
disask85394MushroomLabels.ods (154.0 KB)

1 Like

This is brilliant and very very close to what we are looking for, thank you very much!
I will learn from your example and adjust it to our needs,
Thank you again! This will save us much time!

A “poetic” attempt:
You flatter my vanity with such praise.
But my true desire goes different ways.
Now serious again - without any hope for success:
I don’t know whether you are in the USA or in the UK or probably in a different English speaking country where blatantly outdated customs continue.

  1. What you give as an example for a “date” is none. At least it can’t identify a specific day - as long as you don’t add the info “US-style-month-first-two-digit-year-date-having-cut-off-the-century-20” or respectively. That’s a way of “shortening” things with the effect of making them useless - like shortening a skirt to 15 cm (about 6 in), and then needing a housecoat when going out. A correct date actually communicating information follows the scheme YYYY-MM-DD (ISO 8601 extended; globally approved standard telling the date and the fact “I am a date” -nearly- as well)(#).
  2. The US and the UK joined an international agreement called “Metre Convention” or “Treaty of the Metre” in 1878 and 1884 respectively (including ratification as a binding treaty). The convention was not only about measuring lengths but about a lot more. Till then both countries unfortunately didn’t find an opportunity to implement the concerned measures and units in everyday life though clarity and simplicity in this range was a declared goal of the treaty. Why do you use “oz” or generally the “Imperial system” with its variants and all the outdated stuff? BTW: Where a sufficiently unambiguous definition for imperial units exists at all, it is updated to metric fundamentals in the background. For example, the “modern” in (inch) is by definition 2.54 cm. Concerning the oz see Ounce - Wikipedia.
  3. You used “2 / 5” trying to tell “second portion of five”. Isn’t that one too many uses of the slash? How would you distinguish this from the fraction “two fifths” unambiguously?

You surely want to do me a favor? Well, help to tidy up habits and to get reliable standards of information interchange.

(#) I have removed a reference to the time zone dependency of the date.
PS I get a crisis when I see how recently “the US market” is forcing the inch as a unit on the world in some areas - and everyone accepts it as a matter of course.
PPS This (ask) site uses nonsense pseudo-formats for time/durations and dates. E.g. “6m” expects us to understand “6 min ago”. Are we crazy? The answer is obvious. Users and contributors to forums wasted thousand if not millions of hours finding ways (helping with) out of problems they never had if clean standards were implement in spreadsheets. Except the “English-Canada” locale currently there is none I would know of using an acceptable default date format.
PPPS over.