+ Reply to Thread
Results 1 to 10 of 10

Defined Names within "IF" Functions

  1. #1
    Registered User
    Join Date
    10-16-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Angry Defined Names within "IF" Functions

    Book1.xlsxI am working on a spreadsheet for my boss, and I need some help.

    Attached is an example workbook that I have been playing with, much like the one I need to make, just a whole lot simpler.

    Cell B3 is a drop-down box with three choices (in this case seed). He wants to be able to pick a seed, then a separate drop-down box come down in C4 depending on his choice in B3. The function I have in is as follows:

    =IF(B3=DATA!A1,CORN,IF(B3=DATA!A2,COTTON,IF(B3=DATA!A3,SOYBEAN)))

    (The text in blue is the inserted defined name)

    I have tried =IF(B3="CORN",CORN,IF(B3="COTTON",COTTON,IF(B3="SOYBEAN",SOYBEAN))) as well.

    All I get is #VALUE!.

    Is this even possible to get a drop-down menu from another drop-down menu? Each seed has its own.


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

    Re: Defined Names within "IF" Functions

    You need to put that formula in Data Validation

    On the Data Tab, click Data Validation
    Select List
    Enter that formula in the Source Box

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    Re: Defined Names within "IF" Functions

    Hi and welcome to the forum

    You can do this with the INDIRECT() function, see the attached for an example on how to do it...
    Attached Files Attached Files
    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

  4. #4
    Registered User
    Join Date
    10-16-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Defined Names within "IF" Functions

    I can't find an example formula. I see how it works, but what does the formula look like?

    Thanks!

  5. #5
    Registered User
    Join Date
    10-16-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Defined Names within "IF" Functions

    That didn't work. @Jonmo1

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

    Re: Defined Names within "IF" Functions

    Quote Originally Posted by emilydsmith View Post
    That didn't work. @Jonmo1
    Sure it did, see Cell C4

    emilydsmithEF.xlsx

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    Re: Defined Names within "IF" Functions

    it is done through DV. click on the 2nd dropdown, and then go into the DV window, you will see the formula in there

  8. #8
    Registered User
    Join Date
    10-16-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Defined Names within "IF" Functions

    Thank you so much! I don't know how I was doing it differently but thank you!

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    Re: Defined Names within "IF" Functions

    you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

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

    Re: Defined Names within "IF" Functions

    You're welcome.

+ 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. replace all defined range names in the sheet that start with "Street" to "Road"
    By matrex in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 11-04-2016, 08:53 AM
  2. Excel Function that differentiates "male" from "female" names with 900+ names
    By doylehargrove in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-17-2012, 06:00 PM
  3. Trouble with "Excel 4.0 function stored in defined names" error
    By rkjudy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-20-2011, 01:54 PM
  4. Replies: 5
    Last Post: 06-26-2006, 09:23 PM
  5. User Defined Functions: Where do I go to to find the "code"
    By Frank@shell in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-20-2005, 10:06 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