+ Reply to Thread
Results 1 to 5 of 5

Can't get If statement to display contents of various ranges

  1. #1
    Registered User
    Join Date
    01-21-2012
    Location
    Arkansas, US
    MS-Off Ver
    Excel 2007
    Posts
    2

    Can't get If statement to display contents of various ranges

    I'm designing a spreadsheet-based, "choose your own adventure" adventure game.
    The first step is to choose a character from a drop-down box (A14). The contents of the drop-down box are listed in column A of sheet 2, with various statistics such as weight, height and hair color listed in columns B:G in rows adjacent to the name they go with.

    So here's my problem: how do I phrase a formula in sheet 1 so if the drop-down box hasany of the names from sheet 2(which it will), a cell in sheet 1 will display the statistic associated with that name? I made it work with a nested formula, but it took a long time to write, and I'll have to repeat it for every single cell with a similar goal in the spreadsheet. Which would be awfully labor intensive.

    My formula is: If(A14=sheet2.$A$1; sheet2.$B$1; If(A14= sheet2.$A$2$;sheet2.$B$2; If...etc, repeated for every name and every statistic.
    I'm trying to make something like this work: If(A14=sheet2.$A$1:$A$14; sheet2.$B$1:$B$14)

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Can't get If statement to display contents of various ranges

    Hi AngryPockets06,

    Welcome to the forum.
    Data from sheet 2 can be shown with less efforts .. no doubt, please post the sample file. Thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Can't get If statement to display contents of various ranges

    My guess is that you are looking to match the value in A14 and return the associated values from sheet2, thus you need a look up formula.

    If sheet2 contains a table, where
    first column of the table is the character name and all columns to the right are various statistics (B:G, as you noted)

    Then we would pass the selected value of A14 to a look up formula like so

    =VLOOKUP(A14,'Sheet2'!$A1:$G20,X,false)

    Where "X" is the column index for the statistic you want.
    So, if hair color was column-2 of the table we would have

    =VLOOKUP(A14,'Sheet2'!$A1:$G20,2,false)

    If weight was in column-3 of the table, then

    =VLOOKUP(A14,'Sheet2'!$A1:$G20,3,false)

    Does that help?
    Last edited by Palmetto; 01-24-2012 at 09:28 PM.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Can't get If statement to display contents of various ranges

    I put together a sample file that shows one method of selecting a character from a list and displays the traits associated with that character.
    AngryPockets06_CharPicker_RON_v1a.xlsx

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  5. #5
    Registered User
    Join Date
    01-21-2012
    Location
    Arkansas, US
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Can't get If statement to display contents of various ranges

    Everyone,

    Thanks so much! Your advice gives steam to an aspiring game designer.

    @DILIpandy: I was posting from my phone at the time, so I couldn't upload the file. Thanks for the effort, though.
    @Palmetto: That's it! Thanks a lot.
    @Ron: I appreciate the sample sheet. It's easy to get lost in the text, so an example is a good reference.

    AngryPockets06

+ 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