Scroll down list with attached formulas

[faqclub]

I have successfully created a scroll down list within LibreOffice Calc consisting of a few dozen or so different options. For example test 1, test 2, test 3 etc

The next step I am having some trouble with. I would like to add a formula linked to each selection. So for example:

I would scroll down the list and select “test 1” in A20

Then manually enter a value in B20 ( example 1288 )

A formula in C20 would then automatically subtract, for example 600 … =(B20-600) the 688 left over in C20 then goes on to further formulas across the page but I have manged to work most of them out all ready

For Test 8

I would scroll down the list and select “test 8” in A21

Then manually enter a value in B21 ( example 3300 )

A formula in C21 would then automatically subtract 1300 … =(B21-1300) to give 2000 in C21

The same test number always subtracts the same number be it – 600 or - 1300 . I would like to link these together to save time as dragging the formula down will not work because all the scrolling options are not clumped together in batches while inputting the data, one wrong input and you need to recheck it all, so linking it together with the drop down selection would work best for me.

[Lupp]

I did not clearly understand as I neither know the term “Scroll down list” nor “attached formula”. Reading the question I had to guess a bit and may have missed something.

Did you think of something as is demonstrated in the attached example?

1. Select from a validity range. (Two cells prepared.)
2. Ctrl+Click on the cell containing the respective HYPERLINK formula.
3. Enter the value you have to associate with the target entry from the list.

The few formulae entered in addiotion are only crude examples.
ask68588JumpOnSelection001.ods

[Kennaway]

What is the source of your drop-down list?

I’m new to Calc (switching over from Excel), but my first instinct would be that you’ll want a NamedRange (this should form the basis of your drop-down), and the second column is the amount you want to add by (store it as a negative number to subtract - just to keep things simple) - for simplicity, you may want to store this named range on another sheet in your Calc file, so it’s usually hidden.

Then in your Column C you’ll want to make use of a nested statement using the MATCH and OFFSET functions which matches A21 to the NamedRange, and offset by 1 to the right to select the number associated with the Test (column 2 of your NamedRange). Which would look something like

=B21+(OFFSET(G6,MATCH(A21,Tests, ),1))

in this example G6 is the header row (row above first data row) of the named range (Tests).

I think this should get you started - depending on if/what you already know about NamedRanges, and these functions. We can help you out further. This formula should be movable, you’ll just need to use “$” as appropriate to lock the cell references.

Let me know if this works!

[Lupp]

@Kennaway: I am still lacking a clear understanding. Would you mind to help me to understand things better by telling me in what ways my example missed the needs of the OQ?
Is it just the question of using a named expression for some additional purpose? Do you clearly understand the terms I did not?
Thanks!
(I may be handicapped by my poor English.)

[Kennaway]

We’ll need to hear back from the OP. I think I understood the request a little different than you did, when I looked at your example file. From what I could see, your formula adds -600 to everything.

Have a look at my example:
link text

Made up of a Database and a Program area. The database is the tests, and the program selects a test, manually enter a value, and result is displayed in the third field.