CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Demotry
Demotry

Reputation: 907

Looking for Google Sheet Array Formula

Hello I'm trying to create a Array Formula for the following B and C columns but getting error, but without Array Formula the formula works by dragging the formula to all the cells. So I need a Array Formula so I can restrict a formula for specific range, So when I add a new row anywhere in d middle the that row should be working without any formula.

https://docs.google.com/spreadsheets/d/1kc58OkoA3SysApgALJnecnIoK2uyPV6FlhyVRTUXXcI/edit?usp=sharing

So in the above link where there is a list of items in Sheet2 A: and I importing the list to Sheet1 A: with Array Formula.

Now in D column dropdown list whenever we select an item it keeps removing the used items from C column.

So how to make work while adding Array Formula for B and C Column.

Upvotes: 1

Views: 98

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15328

I suggest a complete chage of strategy ... in B1 Sheet2

=arrayformula(countif(Sheet1!D:D,A1:A))

then in C1 Sheet1 (nothing in B1 Sheet1)

=iferror(query(Sheet2!A:B,"select A where A is not null and B=0 "))

the red corner is a good indication that the item is reserved. https://docs.google.com/spreadsheets/d/1m_SACJtoN2k-c1p4C-eflh3pKLmv6YqP8BM2i0jDUhw/edit?usp=sharing

Upvotes: 2

Related Questions