You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openoffice.apache.org by Vince-WA2RSX <wa...@verizon.net> on 2015/02/04 17:51:53 UTC
Re: Drop Down Menu in AOO4.1.1
Hi Dennis:
Yes, you have interpreted my meaning very nicely.
OK; a calendar pop out (1) or an utility (2) would be a welcomed
addition to AOO Calc.
Does a feature exist whereby I can insert Today's Date into a Calc cell?
I've yet to begin to play with AOO Base.
Regards,
Vince_B.
On 1/28/2015 8:16 PM, Dennis E. Hamilton wrote:
> Let me try my understanding and perhaps help to calibrate what you are after.
>
> 1. Consider a button that would pop out a small calendar in the usual format, with navigation from month to month and the typical 4-5 rows of date numbers against days of the week. There might also be some convenience for navigating to distant months that do not have the current date.
>
> Clicking on a date would cause the specified date to inserted into a currently-selected cell. Closing the calendar without clicking would not make an entry. If the calendar pop-out is kept active, other dates could be selected into other cells.
>
> 2. Another pop-out might be more like a calculator that works with dates. That is more of a stretch. Some of your examples suggest that kind of activity.
>
> Q1. With regard to (1), which might be an interesting exercise for creation of an extension, I wonder how close one can get already by the ways that date-formatted cells accept abbreviated entries. For example, entering a month and day, such as 01-28 (today in my locale settings) can automatically enter a full calendar date in the current year. This is commonplace. I don't know what would happen if I entered just 28 when the cell is already formatted to contain a date.
>
> I know this is not the same as being able to look at a calendar and use the orientation that provides to help us determine a past or future date. Is it any saving of effort, so long that you have a calendar handy?
>
> Q2. Would a utility that ran outside of OpenOffice Calc that provided (1), and put a full calendar date (and even times converted from the local time) onto the clipboard for pasting into a document of any kind, including Calc cells be a step in the right direction that would give you some of the working efficiency you care about?
>
> Note that I have omitted consideration of date calculations since those are easily accomplished with Calc already, just as calculations among other cell entries. It takes some understanding of cell formatting for date-times and for time intervals, but there is extensive support in Calc already.
>
> - Dennis
>
> PS: For any follow-up, please reply to users @ openoffice.apache.org where other experts may have advice and others can learn the solutions to common problems. The OpenOffice Forums have an extensive knowledge base at https://forum.openoffice.org/,
>
>
>
> -----Original Message-----
> From: Vince B. [mailto:wa.tworsx@verizon.net]
> Sent: Wednesday, January 28, 2015 14:38
> To: users@openoffice.apache.org
> Subject: Re: Drop Down Menu in AOO4.1.1
>
> There are no suggested values; I manually monitor data on a random date (whenever I happen to be close to an elapsed time meter that is connected to my heating system. I write the data on paper, and note also the date. Then, I enter the collected data into my AOO spreadsheet.
>
> I am afraid I failed to be clear about what I desire. I am looking for a Calendar-type of dropdown menu to be repeated within a row of spreadsheet cells. I don't know the correct name ! (Might need a macro? Or, perhaps an add-on to AOO.). Thus, I have not been successful searching in AOO's Help file.
>
> As I mentioned, I seek an easier way to enter a calendar date; similar to what may be found in a financial s/w (e.g., checkbook register entry) or in a Calendar s/w, where I can quickly select a desired Month, then the Day of that month, all within a preselected Year. It would be great if the DD/MM/YYYY is repeated (unchanged) until I menu-select a new DD/MM/YYYY.
>
> Is there such a function in AOO? Once the date is selected, the spreadsheet formula calcs the elapsed number of days between one cell and the immediate next cell. For example:
>
> A1 contains the date of, last Thursday; today is Wednesday, which is set in A2. I need to count the number of days; A2-A1, to calc amount of Heating Fuel consumed. .... Counting the number of elapsed days is not the issue; quick selection of revised date data is.
>
> Maybe such a dropdown menu is available in AOO Base, and I will need to combine Base and Spreadsheet?
>
>
> Regards,
> VinceB
>
> Sent from my iPad
>
>> On Jan 28, 2015, at 11:42, Brian Barker <b....@btinternet.com> wrote:
>>
>> At 11:19 28/01/2015 -0500, Vince Bonly wrote:
>>> Can someone point me to info on creating a dropdown menu in a Spreadsheet cell, if this is possible? I have created a spreadsheet to calculate Heating Fuel consumption, and frequently need to enter a specific date into a cell. A dropdown menu, I think, would save me time when needing to do this.
>> o Enter the suggested values into cells somewhere in the spreadsheet. (These can be tucked away somewhere.)
>> o Potentially select this cell range and use Insert | Names > | Define... to give the list a name.
>> o Select the target cell or cell range.
>> o Go to Data | Validity... .
>> o On the Criteria tab, for Allow, select Cell Range.
>> o Click in the Source box and either drag over the list or type its name.
>>
>> There are many options you can adjust, such as "Show selection list" (which you will want), as well as "Allow blank cells", and providing some help text when the target cell is selected. The Error Alert tab allows you to control whether values not in the list are permitted (Warning instead of Stop) and an error message that can be produced.
>>
>> I trust this helps.
>>
>> Brian Barker
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
>> For additional commands, e-mail: users-help@openoffice.apache.org
>>
>
Re: Drop Down Menu in AOO4.1.1
Posted by "Vince B." <wa...@verizon.net>.
No problem.
Regards,
Vince
Sent from my iPad
> On Feb 4, 2015, at 14:48, Dave Barton <db...@tasit.net> wrote:
>
> -------- Original Message --------
> From: Brian Barker <b....@btinternet.com>
> To: users@openoffice.apache.org
> Date: Wed, 04 Feb 2015 18:55:53 +0000
>
>> At 19:30 04/02/2015 +0100, Dave Barton wrote:
>>> From: Vince Bonly:
>>> Date: Wed, 04 Feb 2015 11:51:53 -0500
>>>> Does a feature exist whereby I can insert Today's Date into a Calc cell?
>>>
>>> Insert Today's Date = Hold down the keyboard "CTRL" key and press the
>>> ";" semicolon key.
>>
>> Er, is that in OpenOffice Calc - or perhaps only in Microsoft Excel and
>> LibreOffice Calc?
>>
>> Try:
>> o Enter =NOW()
>> o F2; F9; Enter; Enter.
>>
>> I trust this helps.
>>
>> Brian Barker
>
> Sorry Vince, Brian is correct the keyboard shortcut I suggested is for
> LibreOffice. It's easy to get confused when you support both projects.
>
> Dave
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
Re: Drop Down Menu in AOO4.1.1
Posted by Dave Barton <db...@tasit.net>.
-------- Original Message --------
From: Brian Barker <b....@btinternet.com>
To: users@openoffice.apache.org
Date: Wed, 04 Feb 2015 18:55:53 +0000
> At 19:30 04/02/2015 +0100, Dave Barton wrote:
>> From: Vince Bonly:
>> Date: Wed, 04 Feb 2015 11:51:53 -0500
>>> Does a feature exist whereby I can insert Today's Date into a Calc cell?
>>
>> Insert Today's Date = Hold down the keyboard "CTRL" key and press the
>> ";" semicolon key.
>
> Er, is that in OpenOffice Calc - or perhaps only in Microsoft Excel and
> LibreOffice Calc?
>
> Try:
> o Enter =NOW()
> o F2; F9; Enter; Enter.
>
> I trust this helps.
>
> Brian Barker
Sorry Vince, Brian is correct the keyboard shortcut I suggested is for
LibreOffice. It's easy to get confused when you support both projects.
Dave
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Drop Down Menu in AOO4.1.1
Posted by Brian Barker <b....@btinternet.com>.
At 17:29 05/02/2015 -0500, Vince Bonly wrote:
>On 2/4/2015 3:31 PM, Brian Barker wrote:
>>At 15:13 04/02/2015 -0500, Vince Bonly wrote:
>>>On Feb 4, 2015, at 13:55, Brian Barker wrote:
>>>>From: Vince Bonly:
>>>>Date: Wed, 04 Feb 2015 11:51:53 -0500
>>>>>Does a feature exist whereby I can insert Today's Date into a Calc cell?
>>>>
>>>>Try:
>>>>o Enter =NOW()
>>>>o F2; F9; Enter; Enter.
>>>
>>>After formatting the cells to Date (MMM D, YYYY) your suggestion of
>>>
>>>=NOW()
>>>
>>>Worked splendidly; thanks!
>>
>>By itself, that won't do what you need. It creates a formula whose
>>value is the date (and possibly time) when the cell contents were
>>last calculated. If you enter it today you will see today's date,
>>but if you save the spreadsheet and reopen it next week you will
>>see *that* day's date, not the original date that I take it you want.
>>
>>There are various ways to freeze the result of the formula so that
>>it becomes a value and will not change on future dates, and the
>>sequence F2, F9, Enter, Enter is one of them. Another is to copy
>>the cell contents back over themselves using Paste Special... with
>>Formulas unticked.
>
>Actually, what I need is "current date" whenever I reopen the spreadsheet.
Forgive me, but I suspect that you don't! Yes, I understand that you
need to put the current date into your new entries you make on that
day, but surely you don't want all the date entries you made on
previous days automatically to update themselves to the same current
date - so that all dates are identical and no longer show the dates
the entries were made? That way, whenever you looked at the
spreadsheet, *every* date would be today!
>I do not understand about the "F2, F9, Enter, Enter" sequence (is
>that a separate/alternative sequence, or required with the =NOW()?); ...
It follows =NOW(), as described above.
>... it does not work here (using AOO4.1.1, Spreadsheet) when I tried
>that suggestion.
For you, the problem with =NOW() is that it is a formula, not a way
of inserting a fixed value. Like all formulae, it is recalculated as
appropriate. It means what it says: now, today, this very moment -
and it means that always. If you are to use it to create the current
date but which then remains as whatever it was when you entered it,
you need to evaluate the formula to a value, which then will not
change. There are various ways to do this:
o Enter =NOW()
o Select the cell.
o Copy the cell.
o Paste the copied value back over the cell, but using Edit | Paste
Special... (or Ctrl+Shift+V) instead of ordinary Paste.
o In the Paste Special dialogue, ensure that "Date & time" is ticked
but Formulae is not ticked.
(Er, you have to do *all* those steps: they are not alternatives.)
The other suggested sequence is another way to achieve the same effect:
o Enter =NOW()
o Select the cell.
o Press F2. This toggles Edit Mode, so you can now edit a formula in
the cell itself. You will see now the formula, not its current value.
o Press F9. This recalculates the contents of the cell formula. You
will see the current date and time in a pop-up.
o Press Enter. This accepts the value in the pop-up and enters it in
the cell. (This is now a value, no longer a formula.)
o Press Enter. This confirms the cell editing (undoing the effect of
F2 above), so the value is now reformatted according to the existing
cell format.
>Maybe I need to preprogram the Function keys; I have not done that, as yet.
No need: these are default keyboard shortcuts.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Drop Down Menu in AOO4.1.1
Posted by Vince-WA2RSX <wa...@verizon.net>.
Actually, what I need is "current date" whenever I reopen the
spreadsheet. This will save me time and avoid typos, since I have fat
fingers and poor vision! I do not need a time stamp included for my Oil
Consumption calculations.
I do not understand about the "F2, F9,Enter,Enter" sequence (is that a
separate/alternative sequence, or required with the =NOW()?); it does
not work here (using AOO4.1.1, Spreadsheet) when I tried that
suggestion. Maybe I need to preprogram the Function keys; I have not
done that, as yet.
On 2/4/2015 3:31 PM, Brian Barker wrote:
> At 15:13 04/02/2015 -0500, Vince Bonly wrote:
>> On Feb 4, 2015, at 13:55, Brian Barker wrote:
>>> From: Vince Bonly:
>>> Date: Wed, 04 Feb 2015 11:51:53 -0500
>>>> Does a feature exist whereby I can insert Today's Date into a Calc
>>>> cell?
>>>
>>> Try:
>>> o Enter =NOW()
>>> o F2; F9; Enter; Enter.
>>
>> After formatting the cells to Date (MMM D, YYYY) your suggestion of
>>
>> =NOW()
>>
>> Worked splendidly; thanks!
>
> By itself, that won't do what you need. It creates a formula whose
> value is the date (and possibly time) when the cell contents were last
> calculated. If you enter it today you will see today's date, but if
> you save the spreadsheet and reopen it next week you will see *that*
> day's date, not the original date that I take it you want.
>
> There are various ways to freeze the result of the formula so that it
> becomes a value and will not change on future dates, and the sequence
> F2, F9, Enter, Enter is one of them. Another is to copy the cell
> contents back over themselves using Paste Special... with Formulas
> unticked.
>
> Brian Barker
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
>
Re: Drop Down Menu in AOO4.1.1
Posted by Brian Barker <b....@btinternet.com>.
At 15:13 04/02/2015 -0500, Vince Bonly wrote:
>On Feb 4, 2015, at 13:55, Brian Barker wrote:
>>From: Vince Bonly:
>>Date: Wed, 04 Feb 2015 11:51:53 -0500
>>>Does a feature exist whereby I can insert Today's Date into a Calc cell?
>>
>>Try:
>>o Enter =NOW()
>>o F2; F9; Enter; Enter.
>
>After formatting the cells to Date (MMM D, YYYY) your suggestion of
>
>=NOW()
>
>Worked splendidly; thanks!
By itself, that won't do what you need. It creates a formula whose
value is the date (and possibly time) when the cell contents were
last calculated. If you enter it today you will see today's date, but
if you save the spreadsheet and reopen it next week you will see
*that* day's date, not the original date that I take it you want.
There are various ways to freeze the result of the formula so that it
becomes a value and will not change on future dates, and the sequence
F2, F9, Enter, Enter is one of them. Another is to copy the cell
contents back over themselves using Paste Special... with Formulas unticked.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Drop Down Menu in AOO4.1.1
Posted by "Vince B." <wa...@verizon.net>.
After formatting the cells to Date (MMM D, YYYY) your suggestion of
=NOW()
Worked splendidly; thanks!
Regards,
Vince
Sent from my iPad
> On Feb 4, 2015, at 13:55, Brian Barker <b....@btinternet.com> wrote:
>
> At 19:30 04/02/2015 +0100, Dave Barton wrote:
>> From: Vince Bonly:
>> Date: Wed, 04 Feb 2015 11:51:53 -0500
>>> Does a feature exist whereby I can insert Today's Date into a Calc cell?
>>
>> Insert Today's Date = Hold down the keyboard "CTRL" key and press the ";" semicolon key.
>
> Er, is that in OpenOffice Calc - or perhaps only in Microsoft Excel and LibreOffice Calc?
>
> Try:
> o Enter =NOW()
> o F2; F9; Enter; Enter.
>
> I trust this helps.
>
> Brian Barker
>
Re: Drop Down Menu in AOO4.1.1
Posted by Brian Barker <b....@btinternet.com>.
At 19:30 04/02/2015 +0100, Dave Barton wrote:
>From: Vince Bonly:
>Date: Wed, 04 Feb 2015 11:51:53 -0500
>>Does a feature exist whereby I can insert Today's Date into a Calc cell?
>
>Insert Today's Date = Hold down the keyboard "CTRL" key and press
>the ";" semicolon key.
Er, is that in OpenOffice Calc - or perhaps only in Microsoft Excel
and LibreOffice Calc?
Try:
o Enter =NOW()
o F2; F9; Enter; Enter.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Drop Down Menu in AOO4.1.1
Posted by Dave Barton <db...@tasit.net>.
-------- Original Message --------
From: Vince-WA2RSX <wa...@verizon.net>
To: users@openoffice.apache.org
Date: Wed, 04 Feb 2015 11:51:53 -0500
> Hi Dennis:
>
> Yes, you have interpreted my meaning very nicely.
>
> OK; a calendar pop out (1) or an utility (2) would be a welcomed
> addition to AOO Calc.
>
> Does a feature exist whereby I can insert Today's Date into a Calc cell?
>
> I've yet to begin to play with AOO Base.
>
> Regards,
> Vince_B.
Insert Today's Date = Hold down the keyboard "CTRL" key and press the
";" semicolon key.
Dave
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org