Range or Link ?

dantb

Active Member
Joined
Mar 20, 2002
Messages
358
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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)
 
Upvote 0
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,
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top