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

L

Lennane

New Member
Joined
Oct 6, 2011
Messages
13
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.
 
Sort by date Sort by votes
Thanks Jerry.

Not quite as simple as that. Refer below:

<TABLE style="WIDTH: 496pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=660><COLGROUP><COL style="WIDTH: 124pt; mso-width-source: userset; mso-width-alt: 5280" span=4 width=165><TBODY><TR style="HEIGHT: 21pt" height=28><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 248pt; HEIGHT: 21pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 height=28 width=330 colSpan=2>Data Validation List</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 248pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=330 colSpan=2>Corresponding User Data</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 18.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=25>Sheet Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Cell Reference</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Sheet Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Cell Reference</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Title & Preferences</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>L21</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Project</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>G29</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Title & Preferences</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>L22</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Project</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>G24</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Title & Preferences</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>L23</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Project</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>G25</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Title & Preferences</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>L25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Project</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>G33</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Title & Preferences</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>L26</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Project</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>G34</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Title & Preferences</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>L27</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Project</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>G35</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Title & Preferences</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>L28</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Project</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>G36</TD></TR></TBODY></TABLE>
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
If you are referring to the multiple cells available for DV list selection and entering the number value, we can handle that as step 2.

Try the example using just Cell A2 for DV dropdown and B2 for values and let me know if the format and behavior are what you want.

Also, I'm not following from your screen shot how the 4 columns shown relate to the 2 columns we have been discussing. Please clarify that.
 
Upvote 0
Yes, that works beautifully. I will change the % format to 0.00%, but otherwise perfect.

Its not a screenshot. Its a summary. There are two sheets. The DV selection occurs in the cells on the sheet "Title & Preferences". So there are seven cells on that sheet (L21-L23, L25-L28) that the user can select an option from. Each of those cells links back to a named list on another sheet.

The user data is entered on the sheet "Project" and the cell references relate to the adjacent DV cell reference. So L21 and G29 relate.

Note the relationships are not contiguous.

Appreciate the great help Jerry.
 
Upvote 0
I am afraid this is beyond my comprehension Jerry.

Apologies if I am stretching the friendship, but how do I now execute step 2.
 
Upvote 0
Lennane said:
I am afraid this is beyond my comprehension Jerry.

Apologies if I am stretching the friendship, but how do I now execute step 2.
Click to expand...

Hi Lennane,

The apologies are all mine - I lost track of this as an unresolved thread.

You can copy this revision into your "Project" sheet code.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sDVal_Addresses As String, sUser_Addresses As String
    Dim lIdx As Long
    sDVal_Addresses = "$L$21,$L$22,$L$23,$L$25,$L$26,$L$27,$L$28"
    sUser_Addresses = "$G$29,$G$24,$G$25,$G$33,$G$34,$G$35,$G$36"
    
    If Intersect(Target, Range(sDVal_Addresses)) Is Nothing _
        Or Target.Cells.Count > 1 Then Exit Sub

    On Error GoTo CleanUp
    Application.EnableEvents = False
    
    '---get item index in string lists
    lIdx = WorksheetFunction.Match(Target.Address, _
        Split(sDVal_Addresses, ","), 0)
      
    With Sheets("Project").Range(Split(sUser_Addresses, ",")(lIdx - 1))
        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
CleanUp:
    Application.EnableEvents = True
End Sub

Depending on how often you edit your mapping, you might consider having the mapping as a table in your worksheet instead hard-coded into the VBA code.

Just let me know if you would like to help with that change. :)
 
Upvote 0
Hi Jerry,

Thank you.

If I insert into "Project" sheet code it will search only that page for each cell reference. So should I put in "Title & Preferences" where there is no reference to that sheet in the code? I'll try that first.

Regards, Brett
 
Upvote 0
Lennane said:
Hi Jerry,

Thank you.

If I insert into "Project" sheet code it will search only that page for each cell reference. So should I put in "Title & Preferences" where there is no reference to that sheet in the code? I'll try that first.

Regards, Brett
Click to expand...

You're right Brett. It should be the sheet with the DV Cells which is T&P.
 
Upvote 0
Jerry......

Yes, it's brilliant! Exactly what I needed.

Extensive testing to come but it looks perfect.

Thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Conditional Formatting of a formula based date
Replies
8
Views
295
Alex Blakenburg
A
J
  • Question
Conditional Formatting.. The bane of my Excel Existence
Replies
2
Views
102
RoryA
RoryA
J
  • Question
Conditional formatting based on value of another cell
Replies
3
Views
292
etaf
etaf
M
  • Solved
How do you calculate a conditional sum for conditional formatting?
Replies
9
Views
306
Peter_SSs
Peter_SSs
S
  • Question
Conditional Formatting with Self-Reference to ISBLANK
Replies
6
Views
264
Fluff
Fluff

Forum statistics

Threads
1,215,573
Messages
6,125,608
Members
449,238
Latest member
wcbyers

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top