Reference data not showing up

So I am making an Appsheet from an Excel spreadsheet on Dropbox.
One of my excel spreadsheets has a dropdown and when you pick a value (in my case, a project number), the corresponding description of the project is populated next to it.
But in Appsheet, it says “n/a” in the column next to the project number.
Also the drop down list is not working in appsheet.
Where do I start?

U can refegenerafe of coloum

1 Like

I read this article on “Data Validation Rules”.
It suggests that the app must be synced again. This doesn’t solve it for me, although I did learn how to get the dropdown values working, but still when I select an item from the dropdown list it should find the matching value and populate the corresponding field as it does in Excel, but in Appsheet it still says “N/A#”
Please be simple because I am not very advanced on this topic

What does “refegenerafe” mean?

Steve, thanks for taking the time to reply and send me useful pointers.
I don’t think it is a problem with re-arranging columns or adding new columns, etc.
There is a missing piece of the puzzle or a disconnect between the chosen value and the corresponding entry in another table. Please advise how to make this connection.

If you change the dropdown data validation options in the spreadsheet, you must regenerate the columns of the table in the app to make AppSheet aware of the data validation changes in the spreadsheet.

Okay I did a regenerate and it came up with a bunch of issues…

Column ‘PROJECT’ of sheet ‘TIMESHEET’ contains mismatched formulas. Compare the formulas displayed in R1C1 format below to see the differences. 974 rows have MultiColumn formulas. 2 distinct formulas are present of which the first 2 are displayed. 973 rows contain formulas like ‘IF(E2="","",VLOOKUP(E2,PROJECTS!$A$2:$B$1003,2,FALSE))’ in A1 format and ‘IF(RC[1]="","",VLOOKUP(RC[1],PROJECTS!R2C1:R1003C2,2,FALSE))’ in R1C1 format including rows 2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26. 1 rows contain formulas like ‘IF(E975="";"";VLOOKUP(E975;projects!$A$1:$B$1002;2;FALSE))’ in A1 format and ‘IF(RC[1]="";"";VLOOKUP(E975;projects!R1C1:$B$1002;2;FALSE))’ in R1C1 format including rows 975.

Unable to find values for validation in ‘TIMESHEET’

More info

Table ‘TIMESHEET’ has an implicit (RowNumber) key – if multiple users insert or delete entries concurrently, data loss can occur. Updates may apply to the wrong record or could fail entirely on some data sources.

Steve can you help me online? :wink:

So I’m guessing here, in Excel I have “vlookup” but this doesn’t work in Appsheet & I need to replace it with “lookup” function?

Ouch! :frowning:

The spreadsheet formulas look essentially the same to me, except one set is in A1 format, one is in R1C1 format. If in fact the formals are supposed to be the same, I would suggest the following:

  1. In the spreadsheet, find a cell with the formula that uses your preferred cell addressing method, either A1 or R1C1.

  2. Copy the formula from that cell into every other cell in that column. Confirm that the formula is correct for each column (i.e., that the cell references are all adjusted correctly).

  3. In the app editor, in the configuration for the app column, remove the Spreadsheet formula value entirely.

  4. Regenerate the columns of that table to prompt AppSheet to rediscover the (hopefully) now-consistent spreadsheet formula.

Nope. :frowning:

No, not necessarily. In general, it’s preferable to do all calculations within the app itself, rather than using spreadsheet formulas, but “preferable” does not mean “required”.

1 Like

Thanks Steve, I will persist…tomorrow. Appreciate the help so far

1 Like