Experienced in Excel? Part 2

Collapse

Recommended Videos

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • 500bloc
    MVP
    • May 2003
    • 1279

    #1

    Experienced in Excel? Part 2

    One of the reasons of starting a new thread is because the other one I started had a small font that was too hard to read on my cpu for some reason.


    But my problem in Excel is that I would like to make a selection list that is determined on the the contents in cell (G3).

    I've found out how to make the selection list come up with the tab on the right side by going through Data, then Validity. Again, the thing I am trying to do is make the selection list only show "certain" contents that depend what I have written in the adjacent cell.

    For instance I would like to write PF in cell (F3), when that comes up I would like certain words to be available for that word in cell (G3), not all the words or terms just what I dictate.


    If it helps I can email the file or post with megaupload


    I appreciate any help given.
  • p_rushing
    Hall Of Fame
    • Feb 2004
    • 14514

    #2
    Re: Experienced in Excel? Part 2

    You need to use a VLOOKUP, create it in anpther tab, it would have in column A all the thing you will type in F3 and in column B all the things you want to appear in G3.

    Then in G3, you have some list formula, just replace the list part with the VLOOKUP formula.

    Comment

    • 500bloc
      MVP
      • May 2003
      • 1279

      #3
      Re: Experienced in Excel? Part 2

      Originally posted by p_rushing
      You need to use a VLOOKUP, create it in anpther tab, it would have in column A all the thing you will type in F3 and in column B all the things you want to appear in G3.

      Then in G3, you have some list formula, just replace the list part with the VLOOKUP formula.
      I've tried this with moderate success. Out of 6 possible items on the list, its only showing me 1, and thats usually the first item on the list.
      I'm trying to incorporate all possible 6, but that is where I'm having trouble at. I'm using the VLOOKUP formula but not everything is showing, it looks as if I'm halfway where I need to be. Do u know where this problem could be from?

      Comment

      • p_rushing
        Hall Of Fame
        • Feb 2004
        • 14514

        #4
        Re: Experienced in Excel? Part 2

        Use $ in you formula maybe. If the formula is changing, you need to do something like $A$1 in you vlookup to give the formula an absolute range so it doesn't move.

        Comment

        • 500bloc
          MVP
          • May 2003
          • 1279

          #5
          Re: Experienced in Excel? Part 2

          Originally posted by p_rushing
          Use $ in you formula maybe. If the formula is changing, you need to do something like $A$1 in you vlookup to give the formula an absolute range so it doesn't move.
          I'm using the $A$1, my problem is it doesn't move outside of the 1 word thats being found out of the 6. Its always the first word at the top of the list also, the other 5 below it never get read. Thats what i'm trying to rectify.

          Comment

          • Trevytrev11
            MVP
            • Nov 2006
            • 3259

            #6
            Re: Experienced in Excel? Part 2

            So if you write PF in the cell, you want a certain drop down list to become available of which to choose from in the other cell and if you write something else, like PG, you want a different drop down list, with different choices to come up?


            Hmmm, interesting.

            Not something I've ever tried. I'm assuming it will always return the first value and not give you a list to choose from.
            Last edited by Trevytrev11; 10-18-2009, 12:14 AM.

            Comment

            • 500bloc
              MVP
              • May 2003
              • 1279

              #7
              Re: Experienced in Excel? Part 2

              Originally posted by Trevytrev11
              So if you write PF in the cell, you want a certain drop down list to become available of which to choose from and if you write something else, like PG, you want a different drop down list, with different choices to come up?


              Hmmm, interesting.

              Thats correct, i'm using this for NBA 2K10.

              What i'm trying to do is if someone is a PG, then 6 choices come up, if they're a PF, then a different 6 come up. All from the drop down list of course.

              I'm sure it can be done. At least it sounds possible. lol

              Comment

              • p_rushing
                Hall Of Fame
                • Feb 2004
                • 14514

                #8
                Re: Experienced in Excel? Part 2

                I have this, I found it and kept it, which I think is what you want to do. I. A2-A6 is the data you are matching, B2-B6 is what you want to appear. A11 is where you are pasting this code. You need to put that into a drop down box.

                <meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="PowerPoint.Slide"><meta name="Generator" content="Microsoft PowerPoint 12">
                =INDEX(A$2:A$6,MATCH(SMALL($B$2:$B$6+ROW($B$2:$B$6 )/10^10,ROWS(A$11:A11)),$B$2:$B$6+ROW($B$2:$B$6)/10^10,0))
                …confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

                Comment

                Working...