I am creating an ods I want to share with other people (users)
Some of the cells I want to display EITHER the result of a function (or one of several possible functions defined by a data validation drop-down list in a different cell), OR direct user input.
First possible solution I looked for was some function which allowed for “user input” as one of its possible results. I have been unable to find one.
The topic description is the next possible solution I have sought, and would be preferable I think. Still no luck so far.
I have spent apx. 1 full day searching already.
EDIT_For additional detail and clarification.
My .ods is for D&D 5e Character Building.
It will be protected and shared, so other players in my group can input relevant data but most of the data and functions will be in protected cells and hidden sheets.
The details below are not actually accurate to my .ods , instead they are simplified for clarity and to avoid posting pages worth of irrelevant detail/code.
If I find a solution, I will want to replicate it for several uses in my .ods .
I am using this project as a learning tool in order to utilise spreadsheet skills in other more important projects.
What I have
-A cell in Sheet 1 {'CombatStats’A1} which will display the characters Armour Class (AC).
-A cell in Sheet 2 {‘Equipment’.B2} which is a drop-down list for Armour Type.
-A sheet that will be hidden {‘ArmourData’}, with a range of cells {C3:D5}, which forms a table with “Armour Type” and “Formula” as columns.
{‘Equipment’.B2} is the dropdown list. It allows empty cells. Its source is the “Armour Type” column {$‘ArmourData’.C3:C5} .It works without problems or errors.
Each Cell in the “Formula” column of the table in {‘ArmourData’} has its own function to calculate AC. Each of those functions is of the form…
=IF($‘Equipment’.B2=“ArmourType1”; [Specific AC Formula for ArmourType1])
This calculates the ArmourType specific formula only if the dropdown list has that ArmourType selected, otherwise it outputs “FALSE”
All of these formula were working as intended with the dropdown.
What I want
I want the cell {'CombatStats’A1} to essentially be an automatic toggle which will
-EITHER–Allow the user to directly input their AC value
-OR--------Display the contents of the ArmourType Formula associated with the ArmourType selected in the drop-down list if there is nothing already entered in the cell
What I tried
I set the first cell in the drop-down list table in {‘ArmourData’.C3} as “User Input” and left the associated formula cell {D3} empty.
In the {'CombatStats’A1} “AC” cell, I tried…
=IFS($‘Equipment’.B2=$‘ArmourData’.C4;$‘ArmourData’.D4;$‘Equipment’.B2=$‘ArmourData’.C5;$‘ArmourData’.D5; $‘Equipment’.B2=$‘ArmourData’.C3;[ ])
I looked for a code/option that I could enter in place of the [ ] in the above function that would basically say “allow the user to directly enter a value into this cell” .
I could not find one.
Other than that, the function was working perfectly.
I also tried a few variations on that function, as well as other functions including nested IF functions.
All of them fail because any direct entry into a cell automatically overwrites any formula therein.
What I want to try next
Is the question in the title/topic of this post.
Is it possible to have each of the different “ArmourType” functions in the second column of the ‘ArmourData’ sheet print/output their result to the {'CombatStats’A1} “AC” cell?
So something along the lines of
=IF(‘Equipment’.B2=$‘ArmourData’.C5; IF(['CombatStats’A1 is empty]; [calculate Specific AC Formula for C5] THEN [Display the result in the cell {'CombatStats’A1}]; FALSE)FALSE)
So in effect it would
-Check what Armour Type is selected in the drop down.
-Check whether the “AC” cell in {'CombatStats’A1} already has anything written in it.
IF NOT
-Calculate the AC based on the Armour Type selected.
-Print/display/output the result in the “AC” cell in {'CombatStats’A1}
Another, less optimal, workaround I am considering is simply to have a 2nd space for “AC” alongside the {'CombatStats’A1} cell and just obscure their presentation with formatting.
I could then leave one cell/space for user input, have the other cell for function output, and include a “check if empty” clause in each of the specific AC functions.
This would work, but it would leave the formatting/presentation of the “AC” cells/fields less than neat, which is a strong preference for this, and future, projects.
I am realy hoping this has clarified my question and intended outcome, rather than making it more confusing and needlessly complicated.
