If you definitely need a refresh “onSelectionChanged”, you will need to use the respective sheet event.
However, I don’t understand for what this should be necessary. If you sit in front of the screen, you anyway see where the focus is. If a formula should depend on the current focus, simply use the function in the proper place inside the formula… If you want to get higher efficiency, you can refer to a cell containing the formu8la simply calling the function, and the referenced value will be recalculated automatically when used…
This of mine is a sort of experiment.
I’m trying to obtain an “intelligent” behaviour from data-validation drop-down lists.
I aim to get the elements of the list from a cell range. The content of the latter should depend on the coordinates of the current row or column.
A drop-down list whose content depends on the context is a pretty common task with a PHP page and a DBMS: I’m trying to obtain a similar behaviour with a spreadsheet.
If your dropdown is context-sensitive in the mentioned way, it will use a calculated reference to a cell range. The calculation simply needs to call the function In its formula, and no extra refresh will be needed.
The problem with a validity range in Calc is then that you need to have selected the cell for which the validity is defined. If this is the cell you want to base the range on, there should be simpler means. If that is a different cell the concept may get very unhandy. You would need to select an additional cell telling the validity formula what range to use. With this respect a spreadsheet will behave very differently as compared to a DBMS form or whatever.
If you describe more precisely what you actually want to achieve, a simple solution might be found.
Context dependent validity CellRange isn’t a problem. Problems come from “How to pass the parameters”.
I didn’t dare to ask for more help, but since you are kindly offering… 
My whole problem is synthesised in this file:
link text
Again many thanks for your time. 
Sorry. I simply don’t understand.
Your sheets obviously try to estrablish a surrogate for a database where you have two data tables and one sheet with “relations”. Semantically any of these relations shall tell something like
name HAS oneoftheitemsdescribedbycode WITH serialNumber
and you want to get the available serial numbers selectable from a “dropdown”.
However, many itemcodes haven’t assigned any serialnumnber, while others have assigned a list.
Calc Validity can’t use a textual list of this kind directly.
Recent versions would accept a formula splitting such a textual list into an arraya of entries, but Calc still hasn’t a standard function for the purpose.
Anyway: If you do the splitting by a user function (I might supply), the Validity dropdown would always also offer entries for tools already entrusted to someone else.
Having converted your sheet “Stock” to a sufficiently normalized form, you could mark enrtrusted tools there by formulas, and based on that exclude them from the dropdown.
I’m afraid your attempt is too ambiguous (complicated), and a solution by an actual DB would be preferrable. After all, the task is all but unique to your shop.
BTW. Basical normalization is always a good idea.
(I can no longer see in what way the results you want to achieve are lelated to the subject of this thread.)
My idea was to create a one-column cell range (let’s call it serialsRange) to be populated, by formulas, with all the serial numbers (if any) of a certain item in Stock.
The selection of the item whose serial numbers should be exploded in such a list would depend on the Item I’m editing in the Equipment sheet.
This is the sequence I’ve imagined:
-
I start adding, or editing, a row in the sheet Equipment;
-
if I could rely on a variable containing $Equipment.D{row-number}, where {row-number} is the line coordinate mentioned in the original post, I would use it to refresh the content of serialsRange, so that, when I click on the drop-down button, I may see only the serials related to the item I’m working on.
I’m not sure to have explained it in a comprehensible way. 
The way you tried it, you need to convert the comma-separated lists into sequences (in the Sense of the software). Since there isn’t a standard function for the purpose, you need to either:
a) Write such a function [or use my old XTEXTSPLIT()]
b) Use complex formulas to workaround the lack of such a function based on REGEX() [V6.2 or higher] probably.
c) Reorganize your Stock sheet to get one row per serial number. (What I called “normalize” earlier.)
Only the option c will open a usable way to also exclude a serial number from the validation dropdown if it already was assigned to a different trustee.
Think it through this way: You haven’t tool-classes on stock, but single tools, one by one. An adequate designator for such an item should be a class-identifier combined with one serial-number (if needed.)
That’s what “normilzed representation” means in the context.
Of course, you are right about normalization, but I realize now that I haven’t yet explained my point.
Here is an updated version of my previous file
Try this
-
click on cell F4 dropdown list, and you will see the very set of serial numbers related to the item in row number 4
-
now click on cell F5 dropdown list, and you will keep seeing the previous set, still related to the item in row number 4
-
to see the actual set of serial numbers for the item in row 5, you have to change manually the address written in I2.
Got the point? When you replace the address in I2 the list below gets refreshed. You can try the value in I2 with F6 and see the effect.
I’m trying to have the list refreshed not from the address written in I2, but automatically from the address of the cell I’m working on.
Again thanks for your assistance
Sorry. I won’t create a google account to get your new example.
If you trust in Google, why don’t you use “google sheets”? That should be great though I can’t be sure.
(I distrust Google and their strategies very much though some of my former students are working for them.)
See the annex to my answer.
And, please, accept the attached .ods as my final contribution to this thread.
I still don’t think it’s of much use to to make the results in a specific range depend on the cell currently having the focus (selection inapt; can be multi-cell). Insofar my suggestions are OT regarding the wording of your question.
Use helpers per row preparing the dropdown-range for every row.
(A professional solution would use a DB anyway.)
You’ve already provided a lot more support than I expected for my little issue.
Again thanks for the time and take care.
Call ActiveCell() function with first param 1 or 2 or 3 from event handler like as
Sub onActiveCellChanged(oEvent As Variant)
Const RESULT_CELL = "A1"
oEvent.getSpreadsheet().getCellRangeByName(RESULT_CELL).setString(ActiveCell(3))
End Sub
Assign this as a handler for the “Selection Changed” sheet event
@JohnSun: Any serious reason to use ~25loc for that?
def selection_changes(event ):
target = event.Spreadsheet.getCellRangeByName("A1")
target.String = event.AbsoluteName.split(':')[0] #to hide Range-selections@karolus Please try select any ranges with Ctrl instead single cell or single range. Or get address of active cell on prev (or next) sheet. BTW, for a range active cell is not top-left - drag selection from bottom to top or from right to left and the result will be wrong
Yes I see the Problem, but why should we play with mullti-selections in this context?
dirty:
def selectionchanges(event):
try:
target = event.Spreadsheet.getCellRangeByName("A1")
target.String = event.AbsoluteName.split(':')[0]
except AttributeError:
target = event[0].Spreadsheet.getCellRangeByName("A1")
target.String = event.AbsoluteName.split(';')[-1]Since onSelectionChanged may be needed for a different purpose, I propose a probably satisfying solution based on a function called by a volatile formula.
It cannot act onSelectionChanged automatically, of course, but as with other volatile formulas, recalculation will take place if any “minimal editing action” like Del applied to a blank cell was taken, and AutoCalculate was on (or F9 was used).
See: ask310463CurrentFocusCellAddress.ods
[Edit 2021-05-31 about 20:10 GMT]
As announced in my last comment on the question I attach another .ods showing in what way I would try a solution:
ask310463EquipmentEntrustedtoEmployeesNextBack.ods
Anybody using a version below LibO 6.2 can’t see the example working: REGEX() function missing.
[/Edit]
Many thanks for answering. I’m testing your suggestion now.
Ok. It works as designed to. It’s an encouraging step forward for me.
Now I will try to obtain an automatic refresh.
Thanks for your help.