Issue ARRAYFORMULA & new item

Ask for Help
1

Hello everyone,

I’m facing some issues with formulas and new items.

We created an app to manage our stock. So basically we have a row per item, and users can add items throught the app, which adds a row to our sheet.
Two formulas are associated with the item. So we used the ARRAYFORMULA to show to result in the cell if the item’s reference is not empty. We scrolled down this formula until row #500, and we currently have 375 items.

The problem here, is that when we had an item through the app, it indeed add a row in the sheet, but in row 501, instead of 376, so our 2 formulas/cells stay empty. Glide consider that the rows betwen 375 and 500 are not empty because there’s a formula even though the cells are empty.

Does someone know how to add the new items in the empty rows where we have our fomulas ?

This would help a lot, thanks ! :smile:
Lucas

2

Sounds like you are using ARRAYFORMULA incorrectly. When used correctly, you just need to put that in the first row, and it will autofill as new rows are added.

Depending on what your arrayformula is doing, you might find that you’ll be better off using a Glide computed column.

3

Here are the 2 formulas :

=SIERREUR(RECHERCHEV(B2;IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1OWzuJ-FsYu6A1WDJjJI3rgcPsqid_2otVIPTXtuGBuA/edit#gid=0";“green!$A$2:$I$3500”);9;FAUX);"white”)

=ArrayFormula(SI(ESTVIDE(B2);“”;NBVAL(I2:AL2)))

When you say “in the first row”, you refer to the header, or the first row with data (second one) ?

I tried using the glide tab for that but I don’t really know how to use it for now…

4

It could be either. If you put it in the first row, then you can include the column header by using the form:
={"My Column Header";ARRAYFORMULA(......)}

Written correctly as an arrayformula, that would be something like:
=ArrayFormula(SI(ESTVIDE(B2:B);"";NBVAL({I2:I,AL2:AL})))

5

You were right, my ARRAYFORMULA was wrong ! :wink: thanks!

Now it works, but the result in each row is the same since it adds all the values included frome I2 to AL, instead of I2 to AL2 for row 2, and I30 to AL30 for row 30, do you know if we could fix it ?

6

Add values? I thought NBVAL was the French version of COUNTIF?

So in each row:

  • If column B is not empty, you want the sum of all values in columns I through AL

Is that correct?

7

Is here a screen :

  • Each row is an item
  • An item can be physically in multiple boxes “niche”, column I to AL
  • We want to count the number of full boxes in column H in which an item can be

Sorry if I don’t use the right terms, it’s nondigital values like A01, E34…

SO for example, in row 2 we should have in colomn H “1”, since the item is only in one boxe :point_down:

Screenshot 2021-05-04 12.38.26 PM
Screenshot 2021-05-04 12.38.26 PM1920×1080 331 KB

8

Try this: (untested)

=ARRAYFORMULA(IF(B2:B="","",(COUNTIF(IF(I2:AL<>"",ROW(I2:I)),ROW(I2:I)))))
9

It didn’t work, but it’s okay we will use the total number of filled boxes instead of filled boxes per item.

You’ve helped me with the most important issue (formula 1), by helping me with the arrayforula, this one was a blocking issue.

Thanks a lot for your help Darren, appreciatite a lot ! :smile:

Lucas