Automatic display positive or negative

Dear All, pls help me.
when I type $ 52.00 on C1, I want C1 display positive $52 or negative ($ 52) 
, if B1 is positive or negative, I want the entire column C.
Exemple : 
C1 : I type $52, it display for me $52 , because B1 is positive
C2 : I type $52, it display for me ($52) , because B2 is negative
       B           C
B1   5         $52
B2  (3)       ($52)

It is possible ?
Thank you for your help.

0
Utf
5/8/2010 4:06:01 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
337 Views

Similar Articles

[PageSpeed] 19

This can only be done with VBA and the code below will do it for you.  To use 
the code, open the workbook and go to the sheet you want this to work on and 
then:
Right-click on the worksheet's name tab and choose [View Code] from the list.
Copy the code below and paste it into the code module presented to you in 
the previous step.
Close the VB Editor and test it by making entries into columns B and C.

Note that you must make the entry into column B before you make the entry 
into C.  As written, it does not change the entry in C if you make a change 
in B after an entry is already in C.  If you change B, then you would need to 
re-enter into C to see the change made.

Private Sub Worksheet_Change(ByVal Target As Range)
'only operates when a single cell in column C changes
  If Target.Column <> 3 Or _
   Target.Cells.Count > 1 Then
    Exit Sub ' not in column C
  End If
  'if both B# and C# have entries
  'then make C entry same sign (+/-) as B
  If Not IsEmpty(Target) And _
   Not IsEmpty(Target.Offset(0, -1)) Then
    If Sgn(Target.Offset(0, -1)) <> Sgn(Target) Then
      Target = Target * -1
    End If
  End If
End Sub


"tran1728" wrote:

> Dear All, pls help me.
> when I type $ 52.00 on C1, I want C1 display positive $52 or negative ($ 52) 
> , if B1 is positive or negative, I want the entire column C.
> Exemple : 
> C1 : I type $52, it display for me $52 , because B1 is positive
> C2 : I type $52, it display for me ($52) , because B2 is negative
>        B           C
> B1   5         $52
> B2  (3)       ($52)
> 
> It is possible ?
> Thank you for your help.
> 
0
Utf
5/8/2010 5:31:01 AM
Copy and paste the below formula in C1 cell.
=IF(B1>=0,52,IF(B1<0,-52,""))

Select the entire C column do right click>>Format 
Cells>>Number>>Category>>Currency>>Symbol>> Select $>>Negative 
Numbers:>>select ($1,234.10) and give ok.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"tran1728" wrote:

> Dear All, pls help me.
> when I type $ 52.00 on C1, I want C1 display positive $52 or negative ($ 52) 
> , if B1 is positive or negative, I want the entire column C.
> Exemple : 
> C1 : I type $52, it display for me $52 , because B1 is positive
> C2 : I type $52, it display for me ($52) , because B2 is negative
>        B           C
> B1   5         $52
> B2  (3)       ($52)
> 
> It is possible ?
> Thank you for your help.
> 
0
Utf
5/8/2010 5:57:01 AM
oh Yes, thank you so much.



"JLatham" wrote:

> This can only be done with VBA and the code below will do it for you.  To use 
> the code, open the workbook and go to the sheet you want this to work on and 
> then:
> Right-click on the worksheet's name tab and choose [View Code] from the list.
> Copy the code below and paste it into the code module presented to you in 
> the previous step.
> Close the VB Editor and test it by making entries into columns B and C.
> 
> Note that you must make the entry into column B before you make the entry 
> into C.  As written, it does not change the entry in C if you make a change 
> in B after an entry is already in C.  If you change B, then you would need to 
> re-enter into C to see the change made.
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> 'only operates when a single cell in column C changes
>   If Target.Column <> 3 Or _
>    Target.Cells.Count > 1 Then
>     Exit Sub ' not in column C
>   End If
>   'if both B# and C# have entries
>   'then make C entry same sign (+/-) as B
>   If Not IsEmpty(Target) And _
>    Not IsEmpty(Target.Offset(0, -1)) Then
>     If Sgn(Target.Offset(0, -1)) <> Sgn(Target) Then
>       Target = Target * -1
>     End If
>   End If
> End Sub
> 
> 
> "tran1728" wrote:
> 
> > Dear All, pls help me.
> > when I type $ 52.00 on C1, I want C1 display positive $52 or negative ($ 52) 
> > , if B1 is positive or negative, I want the entire column C.
> > Exemple : 
> > C1 : I type $52, it display for me $52 , because B1 is positive
> > C2 : I type $52, it display for me ($52) , because B2 is negative
> >        B           C
> > B1   5         $52
> > B2  (3)       ($52)
> > 
> > It is possible ?
> > Thank you for your help.
> > 
0
Utf
5/8/2010 10:34:01 AM
Reply:

Similar Artilces:

automatically format dates in a cell
Hi All, when I type a date into a cell, it automatically converts it to a date-time variable and formats it according to the system parameters. Is there any way I can turn this off? I don't want my date as a date time variable, nor do I want it to format to what the system format is. thanks stephan I'm not sure what a "date time variable" is, but if you want an XL date, but in the format you specify, preformat the cells with that format. If you don't want the date converted to an XL date at all, then preformat the cells as Text. They will then be left in whatev...

Finding the position of a certain picture on the screen
Hi everyone, I need to make a program in c# that will find a certain picture (not a big picture, few pixels in a certain formation, which is not necessarily a rectangle) on the computer screen. I haven't really done something like that before, so I'm not sure how to do it. what I had in mind was taking a screenshot (I found a tutorial that explains how) and then start checking every pixel in the screenshot until I find one that looks like one of the pixels in my picture, and then check if there are pixels next to him that are same to the ones in the picture, and in the same fo...

getting window position?
For a *child* window, when you set position and size using CWnd::SetWindowPos, the positioning is relative to the client area of the parent window. I must be getting tired, because look as I might, I can't find an analagous call to get the window position for a child window in terms of the parent window client area. I'm not sure I can even see how to compute it from the screen position returned by CWnd::GetWindowRect. What am I missing? It's seems obvious the call must be there somewhere, but I'm not spotting it. Thanks. Is ScreenToClient what you want? HTH -- ======...

Automatically move attachment in Exch2k
I KNOW someone here knows how to do this, or where I can find a script to do it...I need to be able to have Exchange automatically move an attached file when an email is received on a certain mailbox. Not looking to do it via Outlook, but automatically when the email arrives at the server. Any help gratefully accepted!! ...

Can I put a windows folder into outlook2003 display?
I need to know how to put a windows folder on the left hand display that directs to a windows directory to keep my outlook from getting full. Create new user folders and put emails in there instead of the default = folders Right click on Personal Folders on the left side in OL and select New = Folder. Give it a name=20 --=20 Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "Hanson23" <Hanson23@discussions.microsoft.com> wrote in message = news:8113C273-5B6A-432A-9305-D0A4672EF783@microsoft.c...

Displaying add'l value in Combo box
I have a combo box that uses a table as its row source. When the combo box displays, I would like it to display an add'l value not in the table, e.g. "<All>" I find the easiest way is to provide a listfill function. Here's mine: Public Function AddAllToList(c As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant ' ListFill function - use as RowSourceType for listbox ' Set listbox.rowsource to correct sql ' Set listbox.tag to (eg) 2;<<All>> to override defaults: ' 1;( All ) Static rs As Recordset...

My Outlook 2003 Will Not Display "Signature" on Insert Menu
For reasons that escape me entirely, my installation of Outlook 2003 on this one computer (on a network of 5) does not display "Signature" as an option on the Insert menu. Instead, it displays Insert Symbol (with S underlined.) If there's a way to change that menu structure through Customize, I don't find that either. It will apparently allow me to rearrange the sequence that options appear on the Insert menu, but will not allow me to delete or add any options. How can I get the Insert menu to display Signature as an option instead of Symbol? I shall greatly appreciate yo...

Can I automatically replace data when importing a text file?
I am importing data that has hundreds of numbers like 60003838, 60010813, 60003928, 60003895. I would like to know if there is a way to format the data to change those numbers automaticaly to another value (names) every time I import the data, or is there a way for me to replace all those values automatically? If you have a list that has names behind these numbers you can use a vertical lookup function ( VLOOKUP ). Use Insert - Function, and search for VLOOKUP function and follow the instructions. Hope this helps. "Jake" wrote: > I am importing data that has hund...

Display only folders with CFileDialog
Here is some standard code to display and return the name of a selected file. Could someone please tell me what I must change to display (and return) only folder names? Thanks. TCHAR szFilters[] = _T("Text files (*.txt)|*.txt|All files (*.*)|*.*||"); CFileDialog dlg(TRUE, _T("txt"), _T("*.txt"), OFN_FILEMUSTEXIST | OFN_HIDEREADONLY, szFilters); if (dlg.DoModal() == IDOK) { CString fileName = dlg.GetPathName(); // TODO: Whatever } Why not use SHBrowseForFolder instead? -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Ray Mitchell" &l...

Does it automatically allow bleed?
Hi all I "think" I'm seeing this correctly - I'm setting up a small gift book to print "two-up". The finished size is 6" wide by 4" high. Two of these books print in a 6" wide by 9" high space - with an inch in between for trimming apart the books apart (this is the only way I can get the size I want from the POD printer). So, I'm setting my page to be 6" wide by 9" high. Here's the question: Do I see correctly that it is allowing me about an eighth of an inch of bleed on the outside edges of the page? (If this is true - ...

Automatic Update of Navigation Bars
I am attempting to modify a basic pre-designed web page to meet my specific needs. I have chosen to include a vertical navigation bar and a horizontal one at the bottom of the page. I have also chosen that the horizontal one is updated automatically. However, I have found that this automatic update only occurs when I chose one of the pre-defined default buttons provided by Publisher, e.g. "Contact Us." If I creat a button with my own text on it the automatic update for the horizontal navigation bar does not take place. Additionally, I find that I cannot manually update that ...

Share sheet automatic on the internet.
Hello, I've got this spreadsheet where in I want one of the worksheets to be visible on the internet. This particular sheet is a sheet that shows the previous sheets making use of the camera tool in Excel. Is there a way to make Excel save and publish this sheet every time it is updated? Can Excel do this by FTP? There is no need to make it interactive. I have a server where I can make a special user and a password for this job. Could this be done with a macro? Thanks!! I think there is an option AutoRepublish every time this workbook is saved. you need chekc this option. On Oct 22, ...

automatically adding data to list
I can't seem to find any reference to this so maybe someone can point me in the right direction. I have a spreadsheet I have produced and would like to have the facility so that if I enter a figure in one cell, then the value is automatically appended to the bottom of another list. ie. New data added to cell 1A automatically is copied to the first blank cell in the column 2B or 3B or 4B etc. This could work backwards for me too if it is easier ie. Data entered at the bottom of a list (2B...3B....4B....etc) overwrites the remote copy (1A) of the value in the cell above. Is this somethin...

Displaying from: in new email messages
I am running Outlook 2000 on my laptop, with new emails creating using Microsoft Word. I have a default address that I often send email from, but sometimes I specifically want to change the sent from address and use a different account. That I know how to do, by changing it in the message under options, but I often forget to do that. Is there a way of displaying the account I am sending the message from while creating the email? Or, can the default account to be used be specified for each contact, rather that globally for all contacts? ...

Win Media player10 the Skin does not display File, option,
Hello SOD, The Media player does not display the "file, option and other menu" instead it gives a skin which is not comfortable. To be honest I believe there is some registry issue which will unlock my default skin. thanks & Regards, Mayank Pressing Ctrl+1 should put WMP back into Full Mode, which will always show the default skin. You might have to press Ctrl+M too, to see the actual menus. Regards -- Tim De Baets http://www.bm-productions.tk Mayank wrote: > Hello SOD, > > The Media player does not display the "file, option and othe...

Dynamics AX data displayed in CRM iframes
Apologies if I'm not framing this question correctly (and for the pun), but I've just seen a demo of Dynamics CRM where customised forms were displaying data from a legacy system database using iframes. I'm wondering if we can show the enterprise portal forms that list (for instance) all of the sales orders for a customer in the same way, and if so how easy is it to develop and deploy those customised views in Dynamics CRM? Thanks Tim Schofield On Feb 20, 3:36=A0am, Tim Schofield <TimSchofi...@discussions.microsoft.com> wrote: > Apologies if I'm not framing this ...

Displaying stored jpgs as pictures in a report
hello, MS ACCESS 2003 on XP PRO I've never stored jpgs in a table before, but now I apparently need to. I figured out (i think) how to store a jpg in a table using OLE OBJECT data type. But when I try to make a report of that ttable, the picture displays as a box with the file name displayed as text. How can I get the image to display on the report? Thank you, -- BlueWolverine MSE - Mech. Eng. Go BLUE! Try using an UNbound object frame and inserting the jpg in it. -- Milton Purdy ACCESS State of Arkansas "BlueWolverine" wrote: > hel...

Regional Options from control panel not displayed in Format Cells
Please can somone help me. I have searched on this topic but Alas I can not find an answer. So here is my question. When you set your number formats under control panel -> Regional options they are the ones EXCEL should use for formatting. My example is I want Parenthesis around my negative numbers ( ) . I do not want a - sign infront of number or it to be RED. So I have set my negative number symbol to () under the regional options. But if you start Excel and put in a negative number then goto Format cells -> numbers the option STILL show only a - or RED. I.E. it does not give...

Excel Rep Builder Not Displaying in SharePoint 2010 Excel Services
I am having difficulty viewing an Excel Report Builder report in Excel Services on SharePoint 2010. The error states that Excel Services does not support “Query Tables (also known as “External Data Ranges”). I know that SP 2010 can access external data sources (there is even a place to install the ODC file, separately)… so I don’t know why a simple Exel Reports Builder won’t work. Any suggestions? ...

XXX Account Number Display in Account List
Is there a way to "un-hide" the xxx masks in the Account Number field in the Account List Display? No. But R-click Details reveals that and more. It's pseudo-security at best. See http://umpmfaq.info/faqdb.php?q=169. "Raybo" <raybo@hotmail.com> wrote in message news:OoepFmi5EHA.1260@TK2MSFTNGP12.phx.gbl... > Is there a way to "un-hide" the xxx masks in the Account Number field in > the Account List Display? > Thanks, I saw that post earlier, but thought there might be some type of setting in an update that allows the account to be ...

HELP! sum value to be displayed in sheet 2 when data is in sheet 1
How do I do this? any ideas -- Message posted from http://www.ExcelForum.com =SUM(Sheet1!A1:A100) -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Muppet >" <<Muppet.18bcuz@excelforum-nospam.com> wrote in message news:Muppet.18bcuz@excelforum-nospam.com... > How do I do this? any ideas? > > > --- > Message posted from http://www.ExcelForum.com/ > =SUM(Sheet1!$A:$A) as an example for sum of Column A in Sheet1 -- Message posted from http://www.ExcelForum.com ...

Automatic Time and date in email templates
How can i use automatic time and date in email templates? Automatic as in how and where? -----Original Message----- From: manuel.martinez@keyson.es [mailto:manuel.martinez@keyson.es] Posted At: Monday, November 28, 2005 11:41 PM Posted To: microsoft.public.crm Conversation: Automatic Time and date in email templates Subject: Automatic Time and date in email templates How can i use automatic time and date in email templates? ...

How can I display multiple series in a pie chart?
Here's an example data / chart spreadsheet: http://temp.ancientpc.net/po/piechart.xls Ideally what I want to do is force the pie chart to use the most recent / lowest data series through some way of calculating the lowest non-empty cell location, but I don't know if that's possible. If not, I just want to be able to choose which data series to plot. Even though at the moment there's 8 series available under Chart -> Source Data, it will only plot series 1 in the pie chart. Hi, A pie chart will normally only display a single data series. It can be manipulated to disp...

change position of data
Hi My data set, has located on column x, for example: (x2:x570),i want to move this column to desired row number (x7644). I need to a macro or function, when run it, then at first, open the Box, and take me new row number, and finally, move data on column x to new row number (x7644:x8214). I have many file and little time. Thank's for any help. best regards open the Box??? Insert rows??? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "climate" <climate@discussions.microsoft.com> wrote in message news:8BE7545C-F280-...

macro
Hi, Need help on macro, pls. see if what I'm thinking is possible: I have 2 sheets in my file, the first sheet is where I input my data and I formatted sheet 2 for printing purposes. Let say I have these in sheet 1: Data 1 Date 2 Data 3 aaaa aaaaaa aaaaaaaaaaaaaa bbbb bbbbbb bbbbbbbbbbbbbb cccc cccccc cccccccccccccc Let say my cursor is on the first row of my data. I created a macro, with a corresponding shortcut key, so that the data on that row will be transferred to sheet 2 like: Data 1 aaa Date 2 aaaaaa Date 3 aaaaaaaaaaaaaa Now, I want to change the code (or mayb...