Question

Assistance building a calculation or formula based off a text selection matching another field

Forms

Updated September 6, 2024

asked on September 6, 2024

Attached is a screenshot of what I am trying to do. Our employees will make a selection in the first column (which is not in the screenshot), that selection populates "EE Only, EE + 1, Family" with dollar amounts. Then they will choose the "Coverage Level" they want which has matching labels and values (the values also match the sql table column names) of their selection. The final column "Rate" should contain the value of which coverage level they choose. For example, if EE + 1's column was $1000 and the employee choose EE + 1 in Coverage Level, the Rate would populate with $1000.

0 0

Answer

SELECTED ANSWER

replied on September 6, 2024 • Show version history

Assuming this is a table, try this:

=IF(INDEX(Table.Coverage_Level,ROW())="EE_Only",INDEX(Table.EE_Only,ROW()),IF(INDEX(Table.Coverage_Level,ROW())="EE_1",INDEX(Table.EE_1,ROW()),IF(INDEX(Table.Coverage_Level,ROW())="Family",INDEX(Table.Family,ROW()),)))

You will need to ensure the variables are correct in the formula. I didn't have your table name or exact variables.

If it is not a table, remove the indexes and rows from the formula. Also, edit the variables.

1 0

Replies

replied on September 6, 2024

Here are my table names and variables. I feel like we're super close.

Table = 'Medical_Plan_Options'

"Choose Medical Plan" = 'Medical Plan' Column 1

"EE Only" = 'EE_Only' Column 2

"EE + 1" = 'EE_1' Column 3

"Family" = 'Family' Column 4

"Coverage Level" = 'Coverage_Level' Column 5

"Rate" = 'Rate' Column 6

The SQL table name that has the values in it that the table is pulling from is dbo.openenrollment and it's columns are:

UniqueID

Category

Plan Options

EE_Only

EE_1

Family

I tried to keep variable names the same as much as I could.

I'm still not quite getting it to work.

0 0

replied on September 6, 2024

OH MY GOSH IT WORKS!!! I had just a couple syntax issues that were with my use of underscores! This worked!

=IF(INDEX(Medical_Plan_Options.Coverage_Level,ROW())="EE_Only",INDEX(Medical_Plan_Options.EE_Only,ROW()),IF(INDEX(Medical_Plan_Options.Coverage_Level,ROW())="EE1",INDEX(Medical_Plan_Options.EE1,ROW()),IF(INDEX(Medical_Plan_Options.Coverage_Level,ROW())="Family",INDEX(Medical_Plan_Options.Family,ROW()),)))

0 0

replied on September 6, 2024

If I can add one more catch to this... is it possible to add on to this advanced calculation? One option in the dropdown for Coverage Level = "Decline" I have a row in my sql table called Decline. If a user chooses "Decline in Coverage Level or Medical Plan (I can use either one) I need $0.00 in the Rate.

0 0

You are not allowed to follow up in this post.

Question

Question

Assistance building a calculation or formula based off a text selection matching another field

Answer

Replies

Sign in to reply to this post.