Spreadsheet So Basically i'm building a sheet to monitor my NW, my problem is simple, i need the 2023 column to adapt to whatever last number on that row there is, for income i just sum values, but for example the total amount in my bank account is different every month, i just need 2023 column to track that, i update the 31 of each month. With the formula you can see in the screenshot i have some problem, if i input the number alright, if the value is defined by other formulas it won't show up. Any solutions? THANKS

I tried =INDEX(X:Y;1;COUNTA(X:Y)) but it won't update if the last number in the row is generated by other formulas.

CodePudding user response：

You can use this formula to find the last value of each row in the range D2:O26. Adapt it as needed!

=BYROW(D2:O26,LAMBDA(e,XLOOKUP(1,INDEX(1/(e<>"")),e,,0,-1)))

CodePudding user response：

You haven't show what formula in columns Jan-Dec causes you problems. If formula produce values 0 in case you don't what them to count, you can use countif to filter them out:

=INDEX(D13:O13,1,COUNTIF(D13:O13,">0"))

Same as your solution, this only works if columns are filled sequentially - if there are no gaps in each row, e.g. Jan and Mar are filled while Feb is blank. If you need a more general solution, you may go with series of nested if(isblank(). Here is an example for the first three months, to get an idea:

=INDEX(D13:O13,1,IF(ISBLANK(F13),if(ISBLANK(E13),1,2),3))