On google sheets, I have a python program that updates 4 columns with 97 rows of data twice a day. In column 5, I have a formula used to track sales but I am forced to manually drag or click the formula to autofill in the new rows of data.
I have tried using the ArrayFormula() function but since I am using Vlookup to search the name of the location in each individual row it is not working since the cell reference is not being dragged down with the formula. Is there another alternative that could be made in order to automatically fill in the extra rows without having to open the google sheet?
Vlookup formula: =IF(B2<VLOOKUP(A2, A3:D194, 2, FALSE), 0, B2-VLOOKUP(A2, A3:D194, 2, FALSE))
Array Formula attempt: =ArrayFormula(IF(B2:B<VLOOKUP(A2:A, A3:D194, 2, FALSE), 0, B2:B-VLOOKUP(A2:A, A3:D194, 2, FALSE)))
The excel data would look like this and would repeat with the names in different locations
| Location Name | Quantity | Date | Time | Daily Sales |
|---|---|---|---|---|
| Springville | 38 | 27/07/2023 | 19:12 | 1 |
| Willow Creek | 37 | 27/07/2023 | 19:12 | 4 |
| Pineview | 33 | 27/07/2023 | 19:12 | 3 |
| Lakeside | 30 | 27/07/2023 | 19:12 | 1 |
| Clearwater | 29 | 27/07/2023 | 19:12 | 0 |
| Oakville | 29 | 27/07/2023 | 19:12 | 1 |
| Meadowbrook | 28 | 27/07/2023 | 19:12 | 1 |
| Cedar Ridge | 27 | 27/07/2023 | 19:12 | 0 |
| Maplewood | 27 | 27/07/2023 | 19:12 | 0 |
| Riverdale | 27 | 27/07/2023 | 19:12 | 1 |
| Forest Hills | 26 | 27/07/2023 | 19:12 | 0 |
| Sunnyside | 26 | 27/07/2023 | 19:12 | 0 |
| Greenwood | 26 | 27/07/2023 | 19:12 | 1 |
| Lakeview | 25 | 27/07/2023 | 19:12 | 0 |
| Sunset Heights | 25 | 27/07/2023 | 19:12 | 2 |
| Laurel Hill | 24 | 27/07/2023 | 19:12 | 1 |
| Briarwood | 23 | 27/07/2023 | 19:12 | 1 |
| Oakwood | 22 | 27/07/2023 | 19:12 | 1 |
| Cedar Heights | 21 | 27/07/2023 | 19:12 | 16 |
| Pinecrest | 21 | 27/07/2023 | 19:12 | 0 |
| Mountain View | 21 | 27/07/2023 | 19:12 | 2 |
| Willowbrook | 19 | 27/07/2023 | 19:12 | 0 |
| Maple Valley | 19 | 27/07/2023 | 19:12 | 0 |
| Riverside | 19 | 27/07/2023 | 19:12 | 1 |
| Lakefront | 19 | 27/07/2023 | 19:12 | 1 |
When working with array formulas, providing a range to an argument that by default expects a range will not cause it to shift to the next row. Here's what you can try instead:
=MAP(A2:A,B2:B,LAMBDA(α,b,IF(α="",,LET(vl,VLOOKUP(α,OFFSET(α,1,,9^9,2),2,0),IF(b<vl,0,b-vl)))))