Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2009
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    Excel Sheet will dispaly in another Excel sheet cell by vlookup formula, the sheet will change by drop-down list from a range of cells


    can you give the solution for this.

    VVk

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Use the INDIRECT function in conjunction with your VLOOKUP:
    (Cell B1 contains sheet name, cell A2 contains value to look up)

    =VLOOKUP(A2,INDIRECT("'" & B1 & "'!A11000"),2,False)

    Please note the single quotes in the formula, they are needed in case a sheetname contains special characters like spaces.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Apr 2009
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi pieterse,

    Im not getting, can you explain detailed.

    in Data Sheet i need Employee target sheets (1,2,3) in Highlighted area, by changing the employee name in data sheet. Employee target sheet (1,2,3) will change automatically

    VVK
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    How can we decide which of the sheets (1, 2 or 3) should be used? I don't see how that would follow from the employee name.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If the sheetname is based ont he value you lookup in Data C4 you can add a name to pull in the region:

    Insert - name -define
    Names:
    Targets
    Refers to:
    =INDIRECT("'"&Data!$C$4&"'!$A$2:$F$16")
    [OK]

    Then you can

    insert - picture
    From file
    [It doesn't matter what picture just find on your drive]
    Select picture and enter in the formula bar:
    =Targets
    [enter]

    And the picture will change to the range A2:F16 in the sheet number from Data C4.

    If the sheet name comes from something else, you will have to indicate it to us...
    Steve

  6. #6
    New Lounger
    Join Date
    Apr 2009
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Steve,

    Good Morning,

    Im not getting can you help on this.

    VVK

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Does the attached help?

    I change sheet2 to 4 since you had no employees with a number of 2. It will work with the first 3 people who have IDs 1,3,4 and gthe appropriate range from the sheet will be grabbed.

    Steve
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •