Return the value of Cell A2 from table "Sheet"?

[sorin_mihai]

Hy,

I’m sorry, I can’t find an expression to use in a formula, to do this very simple thing:

Return the value of Cell A2 from table “Sheet”?

So it’s not LOOKUP, it’s just getting the value of a cell, knowing the table name, the column letter and the row number.
Do you know it?

Thank you

[Aleksi]

In generally it would be like LOOKUP(2,“Sheet”,"_ROWNUMBER",“ColumnName”)

[sorin_mihai]

Hmmm … there is something I’m not doing right.

So to explain, what I want is to write a formula for a notification workflow, for the email address to send the notification to. It sends it when a task is set to Urgent. But it has to send the notification to all the Team members that are responsible for the task.

Basically Send a Notification on a Priority Change of a Task, to all Team members Resopnsible for that Task.

So what I was thinking (and if there is an easier way please let me know ), was to have a formula something like this:

IF(CONTAINS([Team],LOOKUP(2,[Members],"_2",“Name”)),LOOKUP(2,[Members],"_2",“Email”),"")

So
IF in the List of Names ([Team]) in the Task that has just changed it’s Priority to Urgent, there is also the first name (Row 2, Column “Name”) from the Members table ([Members]):

LOOKUP(2,[Members],"_2",“Name”)

, then, return his email:

LOOKUP(2,[Members],"_2",“Email”)

, if not than leave it blank.

If it works it’s fine for me for now because there aren’t many Members, so I can make such a rule for each member (same formula where I just change the Row Number to search for the next name in the column Name from the Members Table and so on) . However, if there were 100 Members, this method wouldn’t be so convenient anymore because … there would be 100 formulas. So I’m guessing there is another way to Send a Notification on a Priority Change of a Task, to all Team members Resopnsible for that Task.

So far, writing the formula as you suggest, doesn’t work, as it says:

Unable to find column _2

[Aleksi]

It doesn’t work because that formula needs “_ROWNUMBER”, not “_2” which is possible a column name.

Would you please open your table structure a little. Where these columns are and is there a relationship between these tables.

[sorin_mihai]

I’m not sure I understand. What should go in “_ROWNUMBER”? I put "2 because I want it to look in the second row.

---

So the Team Column is a EnumList Reference to the Names in Members Table. That’s the link to populate the List of Team Members for a Task.

And the point is to make a Notification send to the Team Members of a Task that has just had a Priority change to Urgent.

So the question is what formula retrieves the Emails of the Team Members found within a list, associated to a task … one by one. (or if the “To” window of the Action from a Workflow accepts it, get all the emails, separated by a comma or something).

[MultiTech]

sorin_mihai:

So the question is what formula retrieves the Emails of the Team Members found within a list, associated to a task … one by one. (or if the “To” window of the Action from a Workflow accepts it, get all the emails, separated by a comma or something).

Put something like this in the “To” section of the workflow:

Select(team_members[email], in([Team_Member_ID], [members]))

The idea here is that you’ve got an enumlist that lists the team members for the task (holding the Key ref value for each team member in a list); so in your workflow you can add a formula like what I’ve got above into the “To” section that looks at that list, then pulls all the emails of the people in that list.

[sorin_mihai]

I’m sorry, I can’t really follow the formula.

So there is:

The table Members that has the columns Member_ID, Member Name, Member Email

And the Table Tasks that has the column Team that is an Enumlist referencing Member Name

Select(team_members[email], in([Team_Member_ID], [members]))

what is “team_members[email]” referencing?
[Team_Member_ID] would be Member name I guess, from Members Table … but I’m not sure if that’s what you mean
[Members] would be the Member Table.

And this formula would retrieve all emails? (never really used Select before so I don’t know it’s logics…)

Does the “To” field accept more than one email inside it?

[MultiTech]

sorin_mihai:

Enumlist referencing Member Name

What is the enumlist base-type?

sorin_mihai:

So the Team Column is a EnumList Reference to the Names in Members Table . That’s the link to populate the List of Team Members for a Task.

Base on what I see here:

The values stored in that column are the actual Names of the people.

---

Try the following formula:

SELECT(Makeri[Email], IN([Prenume], [_thisrow].[Echipa]))

[sorin_mihai]

MultiTech_Visions:

What is the enumlist base-type?

It’s a reference base type, to the Member Table where the key is the Name column

[MultiTech]

But it looks like you’ve got an actual ID column marked as the key:

[sorin_mihai]

MultiTech_Visions:

But it looks like you’ve got an actual ID column marked as the key:

Yes. Is that an issue in any way?

MultiTech_Visions:

The values stored in that column are the actual Names of the people.

Well, there is a column for names and one for nicknames. I reference the Nickname column which is the one I indicated (you will find Sorin there too, 'couse I use my name as a nickname …) but anyway, that’s not so important…as long as I understand the formula, I’ll get the right column name.

MultiTech_Visions:

SELECT(Makeri[Email], IN([Prenume], [_thisrow].[Echipa]))

Ok, I’ll play around with this until I get what I want. If it works, it’s already much simpler than what I was about to do.

But just so I know generally, how do you write the formula to get the value in cell A2 of table “Sheet”?

Aleksi:

In generally it would be like LOOKUP(2,“Sheet”,"_ROWNUMBER",“ColumnName”)

what is “_Rownumber” here?

Thank you

[MultiTech]

sorin_mihai:

In generally it would be like LOOKUP(2,“Sheet”,"_ROWNUMBER",“ColumnName”)

what is “_Rownumber” here?

Whenever I’m writing out a LOOKUP formula, I read it like this in my head (it might help guide you too):

LOOKUP(What to find, what table to look in, where to look, what I want)
LOOKUP(2, Table, _RowNumber, Column)

_RowNumber is the column where the value being looked up will be found.
With LOOKUP we’re saying:

• here’s a value
• look in this table
• you’ll find that value in this column
• and give me whatever is in the column I specify

[sorin_mihai]

MultiTech_Visions:

LOOKUP(What to find, what table to look in, where to look, what I want)

But then why is “2” in the “What to find” position? I’m not looking for the value “2”. I’m looking to get whatever Value is in the Google Spreadsheets Cell A2 (or B3 or C6 or whatever) in Table called “Sheet” …

MultiTech_Visions:

you’ll find that value in this column

and why is “_ROWNUMBER” in the “you’ll find that value in this column” field …

Maybe there is a misunderstanding due to talking in writing :)) but I don’t get it.

[sorin_mihai]

Oooh … I think I got it now. It’s a mindbender

the formula is saying - Look for (2, in table sheet, where 2 is the Rownumber, now give me the value in column - column name)
Right?

[MultiTech]

Nailed it.

Your original question was “How can I get something from row 2, in column a” - but we don’t do column letters in AppSheet, we use names.

So another way to say your original question would be: “How can I get something from row 2 from the “Something” column.”

BUT…

sorin_mihai:

So to explain, what I want is to write a formula for a notification workflow, for the email address to send the notification to. It sends it when a task is set to Urgent. But it has to send the notification to all the Team members that are responsible for the task.

This is what you’re really asking about.

In order to solve THAT problem, you’ll need to use the SELECT()

sorin_mihai:

SELECT(Makeri[Email], IN([Prenume], [_thisrow].[Echipa]))

[MultiTech · Accepted solution]

MultiTech_Visions:

SELECT(Makeri[Email], IN([Prenume], [_thisrow].[Echipa]))

Some version of this formula is what you’re going to need to use in the place to enter emails to send your workflow to:

[Aleksi]

The “_ROWNUMBER” means it tries to find the value from row 2 as it is set in the LOOKUP formula. The LOOKUP is the same than ANY(SELECT(Sheet[ColumnName],[_ROWNUMBER]=2))