Log In
Register
Home
New Member
July 4, 2023
Offline
We are trying to map data to a master sheet that is coming from various entered data that utilizing different terms. In our case, each insurance carrier calls something slightly differently and it may come in a different order.
A user gets an offer sheet from 5 various carriers. Each carriers are terms are defined in the terms tab and when select on the select sheet, the carriers terms are returned. A column is provided for the user to type in the amounts the carriers are offering for each coverage. We want to 'map' if CNA grab Media Liability and put if in the comparison sheet b11. CNA is derived from the select sheet in A6. The comparison sheet has 'master terms' we want to show to the client even if each carrier calls it something different. We have tried match, if xmatch and attempted nested ifs and offset, but can't seem to get it right. A few tests are on the comparison sheet.
We could have a huge if 'CNA' then offset to a cell, but CNA maybe in a different column each time. We know the row will be the same for each carrier since the terms are fixed in a specific order.
It first need to know which column to use, know how far down and 1 column over since all the values are 1 column to the right f the carrier.
I hope this
Trusted Members
October 17, 2018
Offline
Hi Gregg,
I paly around with similiar issues and different sources which need to be placed in the one single table.
What I did and I hope I can explian it is that I have a worksheet named ADMIN (any name is okay) and in the sheet column A lists the filednames I haev in the main sheet
So the Report worksheets has let's say 20 columns the Column A in ADMIN is the transposed copy of these columns strating with row 2
In Column B I have the column letter correspondign to cell
ADMIN A1 = Coverage Line
ADMIN B1 = Column letter
A2 is Network Security & Liabiliy
B2 = A
etc.
In column C1 you place the provider and in C2 the corresponding column header for that provider and so on.
Then all you need to do is check the provider column and use the corresponding column letter in B to place the value
It might sound/read complicated but try it out and see if you can manage.
New Member
July 4, 2023
Offline
Sorry, I'm not following the reply. I have attached the sheet to help. I have been trying match and offset. I also added reference numbers if that would help match the lines to grab the right number. I need to figure out what carrier, then use that column to know which row to use.
Trusted Members
October 17, 2018
Offline
Give me a moment and I'll try and put my idea in the file you attached
Can I assume that the basic report (yours) is the Comparison sheet with the column Coverage Line?
CNA, Allianz AWAC and more are the carriers you wish to compare?
Trusted Members
October 17, 2018
Offline
Hi Greg,
Sorry for the delay but the is is as follows.
If you check the worksheet named Basic Report you might see what I have tried to explain (I'm not that great at explaining or passing my thoughts across)
The table shows in the forst column the coverage lines you want filled with the correct description as you want it.
The second one is the Report Row and this you have to fill in as to in which row you want it in the Comparison Sheet
In the columns next to it youenter descriptions for the same row that come in the CNA, Allianz, AWAC etc
Now all you need to do is when reading the CNA just do a find in this BAsic Record table anduse the value in column B to fill the corresponding row in the Comparison sheet.
You can probalbly do it with formulas, I would do it using VBA
Hope I have been able to explain my idea in an understandable way
1 Guest(s)
