+ Reply to Thread
Results 1 to 13 of 13

Pick diff pick list as per column value

  1. #1
    Forum Contributor
    Join Date
    03-11-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    726

    Pick diff pick list as per column value

    Sir

    i am attaching here with my sample sheet .in which there are 4 sheets -sheet1,2,3,4

    sheet2,3,4 have three list in their cell a1

    what i want is in my sheet1 if b1=2 then my cell a1 of sheet1 show list of sheet2,similarly if B1 of sheet1 is 3 then a1 of sheet1 show list of sheet3 and similarly for sheet4

    plz elaborate how can i do this;;;;;

    help me by formula/vba etc...
    Attached Files Attached Files
    Last edited by ROHAN999; 10-29-2013 at 03:32 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Pick diff pick list as per column value

    Put this formula in A1 of Sheet1:

    =IF(OR(B1<2,B1>4),"",INDIRECT("'Sheet"&INT(B1)&"'!A1"))

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    03-11-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    726

    Re: Pick diff pick list as per column value

    Not giving desired result

  4. #4
    Forum Contributor
    Join Date
    03-11-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    726

    Re: Pick diff pick list as per column value

    If i rename my sheetS then if column b1 of sheet1 denotes sheet name then what will be the formula

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Pick diff pick list as per column value

    Quote Originally Posted by ROHAN999 View Post
    Not giving desired result
    What is the desired result, then? Do you want to see the list from column C of the appropriate sheet?

    If i rename my sheetS then if column b1 of sheet1 denotes sheet name then what will be the formula
    Tell me what names you are going to use - as these are hard-coded into the formula, I need to know what they are so that I can show you how to do it.

    Pete

  6. #6
    Forum Contributor
    Join Date
    03-11-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    726

    Re: Pick diff pick list as per column value

    Like i will rename my sheet2,sheet3 etc as india ,australia etc and the same i will give in cell b2 of sheet1

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Pick diff pick list as per column value

    With the actual name of the sheet in B1, you can do it this way:

    =IF(B1="","",INDIRECT("'"&B1&"'!A1"))

    Hope this helps.

    Pete

    If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Also, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  8. #8
    Forum Contributor
    Join Date
    03-11-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    726

    Re: Pick diff pick list as per column value

    Sir

    i think i was unable to explain the req

    actually what i wanted was ...if i put sheet2 in b1 then dropdown list of sheet2 in cell a1 should appear

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Pick diff pick list as per column value

    I did ask that in Post #5, but you didn't answer.

    Put this in A1 of Sheet1:

    =IF($B$1="","",INDEX(INDIRECT("'"&$B$1&"'!C:C"),ROWS($1:1)))

    and copy into A2:A3. Then enter your sheet name in B1.

    Hope this helps.

    Pete

  10. #10
    Forum Contributor
    Join Date
    03-11-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    726

    Re: Pick diff pick list as per column value

    Its still give just one value.....instead of dropdown list of that sheet

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Pick diff pick list as per column value

    Did you copy that formula down column A?

    Pete

  12. #12
    Forum Contributor
    Join Date
    03-11-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    726

    Re: Pick diff pick list as per column value

    No i will try and let you know

  13. #13
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Pick diff pick list as per column value

    Is this by chance what you mean?
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ 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. Create userform that allows user to pick a color, then have macro reference the color pick
    By submariner18 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-21-2013, 05:04 PM
  2. A pick list that defines another pick list
    By aaanenson in forum Excel General
    Replies: 1
    Last Post: 09-21-2012, 10:48 AM
  3. Excel 2007 : Pick-List pick fills in another cell
    By hpasso217 in forum Excel General
    Replies: 0
    Last Post: 02-28-2011, 05:38 PM
  4. pick from a list of dates and bring over next column
    By patd7260 in forum Excel General
    Replies: 6
    Last Post: 01-26-2010, 12:36 PM
  5. Replies: 1
    Last Post: 06-27-2005, 07:05 AM

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