+ Reply to Thread
Results 1 to 23 of 23

formula to copy cell data on logical comparison

  1. #1
    Registered User
    Join Date
    12-07-2014
    Location
    Qatar
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    10

    formula to copy cell data on logical comparison

    How can I replace a cell value(numbers), if it is less than another cell value that contains numbers?
    Can I have a formula?

    Imagine 3 cells A1, B1 and C1 contains the value A1= nothing, B1=23.56 C1=78.42
    If I enter 12.36 in A1 then the value of B1 should be replaced with A1 as A1 is less than the value in B1
    If I enter 98.34 in A1 then the value of C1 should be replaced with A1 as A1 is greater than the value in C1
    Hope my question is clear now?
    How can I do the above function automatically using formula or any other method

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: formula to copy cell data on logical comparison

    Formulas cannot work like this - a cell can either contain a value (23.56) or a Formula that is calculating a value [or values]. A single cell cannot really be both.

    Now that being said, the question itself is a little confusing - what is the purpose of this exercise? There may be a better approach
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  3. #3
    Registered User
    Join Date
    12-07-2014
    Location
    Qatar
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    10

    Re: formula to copy cell data on logical comparison

    The purpose of my exercise is to keep track of the highest and the lowest value entered in a cell.

  4. #4
    Registered User
    Join Date
    03-02-2017
    Location
    Taiwan
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: formula to copy cell data on logical comparison

    In A1 you put your data.
    Then, in B1 you put this formula =IF(AND(IF((A1<23.56),TRUE,FALSE),IF(A1="",FALSE,TRUE)),A1,23.56)
    Then, in C1 you put this formula =IF(AND(IF((A1>78.42),TRUE,FALSE),IF(A1="",FALSE,TRUE)),A1,78.42)

    Drag down your formulas in B1 and C1 until you don't have anymore data in column A

    If this is useful to you, please click on the "Add reputation" button just under my post. Thanks!

  5. #5
    Registered User
    Join Date
    12-07-2014
    Location
    Qatar
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    10

    Re: formula to copy cell data on logical comparison

    Hi Stevee,
    Thanks for your guidance.
    I entered the formula as you said in B1 and C1. But when i entered some value in A1 then B1 & C1 also result in 0
    My requirement is as follows
    When I enter any value in A1, if the entered value is less than the value in B1 then B1 should be replaced with value entered in A1
    Also if the entered value is greater than the value in C1 then C1 should be replaced with value entered in A1

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: formula to copy cell data on logical comparison

    As Speshul has already told you, you cannot have both a formula and a value in the same cell, so B1 and C1 cannot, at the same time, contain a value and check it against the contents of A1. You are going to need to find a different method of doing what you want. One of the VBA coders might be able to help.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Registered User
    Join Date
    03-02-2017
    Location
    Taiwan
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: formula to copy cell data on logical comparison

    This is what I get if I use my formulas (columnA is the inputted values). If you don't get that maybe it's because your excel is not in English and you don't use ".", then you need to modify the "." in the formulas also. You might also need to modify the "," in the formulas.



    2 2 78.42
    25.9 23.56 78.42
    0 0 78.42
    930 23.56 930
    5.78 5.78 78.42
    56 23.56 78.42
    Last edited by Stevee829; 04-13-2017 at 09:55 AM.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: formula to copy cell data on logical comparison

    How is this post relevant to this thread, please? Have you posted in the wrong thread?

  9. #9
    Registered User
    Join Date
    03-02-2017
    Location
    Taiwan
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: formula to copy cell data on logical comparison

    Quote Originally Posted by AliGW View Post
    How is this post relevant to this thread, please? Have you posted in the wrong thread?
    I'm just replying to thomasm87 who says my formulas don't work. They work!

  10. #10
    Registered User
    Join Date
    03-02-2017
    Location
    Taiwan
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: formula to copy cell data on logical comparison

    Quote Originally Posted by AliGW View Post
    As Speshul has already told you, you cannot have both a formula and a value in the same cell, so B1 and C1 cannot, at the same time, contain a value and check it against the contents of A1. You are going to need to find a different method of doing what you want. One of the VBA coders might be able to help.
    Not totally true, the IF function can check against a value

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: formula to copy cell data on logical comparison

    I don't think you have fully understood what the OP requires. With your suggestion, there can only be either the value programmed into the formula in B1 or C1, or the value in A1. What I believe the OP wants is for any value to be entered into B1 or C1 manually, then compared with A1, and that value changed if necessary. That can't be done UNLESS B1 and C1 are, in turn, referencing other cells that contain the manually entered numbers. There's no point in pursuing this now until the OP has returned and confirmed either way.

  12. #12
    Registered User
    Join Date
    03-02-2017
    Location
    Taiwan
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: formula to copy cell data on logical comparison

    Quote Originally Posted by AliGW View Post
    manually entered numbers
    Then OP just has to "manually enter the numbers" into the formulas instead I don't really see the "big deal" here.

    =IF(AND(IF((A1<23.56),TRUE,FALSE),IF(A1="",FALSE,TRUE)),A1,23.56)
    =IF(AND(IF((A1>78.42),TRUE,FALSE),IF(A1="",FALSE,TRUE)),A1,78.42)

  13. #13
    Forum Moderator - RIP 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
    29,464

    Re: formula to copy cell data on logical comparison

    As has been mentioned and assuming I understand your request you can't do that with functions. A cell at any one time can only be a formula or a value (text or numeric).
    Or to be strictly accurate you can if you are prepared to have circular references and set the 'Enable Iterative Calculations' to true.
    a
    However this is not recommended and you won't be able to reset the B & C numbers which will just diverge getting continually smaller & bigger.


    You need a sheet change macro for this which I've included in the attachment.

    The code is:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Richard Buttrey; 04-13-2017 at 07:25 PM.
    Richard Buttrey

    RIP - d. 06/10/2022

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

  14. #14
    Forum Moderator - RIP 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
    29,464

    Re: formula to copy cell data on logical comparison

    Quote Originally Posted by Stevee829 View Post
    Then OP just has to "manually enter the numbers" into the formulas instead I don't really see the "big deal" here.

    =IF(AND(IF((A1<23.56),TRUE,FALSE),IF(A1="",FALSE,TRUE)),A1,23.56)
    =IF(AND(IF((A1>78.42),TRUE,FALSE),IF(A1="",FALSE,TRUE)),A1,78.42)
    Hi Steve

    AIUI the OP wants B & C to change automatically so that they will always record something other than 23.56 and 78.42.

    No doubt Thomas will confirm when he gets out of bed.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: formula to copy cell data on logical comparison

    Quote Originally Posted by Stevee829 View Post
    =IF(AND(IF((A1<23.56),TRUE,FALSE),IF(A1="",FALSE,TRUE)),A1,23.56)
    =IF(AND(IF((A1>78.42),TRUE,FALSE),IF(A1="",FALSE,TRUE)),A1,78.42)
    These formulae, whether the correct solution for the OP or not, are unnecessary long. To do what you have interpreted to be correct, this is all that is needed:

    =IF(A1="","",IF(A1<23.56,A1,23.56))
    =IF(A1="","",IF(A1>78.42,A1,78.42))

  16. #16
    Registered User
    Join Date
    12-07-2014
    Location
    Qatar
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    10

    Re: formula to copy cell data on logical comparison

    Hi Richard,

    Thanks for the VBA code.
    Could you please help me with the procedure for adding the above VBA in my excel sheet

    Thomas

  17. #17
    Forum Moderator - RIP 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
    29,464

    Re: formula to copy cell data on logical comparison

    Quote Originally Posted by thomasm87 View Post
    Hi Richard,

    Thanks for the VBA code.
    Could you please help me with the procedure for adding the above VBA in my excel sheet

    Thomas
    Hello Thomas,

    There are various ways to get into the VBA, perhaps the easiest in this case is to right click the tab name and select 'View Code' which will open the VBA.
    Over on the right you'll see two drop down boxes which have defaulted to General & Selection Change.

    Click the General drop down and select Worksheet
    Click the SelectionChange drop down and choose the Change event. This will create the Sheet Change event procedure

    Please Login or Register  to view this content.
    Copy and paste the code I gave you into this procedure. Obviously don't copy the Sub and End Sub lines since you already have those.

    This macro will work for the sheet you started with. It's not clear from your OP whether you want this functionality on several or indeed All sheets. If so post back since we'll need to use a Workbook Sheet Change event.

  18. #18
    Registered User
    Join Date
    12-07-2014
    Location
    Qatar
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    10

    Re: formula to copy cell data on logical comparison

    Thank you very very much
    It is working as expected
    If I want the same code to work in all cell range of A,B & C
    What Shall I do?

  19. #19
    Forum Moderator - RIP 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
    29,464

    Re: formula to copy cell data on logical comparison

    Hi,

    Untested but try

    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    12-07-2014
    Location
    Qatar
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    10

    Re: formula to copy cell data on logical comparison

    Hi Richard,
    Thanks a million for your kind help
    It is indeed working perfect in all cell ranges of A, B & C as expected.

    Hope I can come back to you in case of any further help on VBA
    Also could you please suggest some good tutorial on VBA for beginners.

    Best Regards
    Thomas

  21. #21
    Forum Moderator - RIP 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
    29,464

    Re: formula to copy cell data on logical comparison

    Hi,

    My pleasure and glad to have helped.

    As to tutorials then the world is your oyster.

    Googling Excel VBA programmers guide will bring up shed loads of stuff including videos. I learned Excel VBA before the internet existed in its present form so can't really comment on how useful and practical they are.

    I learned from text books mainly these two

    https://www.amazon.co.uk/d/Books/Exc...grammers+guide .. I note there are more recent editions of this book although whether they contain much that's significantly different is a moot point since basic VBA hasn't changed much. No doubt they pick up on changes to the Excel App like the additions of Power Pivots, Slicers and various stuff.

    ...and

    https://www.amazon.co.uk/d/Books/Wri...ros+o%27reilly

  22. #22
    Registered User
    Join Date
    12-07-2014
    Location
    Qatar
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    10

    Re: formula to copy cell data on logical comparison

    Hi Richard,
    May I ask you for a new help in Excel?
    Suppose I have two excel workbooks having two columns A & B in each.
    I want to compare the value of A in one book with the value of A in the second book and if both are equal then replace value of B in second with value of B in first book.
    How do I do it?

    Regards
    Thomas

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: formula to copy cell data on logical comparison

    Please start a new thread for this new query. Thanks.

+ 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. Data Comparison and copy to other tab
    By stangz71 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 07-25-2017, 05:19 PM
  2. [SOLVED] Copy between soreadsheets based cell comparison
    By rfaris in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-09-2013, 03:02 PM
  3. [SOLVED] Logical Formula with comparison operator not behaving as expected
    By attal in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-26-2013, 02:13 PM
  4. Replies: 1
    Last Post: 12-04-2012, 01:59 AM
  5. [SOLVED] Logical Test comparison using cell color
    By chamuko in forum Excel General
    Replies: 2
    Last Post: 11-08-2005, 11:25 PM
  6. How do I copy a function for comparison but keep first cell value.
    By N'wester in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-22-2005, 12:06 AM
  7. [SOLVED] Copy data between 2 Excel for comparison
    By Boba in forum Excel General
    Replies: 1
    Last Post: 02-18-2005, 10:06 AM

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