Question for excel wizards

Ask for Help
1

Hi, Loving this app creator. I have a question for those with formula experience.

Heres what im looking to achieve:

I need to take a site code from user entry, lookup the code from a table in another sheet, take the related email address and add this to the email address from the user entry.

This is so my team managers can view all entries related to their sites.

Here is picture of my sheets to help explain.

Sheet1
Sheet11109×804 49.7 KB

Any help greatly appreciated. Thank you

2

I think @ThinhDinh can help younwith that he is awesome at formulas

1 Like
3

Hi @Chris_Aram

Option A: In the Glide Data Editor

  • Go to your “User entry” tab (in the Glide Data Editor).
  • Create a relation column which you can call “Site codes relation”
  • In “from” select “Side code”. In “with/to” select “Site codes” tab and “Sites codes” column.
  • Leave multiple match unchecked.
  • Create a second column which will be a lookup column: lookup the “Managers Email Address” column from the “Site codes relation” column.

If anything here is unclear, have you had at look at Glide’s documentation and videos? They are very well done.

Relation column
Lookup column

Option B: Formulas in Google Sheets
You can use a vlookup() formula or if you want to be a little bit more fancy an index(match()) combination. For your app, I recommend you stick to the Glide Data Editor as this is good practice. Your app aside, it won’t hurt if you play around with vlookup and index-match, those two formulas are useful.

The relation+lookup combo in the GDE allows you to do an index+match in GS. Whenever possible, you should process data in the GDE rather than in GS to reduce lag.

1 Like
4

Hi Chris, I would advise you to follow @nathanaelb’s method A to do it inside the Glide Data Editor, as we don’t want the lag when the data has to be synced back and forth between Glide & Sheets.

If you still want to store that value inside your sheets, then the right arrayformula to put in the first cell of email column (row 1) would be:

={"Email address";ARRAYFORMULA(IF(D2:D<>"",VLOOKUP(D2:D,'Site Codes'!A2:B,2,FALSE),""))}
5

Thanks, ive tried this but i cant get the array to then work to get both email address into one box to then make the row owner??

6

If you need 2 emails to become row owner you must make it an Array column. Name them Email 1, Email 2, …

7

But you cant make a lookup column part of an array with glide, that i can work out any way. So i have a list of site codes with their respective managers, i can get the relation formula to work so when an entry is made the relation looks up the relevant site and brings in the associated email address. Now i cant get this email address into the array because the column cant be renamed Email 2 for example.
Does that make sense?

Thank you

8

I feel like im trying to get a moon on a stick but im sure it cant be that hard. All i want is that once a user has logged an issue, the site code is used to lookup the relevant site manager and then that site manager email address is added to the owner email cell so he can see the issue on his app.
Maybe im trying to go about it the wrong way but thats what im trying to achieve.

Thanks

9

If the lookup column in Glide doesn’t work as you say then make it in Sheets and use the Arrayformula I gave you above. Make sure you position them next to each other so it can become an Array column.

1 Like
10

thanks, the problem ive found with setting this up in sheets is that when the app enters a new line of data the lookup formula doesn’t copy to the next line?

11

What does it return there? The ARRAYFORMULA will be automatically calculated for all lines.

12

Just curious why the need for row owner. Is the data sensitive enough that you need the security of row owner? Or would it be easier to just use @nathanaelb’s first method and simply filter each email column against ‘signed in user’ individually instead of as an array. I’m only asking because it seems like we are taking a complicated route to filter data using the more secure ‘row owner’ when fingering would be enough. Unless you are trying to ensure nobody else will ever be able to see the data other than the user and the manager.

1 Like