Trouble Referencing Another Table

I’m trying to create my first reference. So far, it’s not going great.

The end goal is to create an app that will assist with onboarding students.

I have a Google Form that collects basic student info (contact info, birth date, desired schedule, etc.) and sends that info to a sheet. That sheet is connected to the app as a table. Let’s call it the Info table.

I have a second form that we will use to determine a student’s ability level, which we will use to place them in a class. This form sends data to a sheet, which is connected to a second table (Locator).

I want users to be able to pull up a student in the Info table and see their score from the Locator table. I created a new column in the Info sheet, and named it LocatorResults. I connected it to the Locator table, and for the ref formula, I added [LocatorResults].[Score], where “Score” is the name of the column that houses the score in the LocatorResults. It tells me “The expression is valid but its result type ‘Number’ is not one of the expected types: Ref” I’m not really sure what that means, or why I couldn’t display a number. When I open the app, it looks as if I didn’t even add a new reference column.

I know I have to specify which row I want it to check, but I can’t figure out how to do that. In Info, there is a column for the Google Workspace email, and there is also a matching column in the Locator sheet. I want to tell it something like “Show all values in the score column of rows where the value of the Google Workspace email in the Info table matches the value of the Google Workspace email in the Locator table.”

Any advice you can offer is much appreciated.

Thank you.

That’s not how you setup references between tables at all. Please review this article.

Sounds like this email column needs to be set as the key column in one table, then set as a Ref type column in the other table.

1 Like