Labeling in Calc

users
#1

Using Calc, I need a way to specify that a group of rows all belong together/have the same title.

For example. Say I have a bank that has multiple branches. Each branch has it's own name like "southwest branch", but they're all part of the same bank - Bank of America perhaps. Each branch has it's own row containing branch name, address, phone, etc. but I need a way to tie them all together as being Bank of America, ideally without creating a column that is repeated for all rows.

That way, as I'm scanning down the page, I can choose a particular bank and then select a specific branch of that bank.

I've tried using the Group function which I kind of like, however there's no way I can find to label the group itself with the bank name. I have to open each group and _still _don't necessarily know which bank it is.

I've got a picture in my head that looks like a box which contains all the rows, and the box itself has a caption with the bank name. I'm open to any solution especially a better one.

Using a database is not an option.

Thank you in advance

#2

Nevermind, I figured it out.

In case anyone else has a similar desire and wants to know... I simply select all the rows I want to belong to a group, add a border around it all, and insert a row above the box I just made. The inserted box contains the label I want; I make sure to turn off word wrapping for that one cell.

#3

Am 20.07.2012 23:50, Andrew Brager wrote:

Nevermind, I figured it out.

In case anyone else has a similar desire and wants to know... I simply
select all the rows I want to belong to a group, add a border around it
all, and insert a row above the box I just made. The inserted box
contains the label I want; I make sure to turn off word wrapping for
that one cell.

The component is named "Calc" because it is a calculator in the first place. It is not a table editor nor database although many people use it that way.
Base reports can turn raw, unsorted table data into grouped reports.

#4

Thanks for your input. As you say.... "many people use it that way". Since my intent is to have assistants work with it, I'm stuck with what the vast majority of average people know. They know Excel (the MS equivalent as you know). Sadly, they do NOT know Base or any other database application. To learn it would require they be trained and that probably means hiring a computer scientist instead of an assistant, which makes the cost prohibitive. Either that or I design a bunch of forms and predetermined sql statements or hire someone else to do so just so I can hire a secretary. Seems to me that's the long way around when all I need is to group the rows, set a border and add a label. Sure, a database would be the best solution, but it boils down to expediency, convenience and cost so Calc wins out on all 3.

#5

Hi :slight_smile:
+1
Your solution sounded like the most visually appealing too, which helps! :)  There is always the possibility of migrating it to a database later on if it grows and becomes unweildy or if the assistants skill level turn out to be higher than expected.

Regards from
Tom :slight_smile:

#6

Am 22.07.2012 01:48, Andrew Brager wrote:

Am 20.07.2012 23:50, Andrew Brager wrote:

Nevermind, I figured it out.

In case anyone else has a similar desire and wants to know... I simply
select all the rows I want to belong to a group, add a border around it
all, and insert a row above the box I just made. The inserted box
contains the label I want; I make sure to turn off word wrapping for
that one cell.

The component is named "Calc" because it is a calculator in the first
place. It is not a table editor nor database although many people use
it that way.
Base reports can turn raw, unsorted table data into grouped reports.

Thanks for your input. As you say.... "many people use it that way".
Since my intent is to have assistants work with it, I'm stuck with what

Spreadsheets are extremely difficult to maintain by untrained users. We all use input forms on top of databases all day long online and offline without even noticing it. Professional tools online and on the desktop are built on top of databases. No professional would ever build such things on top on spreadsheets.
What you try to do is unprofessional because you mix layout, formatting and data on the same sheet. The whole thing relies on careful data input in the right order.

Please ignore Tom. He is very kind but totally ignorant towards LibreOffice Base which can read spreadsheet data as if they were database data. Therefore you can generate professional reports from spreadsheet data. You don't need to "convert" anything. Nevertheless, a true database would be so much easier to maintain, particularly by untrained users.

#7

Hi :slight_smile:
That is true.  So why not use the fastest route to get the required result and stick with spreadsheeting for now?  Definitely a good idea to start vaguely thinking about moving to a database program when time&resources permits.  Base is not yet ready for masses and does take some fairly considerable expertise "to get it right".  Set-up is not trivial even if some people find it easy. 
Regards from
Tom :slight_smile:

#8

Exactly Tom. With Calc, setup cost and time is next to zero, with data entry not much more. With Base setup cost and time is non-negligible and data entry is much more time (and therefore real dollar cost) consuming as each field needs to be filled in by hand when using a form. With a spreadsheet I can actually cut & paste the info. from a website directly into Calc. Can that be done with Base?

Seems to me that the best solution even if using a database is to first enter into a spreadsheet where it can then be saved as a file of CSV and then imported into a database should I find the need.

Bottom line is it's already done in Calc - took about a minute - while doing it in Base requires sitting down and spending time and money on first creating a schema with forms and further, one needs to be a master of SQL which most people including myself are not. (Inner joins, outer joins, left right... haven't a clue. Cut & paste? click, drag, shift-click, click, done, repeat as needed - no higher brain power required.)

In theory a database is more elegant and conceptually appealing, but in practice a spreadsheet IMHO wins the day. YMMV.

#9

Am 22.07.2012 16:19, Tom Davies wrote:

Hi :slight_smile:
That is true. So why not use the fastest route to get the required result and stick with spreadsheeting for now? Definitely a good idea to start vaguely thinking about moving to a database program when time&resources permits. Base is not yet ready for masses and does take some fairly considerable expertise "to get it right". Set-up is not trivial even if some people find it easy.
Regards from
Tom :slight_smile:

<snip />

Please ignore Tom. He is very kind but totally ignorant towards LibreOffice Base which can read spreadsheet data as if they were database data.

<snip />

Tom, you will never get it because your engagement is entirely idealistic. You do not use LibreOffice more than a beginner.

Enter raw data into a spreadsheet (Excel, Gnumeric, Calc, whatever), a text editor, some dBase application, a true database, whatever.
Connect a Base document to the tabular data source and design a report.
The report will read the raw unsorted data and present them in any wanted order with any grouping you want. The very same report will always reflect the current status of the raw, unfiltered, unsorted data.
Both, the data source and the report may be spreadsheets. A sheet report can have some conditional formatting to hide values when they are equal to the preceeding value.

All I try to explain over and over again: In any case you should strictly separate the raw data storage from calculated, sorted, filtered, grouped and formatted output. This is best practice for spreadsheets and databases enforce this separation anyway.

As a matter of fact, database forms (even Base forms) provide much faster and less error prone ways of data entry. In the last 2 years I replaced a dozend of useless spreadsheet lists with simple databases because "my users" never really got used to spreadsheet editing (navigation, dates, numbers, clear, delete, ...). Spreadsheets are too versatile for untrained users. Wrong data yield wrong results. My databases collect a thousand of manually entered records per month on a local network.

#10

Enter raw data into a spreadsheet (Excel, Gnumeric, Calc, whatever), a text editor, some dBase application, a true database, whatever.
Connect a Base document to the tabular data source and design a report.

But that's the point isn't it. I've got to enter data into the spreadsheet. We're in agreement.

Why must I take the time, expense and trouble to "design a report" when I can get satisfactory results with a bare minimum of time, expense and trouble using Calc?

Clearly, you know Base and designing said report is no big deal for you. I think I may have opened Base once, and I simply don't have the time to learn it, nor the money to pay someone else to do the work for me. Especially not when a solution presents itself in Calc so readily.

Yes, your solution is elegant, ideal and based in academia where people have all the time in the world to learn applications and techniques and then design dream or "best practice" solutions. In the world of business it's about simplicity, speed, expense and return on investment. I can hire a virtual assistant from a third world country for less than $2 an hour, who already knows how to use a spreadsheet (not that cut & pasting requires much knowledge), whereas to hire a programmer to design a report would cost in excess of $8 an hour (and everytime I need a change I either have to go find that same contractor who now charges more, or start the interview process all over again; a time consuming task) - plus I still need to hire the assistant that ends up doing just about the exact same (busy) work anyway. So where is the economical benefit to your solution?

Your solution works for you because you have not considered the real cost of implementing it. It may be "best practice" from an academic, engineering and/or scientific standpoint, but from a SMALL business perspective it makes little sense. You've absorbed the cost of your own time and pegged it's value at zero. I'm taking real dollars out of my own pocket and paying someone to do the work. It's the same reason why people hire housekeepers - if the cost of the housekeeper were more than the employers income they couldn't afford to pay the housekeeper for long and would eventually have to clean their own house. You're cleaning your own house and that works for you. Great! I prefer to pay someone so I'm free to focus my time on what I hope are more profitable endeavors.

As a matter of fact, database forms (even Base forms) provide much faster and less error prone ways of data entry. In the last 2 years I replaced a dozend of useless spreadsheet lists with simple databases because "my users" never really got used to spreadsheet editing (navigation, dates, numbers, clear, delete, ...). Spreadsheets are too versatile for untrained users. Wrong data yield wrong results. My databases collect a thousand of manually entered records per month on a local network.

So really you're trying to apply a one-size-fits-all approach. My users (1 or 2 assistants) already know how to use spreadsheets so technically they're not "untrained", whereas your users do not know how. You collect thousands of MANUALLY entered records per month. I might have maybe a thousand or so records total, which can be cut & pasted from website to spreadsheet. My needs are different than yours. Any solution needs to take the customer's needs and unique situation into account and in this case I'm the customer.