Formula required for matching cells

[daviebutton]

Hi

On sheet 2 I have a 13 digit code in Col A, the description in Col B and the
price in Col C, these will be constantly added to manually.

On sheet 1 I would like possibly a 'drop down list' of the 13 digit codes in
column A, then for Col B to auto fill the Description and Col C to fill the
price. Is this possible?

In other words sheet 2 comprises of the data for the ISBN numbers, plus
description plus price, which will over time be added to. On sheet 1, its the
orders taken, so if the ISBN is put into Col A, then col B and Col C are
filled from the data on sheet 2.

I hope this makes sense?

Thanks
Dave

 

[L. Howard Kittle]

On sheet 1 select two cells in the same row and while selected type in this
formula and the use CTRL + SHIFT + ENTER to commit.

=VLOOKUP(A1,Sheet2!A1:C30,{2,3},0)

The lookup ISBN number is in sheet 1, A1.
On sheet 2 column A is the ISBN numbers, column 2 is the Description and
column 3 is the Price.

If you alter the formula you will have to select the 2 cells with the
formulas, make your changes and Array Enter again, useing CTRL + SHIFT +
ENTER.

HTH
Regards,
Howard

 

[Max]

In Sheet2,
your source table is in cols A to C, col headers in row1,
with continuous data running in row2 down

Create a dynamic range for the DV droplist
via Insert>Name>Define for col A (ISBN), viz.:
Name: ISBN
Refers to: =OFFSET(Sheet2!$A$2,,,COUNTA(Sheet2!$A:$A)-1)

Then in Sheet1,
select col A, click Data > Validation,
Allow: List
Source: =ISBN
Click OK, and that should give you the ISBN droplist which will grow as the
source data in Sheet2 expands (you can easily test this out)

Then with the ISBN data selectable in A2 down,
you could place this in B2:
=IF($A2="","",VLOOKUP($A2,Sheet2!$A:$C,COLUMNS($A:A)+1,0))
Copy B2 to C2, fill down as far as required to return the source results
corresponding to the ISBN data selected in A2 down

Success? Celebrate it, hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik