How can I use a column/row from a separate table in a formula

1

I have 2 tables. I want to achieve the following formula:

table1 column 5 row 1 * table2 column 1 row 1

How can I achieve this?

Does it matter if tables are in a separate “section” entirely?

Thanks

2

Dear @Colin_Winhall,

A possible solution in steps is as in the attached doc, you can find a sample how to do this, by using a 3rd table to take out info from table 1 and table 2 and to multiply the amounts

Hopeful this gives the right direction :bulb:

Best regards,
//JP

1 Like
3

Thanks for the suggestion JP. It is indeed a possible solution but it just seems like it is more effort than it’s worth. It is also becomes very messy when scaled up.

I’m surprised there is not an elegant and easy way to achieve something that is such a basic and often used function in excel/google sheets.

4

There is! You can use the Filtre() formula

5

I actually forgot to post this question here before, but Shirshir answered a similiar question to this one for me, what you can do to access specific cell information is something like this:

Lets say you have Table A with the columns Month, Name and Value but you need to specifically reference March and Joe’s value, here is the formula for that:

[Table A].Filter(Month = “March”).FIlter(Name = “Joe”).Value

If you want you can do that for 2 tables, so let’s imagine Table B with percentage values that you want to multiply by Joe’s value, but you need a specific percentage that is in a category X based on a select list:

[Table A].Filter(Month = “March”).FIlter(Name = “Joe”).Value * [Table B].Filter(Category = X).Percentage

So, if Joe’s value is 500 and the percentage X is 25%, the output for the last formula is going to be 125.

That way you are getting the value of the column with the month that you want and the name that you want, meaning you can aim for whatever cell that is needed, although this is a little too complicated for such a simple thing in excel and etc, but it still works.

I hope that it helps you! Cheers

2 Likes
6

Thanks Pedro you set me on the right track to get this working. Had to take apart the formula bit more to make it work for my scenario but I have it working now! :grinning:

7

Good work man! Would you mind to share an example of your scenario in case other people get stuck in the same problem?

8

You can refer to a specific row with @row and you can refer to a specific column of that row with =@row.columnName.

Though not exactly what you’re asking about, this example should help: