How to create a formula linked to an alphanumeric code and date

[TrikerJohn]

My spreadsheet ability is VERY basic, but until now I’ve managed to construct ones that meet my needs.

However, I now need to create a spreadsheet for slightly more complex financial records, and to keep the input quick and simple I would like, if possible, to achieve the following:

Column A allows input of a code - CB1, CS1, etc. Depending upon this code, a value appears in column B. This value will be the product of a simple formula: Miles x mileage allowance. Each code will have a set distance associated with it. The mileage allowance will also be fixed, but may vary in the longer term. I would therefore need the facility to associate it with a date, so that past records remain correct (i.e. use the old allowance) but new entries pick up the latest value.

I anticipate I would need a separate table to enable me to maintain the codes along with their values and textual information about what they relate to. This table would also contain the date-linked mileage allowance values.

To summarise, I would enter a code - say, CS1 - in column A. The formula in column B would then perform the miles x allowance calculation, taking its variables from the separate table and determined by code and date of entry.

I should be very grateful if someone could guide me on how to achieve this, or at least point me to the appropriate section of online help.

TIA

[ajlittoz]

I think a database with entry forms would be better suited. But since your skills are “very basic”, you should get help from a friend.
The database would record the date of issuance of the codes with their “properties” (mileage allowance per distance – if not linear, cost, …). The forms will allow you to enter these properties. Another form is the one to enter the “consumed resources”: date (can be automatically preset to today’s date), code, miles, … and the result is displayed before you press an OK button to record in the DB.

[Villeroy]

Download and open
PriceList.odb (33.3 KB)
Instead of miles changing over time it deals with item prices changing over time.
Open the contained input form “Existing Invoices”.
Navigate through the green list of invoices. Each invoice has a customer and a date.
The yellow list shows the sales related to the selected invoice (quantity and product).
The white list shows the price list at invoice date looked up in the prices table (product, date, price).

[schiavinatto]

Hi @TrikerJohn, post a template file, it’s easier to suggest something. I believe it can be done in Calc.

In a form, data is fed and calculations are made, and only values are saved in the database.