+ Reply to Thread
Results 1 to 23 of 23

Using cell references in IF logical test

  1. #1
    Registered User
    Join Date
    12-21-2012
    Location
    Hungary
    MS-Off Ver
    Excel 2019
    Posts
    56

    Using cell references in IF logical test

    Is it possible to use cell references in an IF function's logical test?

    Like:

    C1: M2=""
    D1: O2

    if(c1;d1;1)

    Because if I use it that way then the expression is used as a text ("M2=""") hence it doesn't work.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Using cell references in IF logical test

    Something like this......

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


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    12-21-2012
    Location
    Hungary
    MS-Off Ver
    Excel 2019
    Posts
    56

    Re: Using cell references in IF logical test

    I get the #ref error with indirect

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Using cell references in IF logical test

    I believe either C3 or D3 will be blank without valid cell reference. Please explain what you are trying to do?

  5. #5
    Registered User
    Join Date
    12-21-2012
    Location
    Hungary
    MS-Off Ver
    Excel 2019
    Posts
    56

    Re: Using cell references in IF logical test

    I'am trying to simplify an IF function for other users so they will not have to dig inside the IF function, just change the values outside.

    Something like this

    =IF(C1;D1;IF(C2;D2;IF(C3;D3;IF(C4;D4..and so on..)

    And all the expressions are listed in the C(logical test) and D(true) columns
    C1: M1=""
    D1: O1
    C2: LEFT(M2;1)="1"
    D2: O2
    etc..

    So the expressions and all the stuff which would normally be in the IF function are used as a reference and listed outside the function.

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Using cell references in IF logical test

    The way which you are trying to do is something going to be a cumbersome approach rather than just building it inside the IF function

  7. #7
    Registered User
    Join Date
    12-21-2012
    Location
    Hungary
    MS-Off Ver
    Excel 2019
    Posts
    56

    Re: Using cell references in IF logical test

    The users can't change the IF function they get lost in it (that's what they say). Do you have any other idea? or a different approach to solve this problem?

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    What do the users need to change in the formula?
    If posting code please use code tags, see here.

  9. #9
    Registered User
    Join Date
    12-21-2012
    Location
    Hungary
    MS-Off Ver
    Excel 2019
    Posts
    56

    Re: Using cell references in IF logical test

    basically whatever they want every condition should be flexible so they can change them

    Edit: The only thing that is constant is the IF function's structure

  10. #10
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Using cell references in IF logical test

    Thanks Norie for relieving me from this thread

    I was scared, since evaluating formula mentioned as text value in a cell OR conditions in the same way is not the easy one

  11. #11
    Registered User
    Join Date
    12-21-2012
    Location
    Hungary
    MS-Off Ver
    Excel 2019
    Posts
    56

    Re: Using cell references in IF logical test

    I uploaded a sample. It basically look like this
    default.xlsx

  12. #12
    Registered User
    Join Date
    12-21-2012
    Location
    Hungary
    MS-Off Ver
    Excel 2019
    Posts
    56

    Re: Using cell references in IF logical test

    anybody got any solutions or ideas?

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Using cell references in IF logical test

    I looked at your file and, sorry, it makes no sense to me. you will have to explain exactly what you are trying to do, and give a few examples of what you expect answers to look like, as well as how you arrived at them
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  14. #14
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Using cell references in IF logical test

    It's probably possible using one of the techniques described
    here:http://www.myonlinetraininghub.com/e...luate-function
    or
    here:http://www.pcmag.com/article2/0,2817,2212496,00.asp

    both would require macros enabled, and a change of your formula to something like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    to change your formula into a string either method could work on, the real question is , is it worth the hassle?
    if end users know enough to change the conditions, they should be able to do it in the formula, if not, you are going to be answering/fixing it all the time anyways..

    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  15. #15
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Using cell references in IF logical test

    Thanks to FDibbins & dredwolf for getting pitched IN for sharing your valuable suggestions

    @ dredwolf,

    Thanks for showing the light for the OP on the way the OP wanted to move on

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Using cell references in IF logical test

    sorry I couldnt help more, but at least (i hope) a solution was arrived at

  17. #17
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Using cell references in IF logical test

    I just remembered seeing something pretty similar in the forum was all

    @ Sixthsense
    Thanks for the kind words

    Edit-
    Still can not find that thread though

  18. #18
    Registered User
    Join Date
    12-21-2012
    Location
    Hungary
    MS-Off Ver
    Excel 2019
    Posts
    56

    Re: Using cell references in IF logical test

    Thank you for your help guys, I'll take a look at it and see if it works.

    @dredwolf
    I have to do it. I know it is nonsense but it has to be done.

  19. #19
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Using cell references in IF logical test

    Well, Good luck, and I hope it doesn't become too big a problem !

  20. #20
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Using cell references in IF logical test

    here is a sample of what I think you want
    Attached Files Attached Files
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  21. #21
    Registered User
    Join Date
    12-21-2012
    Location
    Hungary
    MS-Off Ver
    Excel 2019
    Posts
    56

    Re: Using cell references in IF logical test

    Quote Originally Posted by JosephP View Post
    here is a sample of what I think you want
    Thank you JosephP. I wanted to do the exact same thing. How could I do this on an other sheet? If the data is on an other sheet (B,M,Q,O column)
    So I want to drag it down and fill the S column (on Sheet2!) so it evaluates all rows.
    I couldn't upload it for some reason so I uploaded it on GoogleDocs:
    HTML Code: 

  22. #22
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Using cell references in IF logical test

    that will be ridiculously complex to set up and incredibly inefficient in use so I really can't recommend using it.

  23. #23
    Registered User
    Join Date
    12-21-2012
    Location
    Hungary
    MS-Off Ver
    Excel 2019
    Posts
    56

    Re: Using cell references in IF logical test

    Ok. Understood, thank for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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