Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / April 2008

Tip: Looking for answers? Try searching our database.

Displaying different values without using formulas

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
PO - 21 Apr 2008 13:30 GMT
Excel 2003, sp2

Hi,

My worksheet has a group of cells (range A3:B10) where the user can enter
numeric values. The user can also enter a percentage value into range B1.
When the user enters the percentage value into B1 I want the numbers in
A3:B10 to display the original value multiplied by B1.

A3:B10 however doesn't contain any formulas (since the user can enter values
and "sabotage" the formulas). I want the original values to remain intact
and just display the new value

If I use macros to update the values I loose the original values.

Is this possible without using formulas (and without using macros), i.e.
using some kind of formatting code?

Regards
PO
Pete_UK - 21 Apr 2008 13:55 GMT
I'm not aware of any formatting code which will do this for you.

Pete

> Excel 2003, sp2
>
[quoted text clipped - 16 lines]
> Regards
> PO
Dave Peterson - 21 Apr 2008 14:05 GMT
If you want to keep a value in those cells, you'll have to use a macro.  In
fact, if you wanted to use a formula, you'd need to use some helper cells.

But how would you know what values are original?  If I change a value (from 1 to
10, say) and the value in B1 is 110%, is my original value 1, 10, 1.10, or 11.0?

If you can decide, maybe you could put those original (whatever that means) on a
separate (hidden) sheet.  

Without caring about the original values, you could use a worksheet event that
does the work:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

   Dim myRng1 As Range
   Dim myRng2 As Range
   
   Set myRng1 = Me.Range("A3:B10")
   Set myRng2 = Me.Range("B1")
   
   With Target
       If .Cells.Count > 1 Then
           Exit Sub
       End If
       
       On Error GoTo ErrHandler:
   
       If Not (Intersect(myRng1, .Cells) Is Nothing) Then
           'in A3:B10
           If IsNumeric(myRng2.Value) Then
               If IsNumeric(.Value) Then
                   If IsEmpty(myRng2.Value) = False Then
                       Application.EnableEvents = False
                       .Value = .Value * myRng2.Value
                   End If
               End If
           End If
       ElseIf Not (Intersect(myRng2, .Cells) Is Nothing) Then
           'in B1
           If IsNumeric(.Value) Then
               If IsEmpty(.Value) = False Then
                   Application.EnableEvents = False
                   .Copy
                   myRng1.PasteSpecial Paste:=xlPasteValues, _
                       operation:=xlPasteSpecialOperationMultiply
                   Application.CutCopyMode = False
                   .Select
               End If
           End If
       End If
       
   End With
   
ErrHandler:
   Application.EnableEvents = True

End Sub

> Excel 2003, sp2
>
[quoted text clipped - 16 lines]
> Regards
> PO

Signature

Dave Peterson


Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.