+ Reply to Thread
Results 1 to 19 of 19

Simpler way to complete my IF formula?

  1. #1
    Registered User
    Join Date
    10-11-2012
    Location
    Lake Elsinore
    MS-Off Ver
    Excel 2010
    Posts
    46

    Red face Simpler way to complete my IF formula?

    Hello I'm working on a spreadsheet and using multiply IF statements within one cell - I'm hoping someone can direct me on a more simple way to do it. Below is an example of the IF statements using - I have to look up 200 different cell combinations:

    IF($A$3=1,"SELECT PRODUCT",IF($A$3=2,AU2,IF($A$3=3,AU3,IF($A$3=4,AU4,IF($A$3=5,AU5,IF($A$3=6,AU6,IF($A$3=7,AU7,IF($A$3=8,AU8,IF($A$3=9,AU9,IF($A$3=10,AU10,AN2))))))))))

    Thank you, in advance, for your assistance.

    Regards,

    Mary

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Simpler way to complete my IF formula?

    Try

    =IF($A$3=1,"Select Product",IFERROR(INDEX(AU2:AU10,$A$3-1),AN2))

  3. #3
    Registered User
    Join Date
    10-11-2012
    Location
    Lake Elsinore
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Simpler way to complete my IF formula?

    Hi Jonmo1,

    Thank you, so much for your response, but I don't think I asked my quetion correctly.

    What I'm actually trying to do is look @ a cell for a value and if the value matches, then go to look at a column of cells to create a drop down list of any cell that has a value great than zero. I'm going to try and place it in an example formula below to help give clarification:

    If Cell A1 =2, then go to Cells BA1.BA250, if cells BA1.BA250>0,Use data to create a dropdown list in Cell D2.

    Wow - this is what I need, I have no idea if it's even possible.

    Thank you, for your help!

    Regards,

  4. #4
    Registered User
    Join Date
    10-11-2012
    Location
    Lake Elsinore
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Simpler way to complete my IF formula?

    I'm trying to create a formula that I'm not sure is even possible and would appreciate any help that I can get. What I'm tring to do is look @ a cell for a value and if the value matches, then go to look at a column of cells to create a drop down list of any cell that has a value great than zero. I'm going to try and place it in an example formula below to help give clarification:

    If Cell A1 =2, then go to Cells BA1.BA250, if cells BA1.BA250>0,Use data to create a dropdown list in Cell D2
    If Cell A1 =3, then go to Cells BB1.BB250, if cells BB1.BB250>0,Use data to create a dropdown list in Cell D2

    I will need to check this for up to 250 in Cell A1

    If the formulas are possible above, then I will need to be able to select one of the items on the dropdown list (which I will use in another formula).

    Wow - this is what I need, I have no idea if it's even possible.

    Thank you, in advance, for your assistance.

    Regards,

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Simpler way to complete my IF formula?

    Here's the reply I tried to post at your other thread:

    Here's a small example of how to do this.

    A1 = drop down list with the selections of 1,2,3,4,5

    F1:J1 = column headers 1,2,3,4,5

    F2:J10 = numeric values

    Enter this array formula** in A10. This will extract the numeric values that are >0 from the column in the data range F2:J10 that matches the column number selected in the drop down in cell A1.

    =IFERROR(INDEX(F$2:J$10,SMALL(IF(INDEX(F$2:J$10,,A$1)>0,ROW(F$2:J$10)),ROWS(A$10:A10))-MIN(ROW(F$2:J$10))+1,A$1),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down to a number of cells that is equal to the number of cells in the columns of the data range. In this example the data range is 9 rows so you would copy the formula down for a total of 9 rows.

    Now, setup the drop down list in cell D2.

    As the source for the drop down list use this formula:

    =$A$10:INDEX($A$10:$A$18,MATCH(1E100,$A$10:$A$18))

    If you want to see this applied in a sample file let me know.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Simpler way to complete my IF formula?

    =choose($a$3,"select product","au2","au3","au4","au5","au6","au7","au8","au9","au10")
    Appreciate the help? CLICK *

  7. #7
    Registered User
    Join Date
    10-11-2012
    Location
    Lake Elsinore
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Simpler way to complete my IF formula?

    Hi Tony,

    I really appreciate your response and would love to see it applied in a sample file.

    Thank you!

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Simpler way to complete my IF formula?

    Here's the sample file:

    tcrjmom.xlsx

    I added some additional error trapping to the array formula.

  9. #9
    Registered User
    Join Date
    10-11-2012
    Location
    Lake Elsinore
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Simpler way to complete my IF formula?

    Thank you Tony!

    I'm working through the formula now, but I have a question. Will the formula still work if the values in cells f2:j10 are alpha not numerical? The formula that is written in the cells I'm seeking only equal zero if the cell it's looking to contains certain verbiage otherwise it returns the definition of another cell.

    Thank you again.

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Simpler way to complete my IF formula?

    Most of the formulas are based on the data being numbers as per your explanation.

    If that's not what you have then describe what your REAL data is like.

  11. #11
    Registered User
    Join Date
    10-11-2012
    Location
    Lake Elsinore
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Simpler way to complete my IF formula?

    Hi Tony,

    My real data is text unless the formula returns a zero.

    I tried to attached an example but I don't have rights to attach anything on this site.

    Regards,

  12. #12
    Registered User
    Join Date
    10-11-2012
    Location
    Lake Elsinore
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Simpler way to complete my IF formula?

    PRODUCT LIST Product name Product name Product name 2 3 4
    Item #1 (linked from other ws) =IF(ISNUMBER(SEARCH("XN",'Who Sells What Links'!C2)),0,VLOOKUP('Who Sells What Links'!C2,$AJ$2:$AK$50,2,FALSE)) =IF(ISNUMBER(SEARCH("XN",'Who Sells What Links'!Cd)),0,VLOOKUP('Who Sells What Links'!d2,$AJ$2:$AK$50,2,FALSE)) =IF(ISNUMBER(SEARCH("XN",'Who Sells What Links'!e2)),0,VLOOKUP('Who Sells What Links'!e2,$AJ$2:$AK$50,2,FALSE)) =b2 =c2 =d2
    Item #2 (linked from other ws) =IF(ISNUMBER(SEARCH("XN",'Who Sells What Links'!C3)),0,VLOOKUP('Who Sells What Links'!C3,$AJ$2:$AK$50,2,FALSE)) =IF(ISNUMBER(SEARCH("XN",'Who Sells What Links'!d3)),0,VLOOKUP('Who Sells What Links'!d3,$AJ$2:$AK$50,2,FALSE)) =IF(ISNUMBER(SEARCH("XN",'Who Sells What Links'!e3)),0,VLOOKUP('Who Sells What Links'!e3,$AJ$2:$AK$50,2,FALSE)) =b3 =c3 =d3
    Item #3 (linked from other ws) =IF(ISNUMBER(SEARCH("XN",'Who Sells What Links'!C4)),0,VLOOKUP('Who Sells What Links'!C4,$AJ$2:$AK$50,2,FALSE)) =IF(ISNUMBER(SEARCH("XN",'Who Sells What Links'!d4)),0,VLOOKUP('Who Sells What Links'!d4,$AJ$2:$AK$50,2,FALSE)) =IF(ISNUMBER(SEARCH("XN",'Who Sells What Links'!e4)),0,VLOOKUP('Who Sells What Links'!e4,$AJ$2:$AK$50,2,FALSE)) =b4 =c4 =d4
    Item # 4 (linked from other ws) =IF(ISNUMBER(SEARCH("XN",'Who Sells What Links'!C5)),0,VLOOKUP('Who Sells What Links'!C5,$AJ$2:$AK$50,2,FALSE)) =IF(ISNUMBER(SEARCH("XN",'Who Sells What Links'!d5)),0,VLOOKUP('Who Sells What Links'!d5,$AJ$2:$AK$50,2,FALSE)) =IF(ISNUMBER(SEARCH("XN",'Who Sells What Links'!e5)),0,VLOOKUP('Who Sells What Links'!e5,$AJ$2:$AK$50,2,FALSE)) =b5 =c5 =d5
    Item #5 (linked from other ws) =IF(ISNUMBER(SEARCH("XN",'Who Sells What Links'!C6)),0,VLOOKUP('Who Sells What Links'!C6,$AJ$2:$AK$50,2,FALSE)) =IF(ISNUMBER(SEARCH("XN",'Who Sells What Links'!d6)),0,VLOOKUP('Who Sells What Links'!d6,$AJ$2:$AK$50,2,FALSE)) =IF(ISNUMBER(SEARCH("XN",'Who Sells What Links'!e6)),0,VLOOKUP('Who Sells What Links'!e6,$AJ$2:$AK$50,2,FALSE)) =b6 =c6 =d6

    EX: I want to check another cell answer if it =2 then I want to use the list in column E (except for zero)
    if the cell equals 3 then I want to use the list in Col F and so on.

  13. #13
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Simpler way to complete my IF formula?

    You should be able to attach a file.

    Try these steps:

    Below the Quick Reply window click the Go Advanced button.

    Below the Reply to Thread window there are a bunch of options. Click the Manage Attachments button.

    Click the Add Files button.

    Click the Select Files button, select the file to attach

    Click Done

    It sounds like your file is pretty big. DO NOT POST THE ACTUAL FILE.

    Make up a SMALL sample file that demonstrates what you want to do.

    SMALL = about 5 columns and 20 rows worth of data.

  14. #14
    Registered User
    Join Date
    10-11-2012
    Location
    Lake Elsinore
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Simpler way to complete my IF formula?

    Hope this attachment gives some clarification; I think I may be making this way more difficult that necessary.

    Your help is greatly appreciated.

    Regards,
    Attached Files Attached Files

  15. #15
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Simpler way to complete my IF formula?

    Sorry, but I don't understand what's in that file!

  16. #16
    Registered User
    Join Date
    10-11-2012
    Location
    Lake Elsinore
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Simpler way to complete my IF formula?

    Ok updated the file - hope this helps give you more understanding.
    Attached Files Attached Files

  17. #17
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Simpler way to complete my IF formula?

    Hmmm...

    You said you have a drop down list in cell A1. There is no drop down list in cell A1.

    You have a combo box in column B and cell A2 is the linked cell to the combo box.

    Here is the instruction you have in the file:

    IF CELL A1 = 1,"SELECT PRODUCT GROUP", IF CELL A1=2 THEN I NEED A LIST OF CELLS R1:R9 (BUT I DO NOT WANT TO INCLUDE ZEROS), IF CELL A1=3 THEN I NEED A LIST OF CELLS S1:S9 AND SO ON
    All references to cell A1 are really meant to be references to cell A2.

    If cell A2 = 1 the selection in the combo box is SELECT PRODUCT GROUP. What should happen when that selection is made? You left that undefined.

    If cell A2 = 2 then you want a list of cells R1:R9 excluding 0s. OK, where do you want that list? What will that list be used for?

  18. #18
    Registered User
    Join Date
    10-11-2012
    Location
    Lake Elsinore
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Simpler way to complete my IF formula?

    Hi Tony,

    You are absolutely correct - it is referenced to cell a2 and it is a combo box - sorry :-( for the confusion.

    The combo box result triggers a "SELECT PRODUCT GROUP" message in other fields in the form that I'm creating.

    I would like to place the "LIST" - actually, what I want is list somewhere in the spreadsheet (really doesn't matter the location) so that I can use the list to create another combo box that the user can select from.

    Once the user selects the item in the newly created combo box, then it will trigger other formulas in the file to provide additional data.

    I'm building a tool for our department to use to simplify a very difficult and time consumning task that we do on a daily basis.

    Again, thank you, for your help.

  19. #19
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Simpler way to complete my IF formula?

    Data validation drop down lists are much easier to setup. Do you have to use combo boxes?

    I'm still having a hard time trying to understand what you want.

    I think what you're trying to do is setup dependent drop down lists but I can't make heads or tails out of the data in the sample file.



    EDIT: See if link helps any:

    http://contextures.com/xlDataVal02.html
    Last edited by Tony Valko; 04-16-2013 at 04:42 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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