I am trying to write a function for finding the name of the last column with values other than zero.
In this case I should get O as the column name.
Do you mean
=SUBSTITUTE(ADDRESS(1;SUMPRODUCT(MAX(COLUMN(<range>)*(VALUE(<range>)<>0)));4);"1";"")
By the way, you can specify a range simply as a range of strings. For example, for your screenshot, it could be
=SUBSTITUTE(ADDRESS(1;SUMPRODUCT(MAX(COLUMN(2:8)*(VALUE(2:8)<>0)));4);"1";"")
(Not everyone knows about this opportunity)
And use references to whole columns/rows, specially columns, with SUMPRODUCT() it is not a good idea, because the function doesn’t shortcut the operation to the range with data like other functions, resulting in a slow calculation.
@JohnSun with the first solution I get the correct result O as the column name.
I have used these to specify the range.
=SUBSTITUTE(ADDRESS(1,SUMPRODUCT(MAX(COLUMN(D2:BE8)*(VALUE(D2:BE8)<>0))),4),“1”,"")
Now I need to put the O result into the $table.$O2
But when I use
$table.$(=SUBSTITUTE(ADDRESS(1,SUMPRODUCT(MAX(COLUMN(D2:BE8)*(VALUE(D2:BE8)<>0))),4),“1”,""))2
I get error 509.
About Error - Which version of the office suite are you using?
About new question - no need to ask, you cannot “put” the result in cell O2 in any other way, only just by writing in the cell a formula that will calculate something and leave the result in that cell. This is one of the basic rules of spreadsheets - each cell is only responsible for its value and cannot directly record the result elsewhere.
“table” - is it name of curren sheet? The sheet shown in the screenshot? O2 is that cell in which 5 is now written? Even if you put the formula in O2 cell you will get Error 522.
@JohnSun, I use LibreOffice Version: 6.4.3.2. “table” is the name of another spreadsheet. I have to digest your answer, I am not yet a proficient user of LibreOffice.
@Math3333 Can you explain in simple words what your spreadsheet should calculate? Do you want to generate some report on the data from the neighboring sheet on the “table” sheet? And while the amount of data can be different?
@JohnSUN, All I have to do is to copy from one spreadsheet the values from the last column with values other than zero to another spreadsheet’s column with the same dates. With matching dates I have no problem. You showed how to find the last column in the source spreadsheet. I can’t figure out how to update the changing last column name. Next it will be P.
To copy data I use this
=IF(AND($Target.$A3,$Source.$A2),$Source.$O2).
A columns are dates. I need to make $Source.$O2 to automatically update to $Source.$P2, when the last column with data becomes P.
Sorry, I read your description several times and understood far from everything. There were no dates in the screenshot, are the numbers in the first line supposed to indicate the dates? Does the sheet “table” have the same dates as the sheet “target”? Or do these dates need to be calculated and changed automatically as the “target” sheet is filled?
Our discussion has already gone far beyond the scope of the original question; let’s not clog this topic with extraneous comments. Please mark this topic as resolved (V icon on left of the answer) and we will bring the discussion to this forum. The format of the forum is more suitable for discussions and clarifications. It will be good if you immediately attach a sample file to your question on the forum.