Need help with a formula!

Martha Hemingway

Hi community,

I could use help with a formula. I have a sheet that has all of our Claims data (types, dates, etc) and then a separate sheet that identifies the different claim fees per type of claim.

I am trying to have each claim reference the Per Claim Fees sheet to take the type of claim (Auto, Workers Comp, General Liability, etc), the Year the claim was reported, and the state to identify which fee to return to the Claims data sheet. For example, in the screen shot below in the blue box with my cursor, I would want the result to be: $1766. ($1,728 + $38) or (WC Indemnity - CA, HI in 2023 + Incidents flat rate fee)

Here are the column in my Claims data sheet:

Here are the columns referencing fees per type per year to show where I got my $#s:

For the bottom row on my Claims data sheet screen shot (Compensation - Indemnity) in 2022, I want the Per Claim Fee to return $1,749 ($1,711 + $38).

Thanks for the help! I feel like this is probably going to be a massive IF statement with some sort of Join/Collect combo maybe?

@Paul Newcome think you could help with this one?

Paul Newcome

If the year is 2023, how do you determine whether it pulls from the 22-23 column or the 23-24 column? Are you able to add some helper columns in either sheet so that wee can have some consistency in data for matching?

Martha Hemingway

@Paul Newcome The reason the Claim Fee sheet has the columns that way is how the user requested to review based on the covered insurance period. So any accidents that are in the "accident year" of 2021, will always need to reference the 21-22 Per Claim Fee column. Any accidents in 2022 will always need to reference the 22-23 Per Claim Fee column, and so on.

Yes - I can add helper columns to either! What's the best way to do this?

Paul Newcome

Ok. So the year in the cell will coincide with the first year in the column header.

If the [WC Indicator] in the main sheet is filled in, then we can assume it is pulling from the Workers Compensation section of the reference table, and if that field is blank, we can assume it is coming from the Liability section?

In the reference table, we are going to at least need a multi-select dropdown to indicate the states as applicable, and a text/number column to put either Worker's Compensation or Liability on each of the rows as applicable.