Ask Your Question

Wildcard's profile - activity

2020-08-15 17:35:29 +0200 marked best answer Convert number to date in Base (or compare to a date at least)?

The SQL command "TO_DATE(<number>)" returns an error that it's not supported. (I'm using a Calc spreadsheet as the back end because I just need to run some queries off of it, not actually have any relational database.)

Of course, in the Calc file the dates are displayed as dates, not numbers—but actually they are timestamps and so include fractions in the underlying number storage. This wouldn't be a problem, but I need to split the day at NOON, and count anything after noon as being the "next day" for purposes of my query.

I thought I could get around this by changing that part of the query to ROUND("Log"."DATE") as "Date", which at least doesn't throw an error and does seem to round correctly (i.e. anything PM becomes rounded to the next day). Two problems with this: (1) The rounded date is then shown in the query results as a number, like 41875. (2) I can't work out how to compare the resulting number to a user defined date parameter.

(1) isn't a big issue because I can include "Date" in the query twice—once rounded for filter criteria, and the other unaltered for display purposes. (I would have to do this anyways as I'm counting afternoons as part of the next day for filtering purposes, but still need to display the date that was actually entered in the spreadsheet.)

(2) is a problem. I think it would be solved if I can map the rounded date number back to an actual date, but possibly the actual issue is how to ensure the parameter entered by the user is understood as a date and properly converted to a number. (Incidentally, does Base store dates and timestamps the same way as Calc? Such that 28 Aug 14 = 41876?)

I'm giving all the extra data on what I'm actually trying to do, because it's possible there is a different approach that would be a lot better. ;)

The actual FINAL end result that I need in terms of functionality, is to have the end user fire up Base, click "generate weekly report", choose the date range (or at least one date—the range will always be from noon Wednesday to the next Wednesday at noon), and then be able to print the query results in a nice tabular form.

2019-07-31 02:15:13 +0200 received badge  Good Answer (source)
2018-01-02 07:42:12 +0200 received badge  Taxonomist
2017-04-07 14:37:30 +0200 received badge  Nice Answer (source)
2017-01-07 17:19:06 +0200 received badge  Famous Question (source)
2016-08-25 12:13:19 +0200 received badge  Self-Learner (source)
2016-08-25 12:13:19 +0200 received badge  Necromancer (source)
2015-10-28 03:23:44 +0200 received badge  Notable Question (source)
2015-10-28 03:23:44 +0200 received badge  Popular Question (source)
2015-08-28 22:37:49 +0200 received badge  Famous Question (source)
2015-06-19 16:03:23 +0200 received badge  Famous Question (source)
2015-04-10 04:10:20 +0200 received badge  Famous Question (source)
2015-03-20 16:18:56 +0200 received badge  Famous Question (source)
2015-03-10 06:37:09 +0200 commented question How I locate words and sentences within a LibreOffice document?

Your question doesn't make sense to me. Are you aware of the "find" feature? Try Edit -> Find... and then type in the text you want to find.

2015-03-10 06:35:24 +0200 commented answer Link to certain cells (page views) within Calc?

Perfect! Actually that's even better than I had done it some years ago. Nice to see that it plays nice with named ranges, also. If you name a cell or a range "pig" (without the quotes), you can link to it with =hyperlink("#pig","click here"). Good to know!

2015-03-10 00:51:08 +0200 commented answer External link to display image in writer?

@bob_h, please use the green checkmark to accept an answer, not the "close question" option.

2015-03-09 11:54:58 +0200 commented question Add serial number to Calc template

I think I get what you're asking for. Like for instance if the template is an invoice template, you want each new invoice made using that template to be numbered with the next sequential invoice number, which can never be altered by the person using the template—is that correct?

2015-03-09 11:50:33 +0200 commented question External link to display image in writer?

Might want to edit this to keep the title and the question separate. The title should be a very short summary and the question should be written in the place where you put only your system details.

What email application are you working with?

2015-03-09 11:46:22 +0200 answered a question Calc macro to refresh an advanced filter, or formula to workaround?

This question has generated more views than anything else I've posted here, so I thought I would come back and write up how I finally solved this situation. I never did get advanced filters to refresh automatically. Instead, I did the following:

  1. Create an additional column alongside the data to precalculate the more complicated parts of the criteria. This just needs to be a boolean and evaluate to TRUE or FALSE. 1a. If there is data in your table you need to handle in more than one way, or group in some fashion, create extra columns to handle the grouping. For example if you may need to filter by week or by month sometimes, and you already have a "date" column, add a "week" and a "month" column which are precalculated based on the "date" column.
  2. Create a pivot table. Put all the column headings you need in the "rows" section and nothing at all in the "columns" section of the pivot table design.
  3. If there is one of the columns that the user will routinely want filtered for only one value, add that column heading to the "page" section of the pivot table design to allow easy filtering.
  4. Refresh the pivot table before each use.

In my case, I set the pivot table to sort by student, then subject, then date, by simply putting these three column headers into the "rows" section of the pivot table design in that sequence. I created a "semester" column in the original data table which just displayed the semester according to the date entered in each row, and added "semester" to the pivot table design in the "page" section, to allow the user to filter for any one specific semester. I created another column in the original data entitled "FOR_GPA" with a complicated formula simply designed to determine, yes or no, whether the data in that row should be calculated into the student's GPA (e.g. yes for usual tests, no for electives, etc.) This worked beautifully and totally solved the "how to reapply advanced filter" problem as well as allowing for greater flexibility in how the data was presented and summarized.

If any part of the above is unclear please comment below and I will try to clarify.

2015-03-09 11:29:24 +0200 answered a question Multiple translated variants of Writer document

Very interesting question; there may be some way. Depending on the scale of your application, you may consider investing the time in a proper setup. For instance, if the document is very large, you may consider splitting it into multiple small files. The "date modified" timestamp on each would then provide you with the info you need at a glance, in Finder or Windows Explorer. Then you could create three "full files" linked to all the little chapter files. (I'm getting this idea from the InDesign book writing workflow, wherein each chapter is a single file and the book is another file that simply includes all the chapters.)

I don't know that there is any way to do this version tracking from WITHIN LibreOffice—I don't think version control is LO's strong suit. But the above method would be a decent workaround, depending on how long the document is and how unwieldy it would be to split it up.

2015-03-09 11:20:15 +0200 answered a question When I cut or copy and then paste to Writer, text appears in table. Never did before. How do I get it to appear as text alone?

Go to "Edit" -> "Paste Special..." and choose "Unformatted text." Should handle it.

If not, paste it into a TXT file first. That's a good way to strip out inline images as well as other odd formatting issues. Use TextEdit (with rich formatting off) on a Mac, or Notepad on a Windows. But the "Paste without formatting" given in my first sentence will handle almost all scenarios.

2015-03-09 11:03:55 +0200 asked a question Link to certain cells (page views) within Calc?

Hello, I remember I had done this successfully some years ago but I can't remember how I set it up:

On a giant sheet which contained many charts, I set it up so that there was a sort of "table of contents" (manually set up) at the very top left, so when you opened that sheet you would see cells which you could click to view all the charts in that sheet.

For example, cell C5 might contain text reading "click here to view chart ABC." When you click C5, your view would jump so that cell AA576 was in your top left corner, and you were looking at chart ABC. Then cell AB577 would have text saying "click here to go back to the table of contents" and you would click it and your page view would jump to A1 in the top left.

It was very handy for navigating large sheets and made the file much more user friendly than using vast amounts of scrolling or switching between vast numbers of sheets. I don't remember what option I used to make this type of link. Can anyone help with this?

2015-03-09 09:33:41 +0200 commented answer lock cell for recalculation by checkbox

I think I understand. Why not just have the invoice number entered manually? Or, if you like, copy and paste it from a single cell "next invoice number" which will automatically show the next invoice number. That way you don't need to mess around with locking cells.

It seems you are trying to use "locking cells" to prevent recalculation, not to prevent editing by the user. That is different. The best way to prevent recalculation is to enter the data as a static value, not a formula.

2015-03-09 09:24:05 +0200 commented answer Convert number to date in Base (or compare to a date at least)?

Hi pierre, I just thought I'd mention that what you suggested here (rounding the date in a separate column) is what I ended up doing. I came up with the idea on my own, but thought you'd like to know that yes, that is a good solution. :)

2015-03-04 17:05:35 +0200 received badge  Famous Question (source)
2015-02-10 17:31:44 +0200 received badge  Notable Question (source)
2015-02-10 00:28:24 +0200 received badge  Enthusiast
2015-02-09 08:46:28 +0200 received badge  Notable Question (source)
2015-02-09 04:17:05 +0200 answered a question lock cell for recalculation by checkbox

It looks like you want a one-way operation to easily lock the contents of a given cell (but not to unlock them). I can't see any other function that the checkboxes are supposed to perform.

Firstly, I will say that there is almost certainly a much, much easier way to accomplish the basic task you are trying to perform here. I say this because your question is far from the usual requirements for an Excel or Calc sheet. If you give more information on what you are basically trying to accomplish, it may be possible to recommend a much simpler route. (i.e., what were you trying to get Calc to do that led to the need for these checkboxes? What was the original, simple idea of what you wanted the computer to help you with?)

However, I will attempt to answer your question as asked.

The only way I am aware of to lock a cell's contents so they cannot be edited is by protecting the sheet it is on. You do this by selecting Tools->Protect Document->Sheet... and clicking OK. (The default settings are usually what you want.)

By default, this will mean ALL cells on that sheet are protected. It is more usual to need to UN-protect one or two cells than to need ONLY to protect one or two cells. However, you can get around this. You just unprotect all of the cells before you protect the sheet:

  1. Select the entire sheet. (Click in the upper left corner of the sheet, just to the left of "A" and just above "1".)
  2. On the menu, go to Format->Cells...
  3. In the "Cell Protection" tab, uncheck the "Protected" box.
  4. Click OK.
  5. Select Tools->Protect Document->Sheet...
  6. Click OK.

The sheet is now ready for use.

When you want to protect a particular cell: 1. Go to Format->Cells... 2. Check the "Protected" box in the "Cell Protection" tab. 3. Click OK.

You will not be able to change any cell back to "unprotected" without unprotecting the entire sheet first. You can set a password for the sheet protection, if it's important to have your "individual cell protecting" functionality be truly one-way.

(If this answers your question, please click the green checkmark.)

2015-02-09 03:57:39 +0200 received badge  Popular Question (source)
2015-02-09 03:56:23 +0200 commented answer Why isn't VLOOKUP working here?

This is interesting. I've used MATCH and INDEX before, but it was a long time ago and I never conceptualized what they were for. Would you mind giving a comparative example of how they could be used together (or apart, whatever) to accomplish the same thing as a given VLOOKUP instance?

2015-02-09 03:54:38 +0200 commented answer Why isn't VLOOKUP working here?

Karolus, would you mind converting that comment to an answer? That IS the information I was looking for. (Although JohnSUN's answer is very helpful and practical! ;) You gave WHY it only worked sometimes, which was the mystery for me.

2015-02-09 02:24:33 +0200 received badge  Notable Question (source)
2015-02-08 15:39:03 +0200 received badge  Popular Question (source)
2015-02-08 10:17:39 +0200 commented answer Why isn't VLOOKUP working here?

Well that WORKS...but it seems weird that without that parameter, it worked sometimes but not always. I couldn't figure out any pattern, so I noted some different values for C1 that cause different cells to fail.

For now I'll use the sorting parameter so I can continue working, but.......? Can you see why it is working in A5 and B5 when that parameter is omitted?

2015-02-08 10:14:32 +0200 answered a question How to change link associacion?

If you right-click and choose "Edit Hyperlink..." you will get some useful options. Your link may be set to "Document" instead of "Internet".

It is also possible to show text different from the link, for example "my website" which when clicked will take you to www.google.com or whatever.

(If this is the answer you needed, you can mark it: green checkmark.)

2015-02-08 10:10:52 +0200 commented question Query calculation in Base

JohnSUN, you may as well convert your comment to an answer. I think you nailed it in one. And if not, you can always edit your answer. (I was going to upvote it because you said exactly what I was thinking.)

2015-02-08 10:03:52 +0200 commented question lock cell for recalculation by checkbox

Can you give a bit more clarity on what you're trying to accomplish? The file you attached doesn't include any formulas at all. It's just numbers and text entered in a grid. Wouldn't you like something to be automatically calculated (or figured out, whatever) by the spreadsheet? If so, what?

2015-02-08 10:00:39 +0200 commented answer How do I keep a running balance when blank rows can exist?

Click the green checkmark next to whichever answer really answered the question.

2015-02-08 09:56:49 +0200 asked a question Why isn't VLOOKUP working here?

Okay, I was playing around with numbers in a Calc sheet and one of the formulas started doing something bizarre. It's the same formula in A5, B5 and C5 (with different reference cells), but C5 isn't working. Note that if you change C1 to "11", none of A5:C5 will work. If you change C1 to "3", A5:B5 will not work but C5 will.

I'm not trying to do something complicated with those formulas—just look up the number "1" in another column, then return the value of the cell right next to the number "1."

What gives??? Why the errors and why only sometimes??? (P.S.: I tried "LOOKUP" also with the same results.)

C:\fakepath\modular math.ods

(For those interested in what the heck all the other formulas are, I'm playing around with the extended Euclidean algorithm and modular arithmetic equations.)

2015-02-06 10:09:06 +0200 answered a question libreoffice base: dropdown list inside a table

I remember when I first looked into Base, before I had any real knowledge of the theory of databases, and I looked for the feature you're looking for. It doesn't exist, but there is a better way to accomplish the same thing. Let me explain:

First, you should understand "primary keys" and "foreign keys". If you are kind of fuzzy on understanding those, or don't instantly know exactly what they are and what is the difference between them, read this tutorial until you get enough theoretical background.

Now, I'll describe the feature the way I imagined it, before I knew anything about databases. I wanted to choose a name (first and last name) out of the "customers" table when inputting data into the "orders" table. I wanted a drop down list to show in the "customer" field of the "orders" table, which would concatenate the first and last names of each customer from the "customers" table.

If you understand primary keys, you will easily see that I didn't understand them at the time.

The actual way to solve what I was attempting is to have a primary key in the "customers" table which would be an integer, not the names. In the "orders" table I would have a foreign key linked to the primary key of the "customers" table.

Of course, this would be utterly useless for entering information directly into the "orders" table, because I would have to just type in a number for the customer, and just remember that "customer #65536" is "Bob Jimson". Not going to happen.

The rest of the answer is to make a FORM. In a form, NOT in a table, is where you can get your drop down boxes. The form can only be created after the "customers" and "orders" tables have both been created. It can be set up so when you go to enter a new order, using the form, you get a drop down list showing the first and last name of each customer. When you choose a customer from the list, that customer's ID# (primary key) is entered in the "customer" column of the "orders" table (as a foreign key) for the order record you are creating.

The "how to" on this is covered quite well in the tutorial I linked to, but this should hopefully give you some idea of how the pieces fit together.

(If this answered your question, you can mark it as answered — green checkmark.)

2015-02-06 06:46:04 +0200 commented question Where is the list of Base SQL functions that work when using Calc as a datasource?

Oh, and by the way, extra hidden columns (plus pivot tables) is totally the way to go if anyone else runs into this problem ever. Either that, or skip Calc entirely and do everything from Base.

2015-02-06 06:44:13 +0200 commented answer Where is the list of Base SQL functions that work when using Calc as a datasource?

Kind of absurd that those functions haven't been expanded in the last 12 years...that document is dated 2003. However, everything I've tried from that list seems to work, and everything I couldn't get to work (before asking my question here) isn't on that list. So I think it IS the currently valid list. :) Thanks.

2015-02-06 06:42:34 +0200 received badge  Notable Question (source)
2015-02-06 04:33:42 +0200 commented question mathtype equation numbers

By the way, I don't see a way to EDIT the equations, just to view them (on Mac OS X).

Unfortunately I don't have a way to see the file on Linux to even see if I can figure it out.

2015-02-06 04:31:27 +0200 commented answer Is there any way to make the slide show setting "Mouse pointer visible" default on

Slide Show -> Slide Show Settings... -> Check the "Mouse pointer visible" box.

I didn't test if it works because I've actually never used Impress and I don't have any slides to show, but the setting is there, at least.