I am using Excel XP. I have a spreadsheet with product codes, textual
description, price, and extension as the column labels and about 15 records.
I want to know if there is a way to (for example) enter the product code of
an item in a cell in another worksheet (or a different workbook) and have the
complete record of that item be duplicated.
ray

Take a look at VLOOKUP and use 5 of these in 5 consecutive cells, with each
one referencing the same cell (The first of the 5 which you will put the
Product code in) and returning the next piece of data.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------




"rayteach" wrote in message
...
I am using Excel XP. I have a spreadsheet with product codes, textual
description, price, and extension as the column labels and about 15
records.
I want to know if there is a way to (for example) enter the product code
of
an item in a cell in another worksheet (or a different workbook) and have
the
complete record of that item be duplicated.
ray

Name the range that contains your product codes by selecting all the cells,
then using InsertNamesDefine and typing in a brief name - let's call it
Products

Now, in the cells adjacent to the cell where you'll input the product code
use (assuming the product code is entered in A1)

=VLOOKUP($A$1, Products, 2, 0)

The 2 instructs Excel to get whatever is in the second column of the product
table, so change this number to 3, 4, etc as you copy it into other cells.
The 0 requires an exact match on product code and returns an #NA error if
there is no match



"rayteach" wrote:

I am using Excel XP. I have a spreadsheet with product codes, textual
description, price, and extension as the column labels and about 15 records.
I want to know if there is a way to (for example) enter the product code of
an item in a cell in another worksheet (or a different workbook) and have the
complete record of that item be duplicated.
ray

Your formula worked perfectly but, perhaps I am being thick headed, I still
need to copy the formula through each cell. I do not see how this is an
advance over copy and paste? There is no way to simply type the product code
into a cell and then have the rest of the record copied into the adjacent
cells?
--
ray


"Duke Carey" wrote:

Name the range that contains your product codes by selecting all the cells,
then using InsertNamesDefine and typing in a brief name - let's call it
Products

Now, in the cells adjacent to the cell where you'll input the product code
use (assuming the product code is entered in A1)

=VLOOKUP($A$1, Products, 2, 0)

The 2 instructs Excel to get whatever is in the second column of the product
table, so change this number to 3, 4, etc as you copy it into other cells.
The 0 requires an exact match on product code and returns an #NA error if
there is no match



"rayteach" wrote:

I am using Excel XP. I have a spreadsheet with product codes, textual
description, price, and extension as the column labels and about 15 records.
I want to know if there is a way to (for example) enter the product code of
an item in a cell in another worksheet (or a different workbook) and have the
complete record of that item be duplicated.
ray

Thank you for your prompt response. The post by Duke Carey was more help to
me as he guided me through the vlookup process in a way a beginner like me
can understand. I have also repsonded to his post as it did not automate the
process the way I am hoping.
--
ray


"Ken Wright" wrote:

Take a look at VLOOKUP and use 5 of these in 5 consecutive cells, with each
one referencing the same cell (The first of the 5 which you will put the
Product code in) and returning the next piece of data.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------




"rayteach" wrote in message
...
I am using Excel XP. I have a spreadsheet with product codes, textual
description, price, and extension as the column labels and about 15
records.
I want to know if there is a way to (for example) enter the product code
of
an item in a cell in another worksheet (or a different workbook) and have
the
complete record of that item be duplicated.
ray

I do not see how this is an
advance over copy and paste?

Using copy/paste, you'd have to do this every time you want to lookup some
data. Using the lookup formulas you do it once by just changing the lookup
value.

Also, the examples you've been given are rather basic. They can be modified
to give them much more capability.

For example, you don't need to enter 3 different formulas, one for each
column index number:

=VLOOKUP($A$1, Products, 2, 0)
=VLOOKUP($A$1, Products, 3, 0)
=VLOOKUP($A$1, Products, 4, 0)

You can write one formula and as you copy it across to other cells, have the
column index number automatically increment:

=VLOOKUP($A$1, Products, COLUMNS($A:B), 0)

Biff

"rayteach" wrote in message
...
Your formula worked perfectly but, perhaps I am being thick headed, I
still
need to copy the formula through each cell. I do not see how this is an
advance over copy and paste? There is no way to simply type the product
code
into a cell and then have the rest of the record copied into the adjacent
cells?
--
ray


"Duke Carey" wrote:

Name the range that contains your product codes by selecting all the
cells,
then using InsertNamesDefine and typing in a brief name - let's call it
Products

Now, in the cells adjacent to the cell where you'll input the product
code
use (assuming the product code is entered in A1)

=VLOOKUP($A$1, Products, 2, 0)

The 2 instructs Excel to get whatever is in the second column of the
product
table, so change this number to 3, 4, etc as you copy it into other
cells.
The 0 requires an exact match on product code and returns an #NA error if
there is no match



"rayteach" wrote:

I am using Excel XP. I have a spreadsheet with product codes, textual
description, price, and extension as the column labels and about 15
records.
I want to know if there is a way to (for example) enter the product
code of
an item in a cell in another worksheet (or a different workbook) and
have the
complete record of that item be duplicated.
ray

Thank you so much for your response. That makes it much easier to use the
VLOOKUP function.
--
ray


"Biff" wrote:

I do not see how this is an
advance over copy and paste?

Using copy/paste, you'd have to do this every time you want to lookup some
data. Using the lookup formulas you do it once by just changing the lookup
value.

Also, the examples you've been given are rather basic. They can be modified
to give them much more capability.

For example, you don't need to enter 3 different formulas, one for each
column index number:

=VLOOKUP($A$1, Products, 2, 0)
=VLOOKUP($A$1, Products, 3, 0)
=VLOOKUP($A$1, Products, 4, 0)

You can write one formula and as you copy it across to other cells, have the
column index number automatically increment:

=VLOOKUP($A$1, Products, COLUMNS($A:B), 0)

Biff

"rayteach" wrote in message
...
Your formula worked perfectly but, perhaps I am being thick headed, I
still
need to copy the formula through each cell. I do not see how this is an
advance over copy and paste? There is no way to simply type the product
code
into a cell and then have the rest of the record copied into the adjacent
cells?
--
ray


"Duke Carey" wrote:

Name the range that contains your product codes by selecting all the
cells,
then using InsertNamesDefine and typing in a brief name - let's call it
Products

Now, in the cells adjacent to the cell where you'll input the product
code
use (assuming the product code is entered in A1)

=VLOOKUP($A$1, Products, 2, 0)

The 2 instructs Excel to get whatever is in the second column of the
product
table, so change this number to 3, 4, etc as you copy it into other
cells.
The 0 requires an exact match on product code and returns an #NA error if
there is no match



"rayteach" wrote:

I am using Excel XP. I have a spreadsheet with product codes, textual
description, price, and extension as the column labels and about 15
records.
I want to know if there is a way to (for example) enter the product
code of
an item in a cell in another worksheet (or a different workbook) and
have the
complete record of that item be duplicated.
ray

You're welcome!

Biff

"rayteach" wrote in message
...
Thank you so much for your response. That makes it much easier to use the
VLOOKUP function.
--
ray


"Biff" wrote:

I do not see how this is an
advance over copy and paste?

Using copy/paste, you'd have to do this every time you want to lookup
some
data. Using the lookup formulas you do it once by just changing the
lookup
value.

Also, the examples you've been given are rather basic. They can be
modified
to give them much more capability.

For example, you don't need to enter 3 different formulas, one for each
column index number:

=VLOOKUP($A$1, Products, 2, 0)
=VLOOKUP($A$1, Products, 3, 0)
=VLOOKUP($A$1, Products, 4, 0)

You can write one formula and as you copy it across to other cells, have
the
column index number automatically increment:

=VLOOKUP($A$1, Products, COLUMNS($A:B), 0)

Biff

"rayteach" wrote in message
...
Your formula worked perfectly but, perhaps I am being thick headed, I
still
need to copy the formula through each cell. I do not see how this is an
advance over copy and paste? There is no way to simply type the product
code
into a cell and then have the rest of the record copied into the
adjacent
cells?
--
ray


"Duke Carey" wrote:

Name the range that contains your product codes by selecting all the
cells,
then using InsertNamesDefine and typing in a brief name - let's call
it
Products

Now, in the cells adjacent to the cell where you'll input the product
code
use (assuming the product code is entered in A1)

=VLOOKUP($A$1, Products, 2, 0)

The 2 instructs Excel to get whatever is in the second column of the
product
table, so change this number to 3, 4, etc as you copy it into other
cells.
The 0 requires an exact match on product code and returns an #NA error
if
there is no match



"rayteach" wrote:

I am using Excel XP. I have a spreadsheet with product codes,
textual
description, price, and extension as the column labels and about 15
records.
I want to know if there is a way to (for example) enter the product
code of
an item in a cell in another worksheet (or a different workbook) and
have the
complete record of that item be duplicated.
ray