Range or Link ?

dantb · Apr 4th, 2002, 11:33 PM

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

dantb · Apr 4th, 2002, 11:42 PM

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)

dantb · Apr 4th, 2002, 11:45 PM

But still can only get one cell value, nead 12

Chris Davison · Apr 5th, 2002, 05:20 PM

Quote:

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,

Yogi Anand · Apr 5th, 2002, 05:31 PM

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

Yogi Anand · Apr 5th, 2002, 05:37 PM

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 ]

Chris Davison · Apr 5th, 2002, 06:09 PM

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 ]

Chris Davison · Apr 5th, 2002, 11:43 PM

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)

Apr 5th, 2002, 05:31 PM	#5
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,448	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

Apr 5th, 2002, 11:43 PM	#8
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	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 ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 4th, 2002, 11:33 PM	#1
dantb Join Date: Mar 2002 Location: Arkansas Posts: 356	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

Apr 4th, 2002, 11:42 PM	#2
dantb Join Date: Mar 2002 Location: Arkansas Posts: 356	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)

Apr 4th, 2002, 11:45 PM	#3
dantb Join Date: Mar 2002 Location: Arkansas Posts: 356	But still can only get one cell value, nead 12

Apr 5th, 2002, 05:37 PM	#6
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,448	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 ]

Apr 5th, 2002, 06:09 PM	#7
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	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 ]