EXCEL LOOK UP 2

[maeling]

Hello,
I have two workbooks in excel.
workbook one is blank and will be filled in by an end user.

Workbook two is a data source of;
column 1 column 2
manufacturer model
sony vaio
hp prosario

What I am trying to do is - when an end users types Sony into workbook 1 column 1 it enters the data from workbook 2 column 2 into workbook 1 column 2. Does this make sense ? Is it achievable in Excel ?

 

[PCmad]

Sounds like the vlookup function. Do a Help on that it should explain what you need to do.

 

[maeling]

Wow quicj response, thanks,
I had located VLOOKUP in the help files before posting my thread however I didn't think that this was the right solution because the destination cell where the VLOOKUP formula would exist would be overtyped when the end user enters the manufacturer in workbook1 ?? I think

 

[SkipVought]

Hi,

if you type Sony in A1, then A2 would contain the VLOOKUP function

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[SkipVought]

...oops...

if you type Sony in A1, then B1 would contain the VLOOKUP function.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[maeling]

Thanks - I knew what you meant, I'll give it a try

 

[maeling]

Hello,
I can't seem to follow the help instrctions in excel or get the syntax right. Can you assit me based on my data above, using the Sony example ?

 

[xlbo]

what have you tried that doesn't work ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[maeling]

Ok, I have the two workbooks that I mentioned above. In the workbook1 I want to enter the manufacturer data into column A - cell A3 = Sony. I would like the data from workbook 2 cloumn B to appear in cell B3. I have entered the function =VLOOKUP('[ipaq machinery db.xls]Sheet1'!$B$4:$B$10) but get an error message;
YOU HAVE ENTERED TOO FEW ARGUMENTS.
??

 

[xlbo]

well you have missed out 2 parts of the formula - from XL help file:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

col_index_num is the column offset required - ie the number of columns to move across in the dataset to return the data you require

Range_Lookup is a boolean True / False to determine whether to get an exact match or not - False is exact match

All of this is in the help files



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KLewisBPM]

Your range has to include both colums, the data your looking up and the data you want returned

=vlookup(A1,[ipaq machinary db.xls]sheet1!$A$1:$B$10$,2,FALSE)

Assuming A1 is the cell you want the user to type "Sony" into

Kind Regards

Kelley Lewis

 

[maeling]

Geoff,
thanks for the pointers however the excel help is not as clear as your explination to someone that is new to this.
However my formula =VLOOKUP('[ipaq machinery db.xls]Sheet1'!$A$4:$B$10,2,FALSE) still doesn't seem to work. xcel now accepts the syntax without erroring however when I enter data into workbook 1 cell A3 and enter the formula into workbook 1 cell B3 I just get the #VALUE! appear in cell B3 ?

Kelley,
thanks for your suggestion. I have tried to enter your syntax in the destination cell however I get the following error message; "That name is not valid" The word IPAQ is highlighted in the formula.
I have changed the cell referencing from A1 to A3.

 

[xlbo]

you now do not have the 1st argument either:

=VLOOKUP(A3,'[ipaq machinery db.xls]Sheet1'!$A$4:$B$10,2,FALSE)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[maeling]

Thanks this now works, However I have a slight anomoly. Workbook2 has multiple entries for Sony i.e

Sony Laptop
Sony Telephone
Sony TV

Can anything be done to accomodate this ?

 

[xlbo]

not really - I mean which would you want to return and how do you think Excel is going to handle that ???

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KLewisBPM]

with vlookup it will return the first record it finds that matches your criteria.

Please explain why you would need more than one answer returned

Kind Regards

Kelley Lewis

 

[maeling]

I realize I should probably start a new thread but before I do can this not be achieved using combo boxes or list boxes in excel ? Using workbook two as the source data for the list or combo box ?

 

[maeling]

Kelley,
the end user that will be entering the data will only know the make of the equipment she\he will be surveying. I have used sony in my example but in reality it will be huge peices of industrial machinery. The end user will not know the function of the machine until she\he enters the make of the machine and the options are in front of them. It will then ensure consitency of machine type accross the board for all end users. Hope that makes sense ??

 

[KLewisBPM]

ok, I will look into it, however from experience it may be easier to use Access. if you have it and are familiar with it.

Kind Regards

Kelley Lewis

 

[maeling]

Thanks - yes I am more familair with access tahn I am with excel however for this solution it is not possible to use access for logistical reasons.