[Solved] Prompt for input in formula

Discuss the spreadsheet application

[Solved] Prompt for input in formula

Postby Origdogfish » Fri Dec 27, 2019 8:08 pm

Just doing a spreadsheet bookkeeping journal for 2020. I went over my last 5 years and selected the must have spread categories. I used Data>Validity and made a list that prompts the user to select one of the valid categories.

First Question: Is there a way to limit input to only the valid categories displayed in my drop down list..? I can display errors and messages but if the user manually enters something, bypassing the dropdown then it is still accepted as an input.

I have 18 categories the last 2 being "Misc Amount" and "Misc Desc". I prompt the user to select from the list of valid categories and if their choice isn't available, then to select "Misc". Selecting "Misc" copies the Debit Amount to the "Misc Amount" column. I use formulas in each cell to check the input category and to place the amount in the correct column.

Second Question: I would like to have a formula that checks if there is a value in "Misc Amount" and prompts the user to enter text in "Misc Description".
I haven't found how to enter a prompt message for input in a formula... including the Data>Validity method that I used to select valid categories.

Thanks in advance for your time and attention.
Last edited by Origdogfish on Sat Dec 28, 2019 7:10 am, edited 2 times in total.
Open Office 4.1.7 on Windows 10
Origdogfish
 
Posts: 5
Joined: Thu Dec 05, 2019 3:14 am

Re: Prompt for input in formula

Postby MrProgrammer » Fri Dec 27, 2019 10:24 pm

Origdogfish wrote:Is there a way to limit input to only the valid categories displayed in my drop down list?
In general, spreadsheets allow the user to perform any action not specifically prevented by the basic spreadsheet concepts. In contrast, databases prevent the user from performing any action not specifically allowed by basic database concepts. If you need to prevent the user from doing certain things, you want a database, not a spreadsheet.

Spreadsheets do have Data → Validity and Tools → Protect Document, but these controls are primitive and also easily defeated. For example a cell's validity check may stop you from entering (via typing) anything but a positive integer, but you can paste anything you like into the cell including 3.14159, "Seventeen", and "Hello, world!". Using a spreadsheet, the best that you can do is to use formulas to check cells for the allowed values, then use Format > Conditional Formatting to highlight cells which contain values that are not allowed, say formatting them with a red background.

Origdogfish wrote:I would like to have a formula that checks if there is a value in "Misc Amount" and prompts the user to enter text in "Misc Description".
You could use Conditional Formatting to display the Misc Amount in grey font until the user enters Misc Description. The real solution is to use a database, where a form can prevent the user from creating a record which contains an amount without a description.

Read about those Conditional Formatting and Databases in Help → Index or in User Guides (PDF) or searching for topics about them in the Calc Forum. Ask any further questions about databases in the Base forum.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3959
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Prompt for input in formula

Postby Origdogfish » Sat Dec 28, 2019 1:47 am

Thank you MrP for focusing my ongoing education. I will be looking into all of your suggestions. Not eager to take on Base as I am still learning Calc... but I will if I can.

In the meantime I re-read all I could find on Data>Validity and managed to fix, to my satisfaction, the problem with an invalid entry there.

If under Error Alert> Action> selection of "Stop" reverts entry back to the previous entry... even if it was "". Which in my case will either leave the cell blank or with a valid entry.

I had previously selected "Warning" not "Stop" as I thought that was needed to display an error message.

As far as my second question... I would be happy if I could display a message in a formula. Is it possible to protect control codes such as Ctrl-Alt-C in a formula..? I tried with $... no joy.

Thanks again.
Open Office 4.1.7 on Windows 10
Origdogfish
 
Posts: 5
Joined: Thu Dec 05, 2019 3:14 am

Re: Prompt for input in formula

Postby Origdogfish » Sat Dec 28, 2019 7:08 am

Solved...
Not the most elegant solution, but a functional solution after all.

First issue was solved by changing:
Data> Validity> Error Alert> Warning to Data> Validity> Error Alert> Stop
I checked the box in Data> Validity> Criteria to Allow Empty Cells
I also unchecked the box in Tools> Options> Open Office Calc> General> Input Settings> Press Enter To Move Selection
This allows for the active cell to remain on the blank cell awaiting a valid entry... from my dropdown list.

The second issue was fixed in a rather Neanderthal way.
The two columns, side by side are labled Misc Amount and Misc Desc
An entry is placed in Misc Amount by selecting "Misc" from my dropdown list.
I simply put a formula in the Misc Description cell that if the Misc Amount exists display "Enter Desc"
Crude yes. The "Enter Desc" has to be overwritten... but it makes the cell active and prompts for an input.
Mission Accomplished.

Now I can move on to the next Alligator in the swamp.

Thanks all...
Happy Holidays.
World Peace is not only the goal... it is the path.
Open Office 4.1.7 on Windows 10
Origdogfish
 
Posts: 5
Joined: Thu Dec 05, 2019 3:14 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 23 guests