Guys, I am using VLOOKUP function like this:
=VLOOKUP($B2,Produtos!$B$1:$Q,16,FALSE)
When I click on REGENERATE STRUCTURE button, the Spreadsheet formula is:
VLOOKUP(RC2,Produtos!R1C2:RC17,16,FALSE)
But, when I insert a new register on Google Sheet Table, the formula of the new regisrter column has been changed to:
=VLOOKUP($B3,Produtos!$B$1:$Q3,16,FALSE)
So, the formula is putting the number of line ($Q3) instead of ($Q) only, and the data is not founded.
Someone could help me?
Hi! Welcome to the community!
Iโm not sure but perhaps AppSheet doesnโt handle the $Q well. An expression like $Q (where the final row number is left off) says โto the last row.โ If AppSheet doesnโt understand the โto the last rowโ intent, perhaps the issue might be resolved on the Google sheet side. Hereโs an idea:
On another sheet (one that isnโt a โTableโ in your app) put a formula like the following in a cell:
=countif(Sheet1!A1:A,"="&"")+countif(Sheet1!A1:A,"<>"&"")
This will give you the number of rows in โSheet1โ. If new rows are added, the number will change appropriately. In my practice sheet, this formula is in A1 of โSheet2โ.
Use โindirectโ to identify the range in your vlookup expression.
Hereโs my test formula:
=vlookup(3,indirect(concatenate(โK2:Lโ,Sheet2!A$1)),2,false)
In my sheet this produced the same result that =vlookup(3,K2:L1000,2,false) would have produced. But, if new rows get added to the table โL1000โ will become โL1001โ or whatever.
I havenโt tried this will AppSheet but Iโm guessing thereโs a good chance it will work.
I wonder if anyone knows a better, more efficient way of identifying the last row in a page.
I think there may be a simpler way, after all. Instead of using the complicated
=countif(Sheet1!A1:A,"="&"")+countif(Sheet1!A1:A,"<>"&"")
expression to find the exact number of the last row, just type in a number like 10000 (or maybe 50000) โ a number that is a lot bigger than the current number of rows in the sheet and one that you canโt imagine your sheet will ever outgrow. That should work. I just tried it and it did.
Actually, now I see that indirect wasnโt even necessary:
=vlookup(3,$K$2:$L$10000,2,false)
This seems to be perfectly adequate.
I facing a new problem. When I import a new row I want my formula to change automatically like this:
Formula Cell on Row 1: =vlookup(K2,$K$2:$L$10000,2,false)
Formula Cell on Row 2: =vlookup(K3,$K$2:$L$10000,2,false)
Formula Cell on Row 3: =vlookup(K4,$K$2:$L$10000,2,false)
โฆ
Formula Cell on Row n: =vlookup(Kn,$K$2:$L$10000,2,false)
How do I validate field K?
User | Count |
---|---|
40 | |
28 | |
22 | |
20 | |
15 |