If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula required for matching cells
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 |
#2
|
|||
|
|||
Formula required for matching cells
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 "daviebutton" wrote in message ... 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 |
#3
|
|||
|
|||
Formula required for matching cells
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 InsertNameDefine 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 --- "daviebutton" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|