# Pick diff pick list as per column value

1. ## 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...

2. ## 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. ## Re: Pick diff pick list as per column value

Not giving desired result

4. ## 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. ## Re: Pick diff pick list as per column value

Originally Posted by ROHAN999
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. ## 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. ## 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

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. ## 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. ## Re: Pick diff pick list as per column value

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. ## Re: Pick diff pick list as per column value

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

11. ## Re: Pick diff pick list as per column value

Did you copy that formula down column A?

Pete

12. ## Re: Pick diff pick list as per column value

No i will try and let you know

13. ## Re: Pick diff pick list as per column value

Is this by chance what you mean?

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

#### 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