sum partial list equal to value and return last row

AppSheet AppSheet Q&A
1

I have a table where I enter the following data together with many others, specifically I want to focus on two data that are collected. The atabal is called Diario.

Example:

XXXXXXXX Nº Vale Kilos Calidad XXXXXXX
XXXXXXX 34 1232 XXXXXXX XXXXXXX
XXXXXXX 56 5670 XXXXXXX XXXXXXX
XXXXXXX 66 8900 XXXXXXX XXXXXXX
XXXXXXX 67 9850 XXXXXXX XXXXXXX
XXXXXXX 86 3485 XXXXXXX XXXXXXX
XXXXXXX 87 85737 XXXXXXX XXXXXXX
XXXXXXX 134 756 XXXXXXX XXXXXXX

Now from another table, I open a form and it wants the same, when entering the number of kilos (sum), to tell me which is the last Nº Vale that this sum is complete.

Examples:

I add imput: 6902 the next cell with formula return; 56 (Second row)

I imput: 15802, the next cell return: 66 (third row: value of Nº Vale)

I imput 114874, the next cell return: 87 (sixth row: value of Nº Vale)

Notes:

  1. The key in that table is _Rownumber because the rest of the data is repeated and cannot be used as a key.

  2. The sum always starts from a number that is entered previously. This is just a fragment of a much larger formula. But I’m stuck at this particular point.

  3. The NºVale, are always numbers that grow (it is a numerical record that rises in each row although it is not progressive)

  4. The value that I enter manually is the exact amount of the sum of “kilos”.

  5. This is just a formula extract

  6. I have the list or column that I must add, but I need the formula to return the value “NºVale” or at least the _Rowmnumber to be able to identify it and thus extract the data with the Lookup formula.

I have tried with the following formula:

MAX(SELECT(Diario[Nº Vale],
            (SUM(SELECT(Diario[Kilos],
                             ([Calidad] = [_THISROW].[Calidad])
                        )
                 )
             )=([_THISROW].[Kilos])
             
         )
    )

I thought that using the MAX of the value and conditioning the sum to be equal to the box, it would return the value, but it is not.

I repeat. The formula is much larger and with the conditionals when adding the value, but I have tried to simplify the idea as much as I could to see if someone comes up with it.

Thank you very much for your time.

2

You can just add a cumulative sum column and use it for lookup, better than further complicating your expression.

1 Like