Using base format "%" and conditional format "$"

[Lennane]

Thanks to all that post responses to questions on this forum. I have successfully used many over the last six months.

My first post now.

I can't find a solution to my current problem. Did search but no specific titles indicating that the issue was covered previously. Apologies if it has.

I am formating some cells with a base % format. To enter 10% I enter "10", not ".1". Makes % entry very simple.

However the cells reference a data validation list with a number of % options that also have one option for a $ value. So I have a conditional format that applies a currency format in that case.

The issue I have is that when I enter $'s into the cell I have to add the cents without a decimal point or else the base format is applied. That is, for $100,000, I want to enter 100000 but excel expresses that entry as $1,000 after applying my conditional format. The cell value shows 100000%.

Any suggestions? Thanks in advance.

 

[Jerry Sullivan]

Hi Lennane and Welcome to the Board,

If a user enters 10, you'll need some way for Excel to distinguish whether they mean 10% and not $10.

If you have a fixed number of % and $ options, one approach would be to have your DV drop down list include the $ or % symbol. The users could either select items from the drop down or type in the values with those symbols, but they wouldn't be allowed to enter just a number like 10.

If you want to allow the users to enter values not on your DV list, then you'll need to add some signal or rule that could be handled by a Conditional Formatting rule. Examples might be:
1. If less than or equal to 100 it's a %, if more than 100 it's $
2. If it has a $ symbol it's dollars, otherwise it's %.

I'm not sure if any of those are feasible, but until Excel evolves to read minds (Excel 2016 ) you'll need some rules to get your formatting to work the way you want.

 

[Lennane]

Thanks.

Those options are feasible, but don't seem to work for me.

I have also used the format [<=1]0.00%;$#,##0. But the issue is that if the user initially enters a % value, then chooses the option to use a $ value the format doesn't change and the user has to add the cents too.

I want the user to use the data validation selection to set the format, but be able to change back and forward at will.

What gives?

 

[Jerry Sullivan]

If the selections will be made from the DV list or at least must match it, then we should be able to figure out a solution.

We need some way to distinguish 10% from $10 in the DV list. If we just do that with formatting in the DV list, then I don't think it will work (either way it will put the value 10 into the DV Cell).

One option is the format the DV list is text and put the % and $ into the strings. That seems a little crude, but that's my first thought. Using the CF formulas below, the selected text can appear as you want it to be. In a few tests, the displayed values seem to work in formulas as numbers; however it wouldn't suprise if me if there are some differences.

Excel Workbook
	A	B	C
1	30%		10%
2	$10		20%
3	10%		30%
4	$10,000		40%
5	40%		$10
6			$10,000
7			$20,000
Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =ISNUMBER(FIND("%",A1))	Abc
A1	2. / Formula is =ISNUMBER(FIND("$",A1))	Abc

#VALUE!

I need to sign-off for a couple hours, but I hope to have a better idea when I log back on. (or maybe another member will come up with something better before then).

 

[Jerry Sullivan]

Duplicate (sorry!)

 

[Lennane]

Thanks for your efforts Jerry. I will endeavor to assist by showing a bit more detail as to my exact issue while you are away. Regards,

 

[Lennane]

Hope this clarifies.

A) Here are my data validation options for the cost category "Planning Costs":

<TABLE style="WIDTH: 147pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=196><COLGROUP><COL style="WIDTH: 147pt; mso-width-source: userset; mso-width-alt: 7168" width=196><TBODY><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 147pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 height=21 width=196>Cost_Base_2</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 height=21>% of Land Cost</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 height=21>% of Construction Cost</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 height=21>% of Gross Realisation</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 height=21>% of UCV</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 height=21>Total $</TD></TR></TBODY></TABLE>

The user selects one of these options.

B) They then enter a value for planning costs in another cell - either a percentage of the chosen cost base or a total $ value.

C) Another cell applies a subsequent formula to the value entered in B) using an IF function.

The issue I have is in B). I would ideally like the user to be able to enter a % value as 5 for 5%, and 50000 for $50,000. To do this the format needs to be conditioned on the selection in A). But, I want the user to be able to change their mind and then select an alternate option in A). The problem seems to be that when the first election is made (e.g. % of ...)the formating of the cell occurs and then that becomes the cell's default. When I then re-select the $ option in A) I have to enter two more zeros and the cell shows the % symbol at the end of the number string. Unless I do this, the cell shows a $ amount but divided by 100 of what I want. This obviously affects my subsequent formula.

Hope this is clearer.

 

[Jerry Sullivan]

That clarification helps - I misunderstood and thought the DV list was a menu that had the values with some formatted as % and some as $.

I've puzzled around with this a bit, and I'm not seeing a way to get all the functionality that I think you want (User data entry, display, correct numeric value if referenced) without using VBA. Is VBA is an option?... because it would be much easier.

The closest option without VBA that I can think of would be:
User data entry: User enters 10 for 10% or 10 for $10 depending on DV selection. (Meets your criteria)

Display: Would display 10% or $10. (Meets your criteria)

Correct numeric value if referenced: Here we have to cheat.
Whether the DV selection is % or $ the value will be 10. The good news is, if we can only get 2 out of 3 criteria, compromising this one has the most options for work-arounds. For example, we can have a helper cell that divides the value by 100 if the DV selection is %, and then reference that helper cell in formulas instead of the DV cell itself.

Let me know if you want to pursue that approach, or VBA, or if you would rather hold out for a better idea.

 

[Lennane]

VBA is fine, up to a point. I have incorparted some VBA into my model using tips from this site, but I am far from an expert.

I think the problem is that conditional formating applies after a cell value is entered. VBA can set the alternate formats after the selection is made from the data list, I presume.

Thanks Jerry.

 

[Jerry Sullivan]

Since your workbook is already macro enabled, then VBA seems like the best option.

Here is a first pass at some code.
It assumes your DV drop down is in A2 and the cell that the user enters the value in is B2.
(We can adjust the relationship between the cells if this test works for you).

To use the code (always test new code on a copy of your workbook):
1. Right Click on the Tab of your user-interface sheet
2. Select View Code
3. Paste the Code below into the code module
4. Close the editor

Private Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Target, Range("A2")) Is Nothing _
        Or Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False

    With Target(1, 2)
        If InStr(Target, "$") Then
            If InStr(.NumberFormat, "%") > 0 And _
                IsNumeric(.Value) Then _
                .Value = .Value * 100
            .NumberFormat = "$#,##0"
        Else
            If InStr(.NumberFormat, "%") = 0 And _
                IsNumeric(.Value) Then _
                .Value = .Value * 0.01
            .NumberFormat = "0%"
        End If
    End With
    Application.EnableEvents = True
End Sub

EDIT: Make sure to clear all your CF rules for these cells. They aren't needed if you use this code.