Referencing Column Name from another table

#1

In my Christmas list, I am logging what the toy is and for each toy, what kind of batteries that it takes.

Then, I am trying to reference how many of each type of battery that I will need in a summary table, like this:

image

So, I’m trying to do something like “Get the sum of the batteries where the column name in Toys equals thisRow.[Battery Type]”.

My formula looks like this:

=Toys.Lookup(thisRow.[Battery Type], CurrentValue > 0).Sum()

But I get a “Wrong Argument Type” error.

Can you please give me some guidance on this?

Thank you.

#2

Are there toys that might require two different battery types or is it just one type per toy?

#3

I would suggest you to make only one column for batteries that is a lookup from you battery column in the battery table.
Then you would have another column next to the lookup with the quantity.
In your battery thanks in the quantity field you add formula : Toys.filter(thisrow=thisrow.battery.totext()).sum

It’s possible that my formula isn’t completely right :grin:

1 Like
#4

Create a “Battery Types” table with the type of battery listed in the first column. Then create a “Toys” table where you have the name of toy and where the “Battery” column is a Lookup from the Battery Types table. This will let you sort and filter easily and keep things consistent.

Then back in the Battery Types table, create a “Total Needed” column and add the formula:

=Toys.Filter(Battery=thisRow).[How Many?].Sum()

Here’s a screenshot of a setup if that helps…

3 Likes
Get sums of different columns of another table
#5

These are all great responses. It’s possible that some toys could take multiple types of batteries. I don’t have any at the moment that do, but that could change. For instance, if I was to get a remote controlled car that took AA batteries for the car and AAA batteries for the remotes.

Thank you for all of your suggestions; I appreciate the time you have taken to assist me.

I do have sums on the “Toys” table, so this is not necessarily something that I NEED. I’m just curious to see if it could be done and to learn the process for working something like this out.

#6

I have figured it out! I used the switch statement to check the type of battery used and then summed the corresponding column in the Toys table.

=Switch(
     thisRow.[Battery Type], 
         "AAA", Toys.AAA.Sum(), 
         "AA", Toys.AA.Sum(), 
         "C", Toys.C.Sum(), 
         "D", Toys.D.Sum(), 
         "9V", Toys.9V.Sum()
)

Thank you everyone again for your assistance in figuring this problem out. You guys are terrific!

-Rick

1 Like