Duplicate virtual column value based on matching row values in different tables

[Doors]

Hi All,

I have 25 tables where School classes can choose from multiple lunch items from an enum list.

This example table is called “Angelfish” and the class column is pre-populated from a google sheet to reflect the same value.

I have three virtual columns for each dinner selection that counts the number of orders for meat, veg or special.

I’d like to reflect these three separate counts on a different data table called “Summary”:

So the expression needs to reflect the following

Meat Column Expression:

For the current row in the table “Summary” where the class column value matches the value of the class column in the table “Angelfish” show the value of the virtual column “Meat Count” from the Angelfish table.

I hope that makes sense? Thanks in advance.

J.

[Aleksi]

For example the Meat virtual column could have a formula like…
COUNT(SELECT(Dinners[KeyColumn],AND(
[Class]=[_THISROW].[Class],
[Date]=TODAY(),
[Dinner]=“Meat”)
))

[Doors]

Thanks @Aleksi but how does that expression dynamically check all 25 different tables to bring dinner selection data into the rows of my sumamry table?

In other words I dont have a dinners selection table called “dinners”. I have 25 separate tables (From Google sheets tabs) that are called various things. (Angelfish, Dolphin, Pufferfish etc)

Thanks, J.

[Aleksi]

It sounds that you should think about your app structure a little… otherwise your formulas will be too complex. Is there any reason why do you need to have 25 separate sheets? Why don’t you add all data in one sheet?

[Doors]

Thanks, I though you might say that.