Making a cell that checks manually input cells otherwise checks automatic input cells.

[SnipersLaww]

I have been making a calculation sheet for naval games that I play where I input some data and would get calculated results. (I have been using AI to help build all the formulas cause I dont know a thing really about how they all function and it started getting to a point I felt might be beyond the AI to help) The sheet was getting quite large… too large to just place on my second monitor when playing my game. I noticed though with all the various “calculators” I made that alot of the same data is being requested for multiple calculators. I wanted to try to compress the sheet. For the manually input fields it would be easy to just have a master input section for but some of these calculators give results that are also requested in other calculators. What I wanted to do was have a way so that the automatic fields would check my manual fields first for values first (useful incase I needed to override an automatic value) otherwise if no input was found would check their respective automatic fields for values.

I will try to give a description of how I want the process to go with a screenshot of 2 of my calculators with fields that would cross over. (I am not good with describing things so bear with me)

(white fields are manually input and yellow fields are automatic)

Both calculators have the exact same fields but are solving for different solutions. For an example… I want to compress these 2 calculators down to 1.

Lets say I want to find the AOB. That requires me to know the Target Course and Target Relative Bearing. The Target Relative Bearing is not automated anywhere and therefore will always be manually input… so not an issue. The Target Course and AOB however are both automatic fields and in different calculators with a manual input field in their respective calculators. The Target Course calculator requires knowledge of Own Course, Target Relative Bearing, Target Travel Direction, and AOB. So essentially the 2 calculators both have automatic fields they could use to feed the other… but also require the other field in order to find their own value which would just cause a problem if they exclusively relied on each other. This is where having the automatic fields check for a manual field first and if no value is provided they would then run their formula to provide the automatic answer.
(ex: AOB yellow field would check the manually input AOB field for a value and if no value is provided… it would then run it’s formula where it calculates the Target Course and Target Relative Bearing to then provide an AOB respectively the Target Course yellow field would also be checking its manual Target Course field for a value and if no manual value is available it would run its formula to solve itself aswell.)

I know this is a bit long but I tried my best to explain how things work and what I want to try to achieve.

[Steph1]

Let’s say that the automatic Target Course cell (third row, first column of your example) is the cell A3. Let’s say that the manual Target Course cell (9th row, first column) is the cell A9.
You can create a formula like:
=IF(A9="";A3;A9)*G4/H7+K18
(the part *G4/H7+K18 is invented, you should replace it by your actual calculation formula).

[SnipersLaww]

didnt seem to work

[Steph1]

You need to write the formula in the destination cell which cannot be one of the two possible input cells (the manual entry cell and the calculated cell).

[SnipersLaww]

So this would be a 3 cell situation? manual cells, automatic cells, if cells? I think your idea is to have a 3rd set of cells that check if information is available in the manual input and if not to use the automatic output (if b71 is blank use b91 in cell b111. (made up if cell) Not sure that is going to work because the automatic cells would still have issues. The automatic cells still need to figure out if information is available in the manual cell and if not they will check the corresponding automatic cells for their information. I feel like each reference in the formula would need an “if” situation.

Essentially the automatic cells are the ones that have to check both sections for information otherwise they cant function. I think there was some slight confusion though as the automatic cells I believe are the intended destination cells that I will be looking at for the output answers when I use the sheet.

Hopefully this helps explain things a little more?

[Steph1]

I suggest that you upload a sample file, using this icon:

A fundamental principle of Calc is that a cell either contains a fixed value or a formula, it cannot be both. (Unless you write a macro that can automatically write anything in a cell). You can easily store in a working cell (let’s say H91) the fixed value (B71) or the calculated value (B91) using the formula I already mentioned:
=IF(B71="";B91;B71)
Then use H91 for your calculations.

[SnipersLaww]

sample.ods (20.2 KB)
Hopefully this suffices and I tried to put in extra info regarding some things to hopefully assist with understanding.

[SnipersLaww]

so not sure why it took me this long to think about it… so if i leave the first section as a manual input and have a 3rd section that checks if the manual section or the auto section has information… it uses that information (manual has priority) and then the 2nd section (the automatic formula section) should just check the 3rd section for its info since it then wont have to then try checking 2 different sections within the formula…

i think that might work… ill have to give it a test