Thanks:  0
Likes:  0

1. 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. 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. But still can only get one cell value, nead 12

4. 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)

(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,

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

6. 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. 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. 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)

## User Tag List

#### Posting Permissions

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