I am trying to make a model/calculator. It should take data from a dropdown and find that data point in a separate column and then return the row data for that column.
For example, I need to add data to the Hosting Cost column. The data inside the cells in this column should change when Tier selection drop down changes. The formula needs to take the tier # selected in the drop down, find that # on the Hosting Cost Table, and return the correct row data for each row. I cannot figure this out to save my life. Embedded the doc here, feel free to add the formula!
From there, it needs to find this Tier on the Hosting Discount Table. Once it finds that column, it needs to return the correct row data for the 3 rows.
The desired output for row data in the Hosting Discount column in the Pricing Options table is -33.33% in row 1, -43.33% in row 2 and -53.33% in row 3.
hi @Chris_Alfano
Your setup should not be an hard one!
If i had to make it work, i would “transpose” the “Hosting Discount Table”, you can arrange it like
a select list where the number (10.000, 25.000, 50.000 ecc) is expressed as a row value instead of a column name
Let me prepare you an example
In this way you can set the formula into “Hosting Discount” to be like:
[Hosting Discount Table].filter(numbers of $.contains(pricing tier up to selectlist))
Then for the months you can use a switch formula, to check if it’s 3, 6 or 12 months
@Mario Thank you so much for clarifying. I have transposed the Hosting Discount Table like you mentioned (see picture). But am still struggling with the formula.
Currently I have: [Hosting Discount Table New].Filter([Pricing Tier].Contains([Pricing Tier]))
The formula as shown above returns . I am not sure how to incorporate the switch feature to return the actual % in the right row. Can you please tell me how to use the switch function in my formula?
Hey @Chris_Alfano don’t worry!
Share here a copy of your doc and i’ll fix this for you (i’m not able to access the first post one)
P.s. then you can see it working and “unmount” it to fully understand
One thing that is now not working, is on the Generate Pricing Button. For some reason the button is not being disabled when the SqFt slider > Pricing Tier selector.
