A cell containing both a function and an input option

Discuss the spreadsheet application

A cell containing both a function and an input option

Postby richardgmsummers » Sun Oct 14, 2018 6:16 pm

Hi. I may be trying to do the impossible - but it's worth asking. In open office calc is it possible to set up a cell so that it has all of the following:
1) contains a function that incorporates the values in neighbouring cells
2) also allows that same cell to have a value manually inputted into it. This inputted value is to override the function and become that cells value.
3) If the manually inputted value is deleted then that the cell would revert back to the aforementioned function

This could maybe be achieved through a tool that sets a cell's default state as something other than empty/blank or maybe a tool that can create layers within a cell where conditions decide which layer is active. Is anything like this possible? Thank you in advance for any thoughts.
richardgmsummers
 
Posts: 2
Joined: Sun Oct 14, 2018 10:10 am

Re: A cell containing both a function and an input option

Postby Zizi64 » Sun Oct 14, 2018 7:55 pm

Use a helper cell to input values and use a conditional formula in tha main cell.

=IF(ISBLANK(TheHelperCell);aFormulaWhenTheHelperCellIsEmpty;aFormulaWithTheValueOfTheHelperCell)
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.1 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.2 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7376
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: A cell containing both a function and an input option

Postby RusselB » Sun Oct 14, 2018 8:23 pm

Quite simply, in most spreadsheet programs, you can't do all that you are asking for, as entering a value into a cell that contains a formula destroys the formula for that cell.
I only know of one spreadsheet program that has (had?) the ability to recall a previous formula after the entry had been changed using a fixed value.
Forum rules stop me from specifying the program, and I don't know if the most recent versions still have this ability.

That said, Zizi's suggestion of using a helper cell matches the best option that I can come up with.
OpenOffice 4.1.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer
 
Posts: 4657
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: A cell containing both a function and an input option

Postby Zizi64 » Sun Oct 14, 2018 8:44 pm

...or you can create your own macro for this task. Here is a similar one:

Event_ContentChanged2.ods
(12.8 KiB) Downloaded 13 times
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.1 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.2 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7376
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: A cell containing both a function and an input option

Postby richardgmsummers » Mon Oct 15, 2018 11:34 pm

I will investigate macros. Thank you
Apache OpenOffice 4.1.1
Windows 10 Home
richardgmsummers
 
Posts: 2
Joined: Sun Oct 14, 2018 10:10 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 17 guests