• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using conditional formatting in a sortable database

I

InsuranceSolutionsSoftware

New Member
I have a multi-column database of annuity products that brokers can use to determine the best product for their client based on state approval and other criteria. In some cases brokers will "hide" (conditional formatting) products in the list that they do not want the client to even consider. Brokers can then "rank" remaining visible products by highest values. There are disclaimers that are tied to several products which are also hidden if that product is hidden.

The problem occurs when you "rank" (which of-course sorts and re-positions all records in the database), the conditional formatting for the disclaimers do not stay with the product record as it moves up/down in the ranking, but remains with the original cells. Any ideas on how to remedy this? Any potential solution must work for Excel 2003 and later. I look forward to your suggestion. Thank you in advance.
 
A lot of this question is tied to the layout of your data. Could you post a sample workbook somewhere?

http://chandoo.org/forums/topic/posting-a-sample-workbook


Are the disclaimers in the same row as the product? Why aren't they getting sorted...are there gaps in columns/rows? Or is the ranking part of a formula input?

If there's formulas, watch out for this sorting oddity:

http://spreadsheetpage.com/index.php/oddity/sorting_oddity_bug/


PS. You might want to shorten your profile name, if possible. It's running into your posts. =/
 
How are the disclaimers normally "tied" to the record? Is it like a footnote, where you do:

Option A *

Option B

Option C +


* This has warranty #1

+ This has warrant #2


If so, are the footnote markings in their own column?
 
Luke M: Yes disclaimers are as you outlined above. The reason I want to see if there is a conditional formatting solution, is because some of the footnotes have formulas and when using an IF statement, this information gets pretty tricky to formulate.
 
Hmm. Assuming the brokers are ranking the lines by putting numbers in some column, and again, assuming the footnote marker is in it's own column, the CF formula for the footnote at end would be:


=SUMPRODUCT(--(RankColumn>0),--(FootnoteColumn=MySymbol))=0

Format, invisible (white)


Where MySymbol could be a cell reference, or the text, of the footnote symbol used. If you have 2007 or later, might also be able to use a COUNTIFS function.
 
Luke M: Would like to touch base with you if you provide excel consulting services. Go to www.AnnuityDoubleFeature.com, use the "Contact Us" link to send your info if interested.


InsuranceSolutionsSoftware
 
You must log in or register to reply here.
Back
Top