+ Reply to Thread
Results 1 to 15 of 15

Transfer funds from one cell to another cell

  1. #1
    Registered User
    Join Date
    12-28-2018
    Location
    Bristol, England
    MS-Off Ver
    Mac 2017
    Posts
    8

    Transfer funds from one cell to another cell

    Im looking for a formula that will:

    1. look to see if the value is + or -
    2. if positive transfer the positive amount to another cell & if negative do nothing



    ex:

    Cell U19 = 4.27
    Cell U20 = 10
    Cell U21 = -15

    the formula should transfer 4.27 from cell U19 cell to cell R40
    the formula should transfer 10 from cell U20 cell to cell R40
    the formula should not affect cell U21 or cell R40

    Cell R40 should now total 14.27
    Cell U19 should be 0
    Cell U20 should be 0
    Cell U21 should still be -15
    Last edited by johnp19922011; 12-28-2018 at 06:30 PM.

  2. #2
    Registered User
    Join Date
    12-28-2018
    Location
    Oregon, USA
    MS-Off Ver
    2017
    Posts
    13

    Re: Transfer funds from one cell to another cell

    =if(U19>0,U19+R40,U19)

    What that's doing is checking to see if U19 is greater than 0, and if it is to add it to R40, and if it is not, to stay the same. I am assuming you would like the automatic calculation to occur within cell U19, but that's not going to be possible because it's a datapoint. You can manipulate datapoints with additional cells, but not with the same cell. Is there a larger function to these datapoints?

  3. #3
    Registered User
    Join Date
    12-28-2018
    Location
    Bristol, England
    MS-Off Ver
    Mac 2017
    Posts
    8

    Re: Transfer funds from one cell to another cell

    this new formula would be added to an existing formula as listed below

    =SUM(V19+W19+X19)

    So should the new formula be

    =SUM(V19+W19+X19)+if((V19+W19+X19)>0,U19+R40,U19)

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,977

    Re: Transfer funds from one cell to another cell

    You can't do this with a formula since a formula can't do something to another cell other than affect its value if the other cell refers to it.

    The R40 formula is
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    But you;ll need a VBA macro to change he values of U19:U21 if they're >0
    You could of course have another range say V19:V21 with V19 copied to V20:V21

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Registered User
    Join Date
    12-28-2018
    Location
    Bristol, England
    MS-Off Ver
    Mac 2017
    Posts
    8

    Re: Transfer funds from one cell to another cell

    [QUOTE=Sean M.;5034628]to be clear, I don't want U19 to add R40 if U19 is positive... I want it to transfer the value from U19 to R40, leaving U19 at a value of 0.

    The formula you gave looks like it will add U19 and R40 together inside the U19 cell

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,977

    Re: Transfer funds from one cell to another cell

    [QUOTE=johnp19922011;5034633]
    Quote Originally Posted by Sean M. View Post
    to be clear, I don't want U19 to add R40 if U19 is positive... I want it to transfer the value from U19 to R40, leaving U19 at a value of 0.

    The formula you gave looks like it will add U19 and R40 together inside the U19 cell
    To whom is this addressed?

    If nothing you've been given helps I suggest you upload a workbook and show a before and after position

  7. #7
    Registered User
    Join Date
    12-28-2018
    Location
    Oregon, USA
    MS-Off Ver
    2017
    Posts
    13

    Re: Transfer funds from one cell to another cell

    [QUOTE=johnp19922011;5034633]
    Quote Originally Posted by Sean M. View Post
    to be clear, I don't want U19 to add R40 if U19 is positive... I want it to transfer the value from U19 to R40, leaving U19 at a value of 0.

    The formula you gave looks like it will add U19 and R40 together inside the U19 cell
    I see what you mean. Look at it this way. You will be utilizing 4 different cells for your need. U19 is a static number, it cannot change. I know you expressed desire for it to update, but if it's a datapoint, it can't by nature of excel. You are going to have a using cell, that will benefit from whatever the outcome of the function is, You will have a second cell that will be affected by your function as well, and you will have a cell that actually creates the function. IE: A1 is using U19 to make a decision. U19 is a static number, V19 has the formula to affect R40, R40 is another static number as well. Change A1 to use V19 for the result. V19 is going to have my original formula in it, and R40 is another affected cell that can or cannot combine with U19.
    Last edited by Sean M.; 12-28-2018 at 06:52 PM.

  8. #8
    Registered User
    Join Date
    12-28-2018
    Location
    Bristol, England
    MS-Off Ver
    Mac 2017
    Posts
    8

    Re: Transfer funds from one cell to another cell

    Quote Originally Posted by Richard Buttrey View Post
    You can't do this with a formula since a formula can't do something to another cell other than affect its value if the other cell refers to it.

    The R40 formula is
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    But you;ll need a VBA macro to change he values of U19:U21 if they're >0
    You could of course have another range say V19:V21 with V19 copied to V20:V21

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The first thing you said gave me the idea to make a formula have Cell U19 change if its more than 0 and also make R40 add the positive value that would be in U19. so basically two formulas, one for each cell.


    however, this is now what I have in cell r40:

    IF(SUM(V12:X12)>0,SUM(V12:X12),0)+IF(SUM(V13:X13)>0,SUM(V13:X13),0)+IF(SUM(V14:X14)>0,SUM(V14:X14),0)+IF(SUM(V15:X15)>0,SUM(V15:X15),0)+IF(SUM(V16:X16)>0,SUM(V16:X16),0)

    It works, but I have to do this about 35 times and that will make this formula VERY long, is there a way to shorten this formula?

  9. #9
    Registered User
    Join Date
    12-28-2018
    Location
    Oregon, USA
    MS-Off Ver
    2017
    Posts
    13

    Re: Transfer funds from one cell to another cell

    Dont be afraid to use more than 1 cell. you can put all of that in different cells and then have a =sum(A1:A50) after you copy down the basic =if(sum(v12:x12)>0,sum(v12:x12),0

  10. #10
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,977

    Re: Transfer funds from one cell to another cell

    See post #6. Please upload the workbook

  11. #11
    Registered User
    Join Date
    12-28-2018
    Location
    Bristol, England
    MS-Off Ver
    Mac 2017
    Posts
    8

    Re: Transfer funds from one cell to another cell

    Quote Originally Posted by Richard Buttrey View Post
    See post #6. Please upload the workbook
    how do I upload the worksheet?

  12. #12
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,977

    Re: Transfer funds from one cell to another cell

    The paperclip icon isn't working.

    Click the Go Advanced button at the bottom right of your post. Then look underneath the Posting window for 'Manage Attachments" and take it from there.

  13. #13
    Registered User
    Join Date
    12-28-2018
    Location
    Bristol, England
    MS-Off Ver
    Mac 2017
    Posts
    8

    Re: Transfer funds from one cell to another cell

    Quote Originally Posted by Richard Buttrey View Post
    See post #6. Please upload the workbook
    In this worksheet the goal will be to move the positive value from U13 to R40 and do nothing else to the rest of the U column, but apply the same formula.
    Attached Files Attached Files

  14. #14
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,977

    Re: Transfer funds from one cell to another cell

    Quote Originally Posted by johnp19922011 View Post
    In this worksheet the goal will be to move the positive value from U13 to R40 and do nothing else to the rest of the U column, but apply the same formula.
    Now I'm really confused. How is U13 related to R40?

  15. #15
    Registered User
    Join Date
    12-28-2018
    Location
    Bristol, England
    MS-Off Ver
    Mac 2017
    Posts
    8

    Re: Transfer funds from one cell to another cell

    Quote Originally Posted by Richard Buttrey View Post
    Now I'm really confused. How is U13 related to R40?
    U13 has a positive budget that is messing up the percentages on the right. R40 is labeled as a "savings" line, and I would like any excess money listed in the U column to be transferred to R40.

    The only thing I have found that is a possible work around is by making the formula in cell U13 be
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This formula would than be copied to cells U12-U47

    and then in cell R40 I have the formula reading
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    243.70 is the baseline budget, the rest would then be adding any positive values. The problem is, if I were to continue this formula, it would be long and endless.

    so, the updated question is, is there a way to shorten this formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Money formula help!
    By bmoore1118 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-28-2017, 01:06 PM
  2. calculating money formula
    By Learning ExL in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 04-17-2015, 09:28 AM
  3. [SOLVED] Formula To Split The Money
    By jackgan in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-16-2014, 10:03 AM
  4. [SOLVED] Very complex formula dealing with many variables
    By BeachRock in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-28-2013, 04:59 PM
  5. Formula to calculate money due
    By 1.zer0 in forum Excel General
    Replies: 4
    Last Post: 01-21-2011, 05:23 AM
  6. Need formula dealing with time, hours & minutes
    By scarames in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-01-2010, 02:15 PM
  7. Pls help with date and money formula!
    By gingermatti in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-29-2009, 05:42 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1