Pull value from cell in current row based on the value of another cell

[steve1123]

Hello,

A group of 5 partners have expenses that are incurred. Typically, they divide it equally, so 20% per partner. Occasionally, an expense will be allotted more towards one partner than the others. When it comes time to invoice the partners, each partner must pay their amount due.

I have a setup similar to the Slice based on User Input template.

The user selects a partner’s name from a drop down, which populates a table called Invoice_Filter with their name. The various expenses are shown via a dashboard view, and the user can select an expense, see the total amount, and see the percentages allotted to each partner. Sample table below

I want to create a virtual column that will take the amount and divide by the appropriate percentage that has been assigned to the selected partner. Such as:

Partner: Name 1
Amount: $100
Per Partner: $40 ← Virtual Column Desired

Partner: Name 3
Amount: $100
Per Partner: $20 ← Virtual Column Desired
(Above Formula would be something like: [Amount] * (Partner’s name [Percentage])

I thought FILTER would do the job, but I can’t seem to figure it out. I’m sure it’s something very simple but I can’t wrap my mind around it.

Any help would be greatly appreciated. Thank-you.

[Aurelien]

Hi @steve1123

I think it would be interesting to know more about the table structure for percentage alloting, and about more table structures in your app, in general.
However, based on your screenshot, and assuming:

• the screenshot you are providing is a table, with a permanent structure (starting from row 1 nonetheless)
• its name is “Percentage”

Then here is my suggestion:
[Amount]*ANY(Percentage[Name1]) for name1
[Amount]*ANY(Percentage[Name2]) for name2
etc.

[steve1123]

Thank-you for your reply.

The table that contains the filter that the user uses looks like the image below and is called Invoice_Filter.

And yes, the table referenced in the original post does start from row one. My apologies, I just copied and pasted for the screenshot and didn’t put it in row 1.

I think the main issue is that the Invoice_Filter does not have the percentage of the transaction, that is only contained in the Invoice_SA sheet that is reference in the screenshot of the original post.

So, I need a way of using the name value in column B of Invoice_Filter called “Partners” (screenshot in my original post), to then search Invoice_SA (screenshot attached to this post) for the same name along the top row (columns C to G) and pull the related percentage for the charge. And that’s what would get multiplied by the “Amount”.

I’m sorry, I know I’m not doing a great job of explaining this. Please feel free to ask further clarifying questions.

Thank-you.

[SkrOYC]

steve1123:

I want to create a virtual column that will take the amount and divide by the appropriate percentage that has been assigned to the selected partner. Such as:

1. This is just 1 VC that will be applied based on the user that is interacting with the app?
2. There are going to be 5 VC?

For 1) You need a user table
For 2) The following AppFormula can do the trick:

[_THISROW].[AMOUNT]*[_THISROW].[NAME X]

Now, make sure the columns that has percentages are configured as “Percent” and don’t try to use your data outside of AppSheet if it’s not just for Data Analysis (Using PowerBI for example)

[steve1123]

1. Correct. Exactly. Just one VC that will be applied based on the user. The user interacts with the Invoice_Filter sheet. The Invoice_Filter is my user table, in effect. Sample screenshot below.

I need to search the Invoice_SA table for the name in my user table, Invoice_Filter, and retrieve related percentage for that transaction.

Forgive me, but the only issue I am having with your suggested expression is that the name is not fixed. It needs to pull that name based on the user table, Invoice_Filter, under column B “Partners”. Unless I’m missing something (I likely am, no doubt).

Thank-you for your time.

[SkrOYC]

steve1123:

I have a setup similar to the Slice based on User Input template.

The user selects a partner’s name from a drop down, which populates a table called Invoice_Filter with their name. The various expenses are shown via a dashboard view, and the user can select an expense, see the total amount, and see the percentages allotted to each partner.

Ok, I ignored this a bit in my previous answer. So you don’t have a user table that filters based on the user that is using the app, instead is a manual filter.

steve1123:

I want to create a virtual column that will take the amount and divide by the appropriate percentage that has been assigned to the selected partner.

You want 1 VC that calculates the Amount for the Partner that is selected on the Invoice_Filter table?

[steve1123]

Yes and Yes! Absolutely! Both correct.

The user manually selects the Name. This updates the Invoice_Filter table (which only has one row with the selected Partners’ name, just like the “Slice based on user input” template). Then I want one VC (in the current table, Invoice_SA) that will find the selected Partners’ name (from the Invoice_Filter) table, within columns C to G of the Invoice_SA table, and retrieve the appropriate percentage for the Amount.

[SkrOYC]

OK.
The problem is the fact that you are using Columns to hold values for people is maybe not the best Database design.
Anyway, please provide the actual name of partners to help with the expression

[steve1123]

Joe, Steve, John, Dave, Brian

Thanks!

[SkrOYC · Accepted solution]

Assuming the columns have the same name, try:

[AMOUNT]*
SWITCH(
  INDEX(Invoice_Filter[Partners], 1),
    "Joe", [Joe],
    "Steve", [Steve],
    "John", [John],
    "Dave", [Dave],
    "Brian", [Brian],
    0
)

[steve1123]

Yes!! That works beautifully. Thank-you both very much for your time and assistance with this.