Cell content from specific values - default value

[Albireo]

I have test different settings with data/validity
(swedish version LO - I hope it's the same function and names in OO)

The test data includes the VAT rates - (in Sweden - 0%, 6% 12% and 25%)
If I understand correctly, the VAT rates can be defined as validation values in two ways. As .:
- Input help from cells

Validate from cells (Swedish version)

- Selected cells

Don't need cells to validate values

I like the function, but I want to set a default value to that cell (eg. 25%)
An example .:
- B3 = (empty)
- C3 = Validated cell (as 0%, 6% 12% and 25%)

My wish is the following .:
If B3 = empty -> C3=empty
if B3 <> empty -> C3=25% (the default value)
(and with the option to change the value to another value (in this example 0%, 6% 12%)

[RusselB]

Aside from B3 being empty or not empty, is there any relation between the data entered in B3 (or anywhere else in the spreadsheet) that would determine which VAT value is to be used?
As an example, if I buy food at the store the taxes are 0%, but if I buy food at a restaurant with a value of $4 or less, then taxes are 8%, yet if I buy food at a restaurant and it's over $4, then taxes are 13%.

[Albireo]

My wish is to create a manually managed invoice. There is nothing on the invoice that specifies which tax rate to use. Access to that information is in a CSV-file, but had intended not to write a macro for this (and I have no idea how to do that).
In almost 100% of cases, the tax rate is 25%. The VAT field is included in the detailed invoice specification

The following fields are included in the specification for each product / service:

• A - article / product number
  B - Item and description
  C - Quantity
  D - Unit
  E - Price with VAT
  F - Price without VAT
  G - VAT rate
  H - Discount per unit
  I - Row sum without VAT

Normally all fields should be empty (as it seems)

- Column C - Quantity - When filling in the quantity, the unit "pieces" should appear. (will be "pcs" in almost 100% of cases)
(But can be changed to packages, grams or something)

- Column E - Price with VAT and Column F - Price without VAT - If one of the fields is filled in, the other field will be calculated from column G
eg If coumn F is 8 and column G is 25% - column E is counted to .: F + (F * G) = 10
Have not thought about how the formula for column F will be (but it's more complicated - I know it's column E * 0.8 - but how does the formula become?)

Now to my main question .:
- Column G - If no quantity is filled, no percentage shall be shown, but if quantity is filled the percentage shall be shown as 25%.
Now it is possible to change this cell to another value such as 0% or 6% or .. If column G (VAT) is changed, new calculations must be made in column E or F.
The solution may also work on column D - Unit?

[Villeroy]

This is what databases are made for. A database can insert a default value into a column when a new row is going to be stored with that column value missing. With a spreadsheet you have a lot more problems than this one. You can leave out that value and complement it by means of a formula like: =IF(NOT(ISNUMBER(A1));$X$1;A1) with A1 being the cell in question and X1 as default value. Then you do any further calculation with the calculated formula.

[Albireo]

Thanks!

Today, someone fills in a PDF form, when an invoice is to be created (without any checks)
I just want to improve the invoice form a little, and thought a default value on a cell would be easy to introduce.

Intresting!
This time .:
- I first wrote the formula (like this in B1 .: =IF(A1<>"";$G$1;"") and in G1 I have 25%)
- Then I make the Validity
Now it works! (in some way) - I have done this in the opposite way before (first validity then formula)
if the value in A1 is cleared - the cell (B1) of the formula disappears.
But if I made a new choice in B1 eg. 0% the formula is gone.
Now the cell must be handle manually (In this case, it's no problem - as I can see.)
(My problem was I handle the cell in wrong way)
--------------------------------------------------------------------
The last time I tested the LO / OO base - a few years ago - these programs didn't feel ready - bugs / features that didn't work - and so on. I'm bad at analyzing if something doesn't work - have I done wrong or is it a bug or ...? It takes me many hours - maybe days to analyze the problem. - the only thing I know, it doesn't work for me.
The documentation was not good. I could not find any tutorial (only on easy things).
Right now I don't understand how my problem could be solved with Base.
Do you have some example? Is macro programming required? etc.
Is there a program / documentation to be able to manage a LO / OO database through eg. ODBC?
(What kind of questions can be asked to OO Base - type of SQL queries?)
Have another task that I would like to solve with a database - but don't really know how to start.
--------------------------------------------------------------------
I'm glad that Writer and Calc have come further (for example, Calc crashes less often, and built-in features have been developed). One limitation I come across is that Calc is not the fastest program. (it take time with many formulas)
Another thing I don't like is that the programming language has also been translated from English to eg. Swedish - it makes it difficult to get help - many times. Villeroy:s instruction look like this in Swedish version

Code: Select all

=OM(ICKE(ÄRTAL(A1));$X$1;A1)

[Villeroy]

I can offer viewtopic.php?t=73154&p=329930#p329930 where you enter some values into sheet cells and get default values and calculated fields inserted by a (recorded) macro. It is just a proof of concept about non-trivial recorded macros. Instead of validation one could use a list box form control with a default value and link this form control to a single sheet cell. This might work well with my cheap form. Again: no professional would do this with sheets. Never ever.