Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Range or Link ?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Arkansas
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi all, What I am trying to do is, Link data to a excel workbook,from a different excel workbook. Some how I would like to make it if a cell has a value >1, vlookup a specified number that I typed in(In my current workbook),Have it look the number up in the external workbook, and return the 12 collums of data neaded on the specified row that it finds the number.

    Is there a way to do this without having to Run a Query and import all my data ?. Thanks Dan


  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Arkansas
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I Think I just answered my own question. Long day.
    =IF(C2>1,VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$D$22216,2,FALSE),0)

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Arkansas
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    But still can only get one cell value, nead 12

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-04 18:42, dantb wrote:
    I Think I just answered my own question. Long day.
    =IF(C2>1,VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$D$22216,2,FALSE),0)
    it's unclear exactly *which* 12 columns you need returning..... but assuming they are columns "B" through to "M" from your example above, then try this :

    =IF(C2>1,VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,2,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,3,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,4,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,5,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,6,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,7,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,8,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,9,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,10,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,11,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,12,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,13,FALSE),0)


    adjust accordingly...



    (I'm open to any suggestions as to how this can be shortened..... it seems silly repeating the whole VLOOKUP 12 times, but I couldn't figure out an array solution without any errors messages......
    thanks
    Chris)

    Anyhow,
    :: Pharma Z - Family drugstore ::

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Chris:
    We have a limit of nesting number of functions to 7 in a formula -- don't we?

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Chris:
    Sorry! you have not exhausted the limit of nesting the functions in your formula ... oversight on my part!

    [ This Message was edited by: Yogi Anand on 2002-04-05 12:38 ]

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I concatenated, thank you very much

    now go and stand in the corner hee hee

    any ideas on shortening, Yogi ? I just remembered the "-1 column" solution in VLOOKUP using =OFFSET ; could this be incorperated ?



    [ This Message was edited by: Chris Davison on 2002-04-05 13:10 ]

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    rethinking... I'm not sure if you want all 12 in one cell

    if not, copy this similar formula across your 12 columns :

    =IF($C$2>1,VLOOKUP($B$1,[Subject.xls]Sheet1!$A$2:$M$22216,column(b1),FALSE),0)

    :: Pharma Z - Family drugstore ::

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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