Getting prices from data Base to a work sheet in Excell

[Guest]

I have a prices list of Items coded from 1000 to 4000 , Column 1 Code, Column Item Detail , Column 3 Selling Price, This Worksheet is called Cost prices.
I want to transfer a row by code to a work sheet called workings in to a line , which is my cost page in the same Excell program,
At present I copy & paste from Price List to worksheet , I have tried to do this by codes but I am missing something.

Regards

 

[Arkash]

Hi

You could use VLOOKUP to get the relevant data. You will
only need to type the code to get the rest of the
information into your worksheet.

Eg:
Enter the code in cell A1 of your worksheet.

Typing the formula =VLOOPUP($A$1,Sheet1!
$A$1:$D$100,2,FALSE) in your worksheet will bring
the "Item Detail" of the particular code. Modify the
number within the formula, ie. 2 to 3 & 4 to get the rest
of the data.

Hope this helps.

Arkash

-----Original Message-----
I have a prices list of Items coded from 1000 to

4000 , Column 1 Code, Column Item Detail , Column 3
Selling Price, This Worksheet is called Cost prices.

I want to transfer a row by code to a work

sheet called workings in to a line , which is my cost
page in the same Excell program,

At present I copy & paste from Price List to

worksheet , I have tried to do this by codes but I am
missing something.

 

[Guest]

CLCThanks Arkash
For your reply
I have tried this I still can not get it to work I think I need a Brain Charge
In this Work book I have 4 Sheets, Sheet No 1 is called Schedule, Sheet No 2 Is called Workings, Sheet No 3 is Called Summary, Sheet No 4 is called Price List with The Code Numbers 1000 to 4000 with one code Per Row . I am tring to get information into Workings on Row 3 , Columns F,G,H & I By Typeing the Code say Code 1004 in a Search in F3 & information in G3,H3,H3,I3 use the information then get The next Code Item say 2340

Thanks Again for your Time & Help

 

[Max]

Think there's a typo in Arkash's suggestion
(should be VLOOKUP, not VLOOPUP .. )

but perhaps an illustration would also help, with
some specifics drawn from your layout as described ..

In Sheet: Price List
---------------------------
Assume you have in cols A to D
data in row2 down

Code...Field1......Field2.......Field3
1000 F1_Data1 F2_Data1 F3_Data1
1001 F1_Data2 F2_Data2 F3_Data2
1002 F1_Data3 F2_Data3 F3_Data3
etc

In Sheet: Workings
---------------------------
Suppose you have the table below in cols F to I,
data from *row3* down

Code...Field1......Field2.......Field3
1003
1001
etc

where you want to lookup the corresponding
Fields1, 2 and 3 for the list of Codes in col F

Put in G3:

=VLOOKUP($F3,'Price List'!$A2:$D4001,COLUMN(B1),0)

Copy G3 across to I3, then copy down
as many rows as you have codes listed in col F

And if you need to include
error-trapping for unmatched codes,

Put instead in G3:

=IF(ISNA(VLOOKUP($F3,'Price
List'!$A2:$D4001,COLUMN(B1),0)),"-",VLOOKUP($F3,'Price
List'!$A2:$D4001,COLUMN(B1),0))

Unmatched codes will return "-" instead of #NAs
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

CLCThanks Arkash
For your reply
I have tried this I still can not get it to work I think I need a Brain Charge
In this Work book I have 4 Sheets, Sheet No 1 is called Schedule, Sheet

No 2 Is called Workings, Sheet No 3 is Called Summary, Sheet No 4 is called
Price List with The Code Numbers 1000 to 4000 with one code Per Row . I am
tring to get information into Workings on Row 3 , Columns F,G,H & I By
Typeing the Code say Code 1004 in a Search in F3 & information in
G3,H3,H3,I3 use the information then get The next Code Item say 2340

Thanks Again for your Time & Help

Code, Column Item Detail , Column 3 Selling Price, This Worksheet is called
Cost prices.

 

[Guest]

Thank you Max for your information , I sure this will work as I have used lookup before and I was using Arkashs Typo error and we all no its easy to do make a typo
I thank you both for your information
Regards CLC37 ( colin)

 

[Max]

=VLOOKUP($F3,'Price List'!$A2:$D4001,COLUMN(B1),0)

Just a clarification that we're using "COLUMN(B1)"
for the col_index_num in the VLOOKUP
as a convenient incrementer (for copying across)

If we put in any cell in any col
: =COLUMN(B1) will resolve to "2"

And when we copy this cell across 1 col to the right,
it'll become : =COLUMN(C1) which resolves to "3"

And so on ..

 

[Max]

You're welcome, Colin !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

Thank you Max for your information , I sure this will work as I have

used lookup before and I was using Arkashs Typo error and we all no
its easy to do make a typo