Can a function write data into a function selected cell

[Jotra]

What I hope to achieve is a function that will write a (calculated) static value into a cell selected by the function. e.g.

A2=5

B2=J

The function would write a value into cell J5, the value being the sum of cells in another sheet.

The values in both A2 and B2 are the results of table lookups from another sheet.
e.g. B2 contains the function
=IF(Sheet1.D2=“A”,LOOKUP(Sheet1.C2,Sheet2.$C$4:$C$22,Sheet2.$G$4:$G$22),LOOKUP(Sheet1.C2,Sheet2.$C$4:$C$22,Sheet2.$H$4:$H$22))
In this instance, Sheet2.G4 = J and Sheet2.H4 = K

so the target cell will be J5 or K5 depending on the content of Sheet1.D2

Is this even possible ?

The fundamental reason for trying to do this is to avoid having thousands of =IF() statements (about 20 per row, in approaching a thousand rows).

[mikekaganski]

If you mean spreadsheet functions, (user-defined included) - but not general macros - then the answer is no.

Actually, the user-defined spreadsheet function created as macro is able to do that, i.e. if you put =MyFunc("B2") into A1, the MyFunc can alter the B2. But that’s undesirable side-effect.

Here’s an excerpt from my IRC conversation with moggi (one of lead Calc developers):

* mkaganski: moggi: is it OK that a user function in Calc is able to modify the sheet's content as side effect?
* moggi: mkaganski: normally not, ideally sheet functions are pure functions
* moggi: mkaganski: of course that does not prevent a user to write a function in basic that does not follow that rule
* mkaganski: moggi: so there's no read-only blocking when a user function is executed?
* moggi: mkaganski: no
* moggi: mkaganski: the whole calc core is blocking free, but you can most likely mess up the calculation engine if you change the wrong cell
* moggi: mkaganski: e.g. running into infinite loops, ...
* mkaganski: moggi: I understand. My question is because in such a function, "cSheet.getCellByPosition(5, 5).clearContents(511)" is a noop, while "cSheet.getCellByPosition(5, 5).Value = 5" works...
* mkaganski: moggi: and I hoped that allowing setting value is a bug
* moggi: mkaganski: as I mentioned you can most likely confuse calc a lot if you modify another cell while we are in the calculation of another cell

So, while practically you can create such functions, they are unsupported, not guaranteed to work in a future version, and generally a bad idea.

[PYS]

Hi @mikekaganski

MyFunc can alter the B2. But that’s undesirable side-effect.

Except when using AsyncCallback but it is so “special” that I completely agree with your answer

Regards

[Lupp]

You both surely know that side-effects altering the content of cells in the currently recalculated sheet were blocked by “legacy” code and still are in AOO 4.1.2. There should have been a conscious decision to change that.
(I also wonder if the continuing blocking of cell.Formula = "" for a remote cell in the same sheet is purposeful. I sometimes tried to use it to make a cell ‘blank’, and it works this way for cells in another sheet.)

[mikekaganski]

@Lupp: AFAIK it was something performance-related, but I cannot find specific reference ATM. It’s easy to block one function (without a performance penalty), but it’s costly if you put locks to prevent edits in general, that could be the distinction with the cell.Formula = .

[Lupp]

@mikekaganski: I cannot honestly pretend to be convinced by your point.
BTW1: What would you do to make a cell (same sheet) blank actig from the body of a function?
BTW2: Is somebody, as far as you know, currently working on some Chart fundamentals?

[mikekaganski]

@Lupp: I have no points: I only speculate, and may be wrong. However, my speculations are based on words of Calc expert. That’s why I posted the conversation itself, not only my words. The point of moggi’s words imo is that spreadsheet functions should not do what you say in BTW1.
ATM, no one is working with chart fundamentals. Tomaž Vajngerl extends charts to pivot tables, but that’s different. Some problems with text layout in charts are solved by Khaled Hosny.

[Lupp]

@mikekaganski: Tanks!
Except for 1968 I was never too much interested in Charts, but they clearly are of significant value now and then. There are two features I would like to point a developer to without filing an enhancement request:

1. Charts should be able to reconnect to spreadsheet data ranges if they were once transformed to contain their data themselves in DataTables.
2. The overall DataRange(s) property should accept range expressions.
   There are limitations, of course.