software

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 :-)
------------------------------*------------------------------*----------------

quot;rayteachquot; gt; wrote in message
...
gt;I am using Excel XP. I have a spreadsheet with product codes, textual
gt; description, price, and extension as the column labels and about 15
gt; records.
gt; I want to know if there is a way to (for example) enter the product code
gt; of
gt; an item in a cell in another worksheet (or a different workbook) and have
gt; the
gt; complete record of that item be duplicated.
gt; ray
Name the range that contains your product codes by selecting all the cells,
then using Insertgt;Namesgt;Define 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
quot;rayteachquot; wrote:

gt; I am using Excel XP. I have a spreadsheet with product codes, textual
gt; description, price, and extension as the column labels and about 15 records.
gt; I want to know if there is a way to (for example) enter the product code of
gt; an item in a cell in another worksheet (or a different workbook) and have the
gt; complete record of that item be duplicated.
gt; 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?
--
rayquot;Duke Careyquot; wrote:

gt; Name the range that contains your product codes by selecting all the cells,
gt; then using Insertgt;Namesgt;Define and typing in a brief name - let's call it
gt; Products
gt;
gt; Now, in the cells adjacent to the cell where you'll input the product code
gt; use (assuming the product code is entered in A1)
gt;
gt; =VLOOKUP($A$1, Products, 2, 0)
gt;
gt; The 2 instructs Excel to get whatever is in the second column of the product
gt; table, so change this number to 3, 4, etc as you copy it into other cells.
gt; The 0 requires an exact match on product code and returns an #NA error if
gt; there is no match
gt;
gt;
gt;
gt; quot;rayteachquot; wrote:
gt;
gt; gt; I am using Excel XP. I have a spreadsheet with product codes, textual
gt; gt; description, price, and extension as the column labels and about 15 records.
gt; gt; I want to know if there is a way to (for example) enter the product code of
gt; gt; an item in a cell in another worksheet (or a different workbook) and have the
gt; gt; complete record of that item be duplicated.
gt; gt; 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.
--
rayquot;Ken Wrightquot; wrote:

gt; Take a look at VLOOKUP and use 5 of these in 5 consecutive cells, with each
gt; one referencing the same cell (The first of the 5 which you will put the
gt; Product code in) and returning the next piece of data.
gt;
gt; --
gt; Regards
gt; Ken....................... Microsoft MVP - Excel
gt; Sys Spec - Win XP Pro / XL 97/00/02/03
gt;
gt; ------------------------------Â*------------------------------Â*----------------
gt; It's easier to beg forgiveness than ask permission :-)
gt; ------------------------------Â*------------------------------Â*----------------
gt;
gt;
gt;
gt;
gt; quot;rayteachquot; gt; wrote in message
gt; ...
gt; gt;I am using Excel XP. I have a spreadsheet with product codes, textual
gt; gt; description, price, and extension as the column labels and about 15
gt; gt; records.
gt; gt; I want to know if there is a way to (for example) enter the product code
gt; gt; of
gt; gt; an item in a cell in another worksheet (or a different workbook) and have
gt; gt; the
gt; gt; complete record of that item be duplicated.
gt; gt; ray
gt;
gt;
gt;

gt; I do not see how this is an
gt; 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

quot;rayteachquot; gt; wrote in message
news
gt; Your formula worked perfectly but, perhaps I am being thick headed, I
gt; still
gt; need to copy the formula through each cell. I do not see how this is an
gt; advance over copy and paste? There is no way to simply type the product
gt; code
gt; into a cell and then have the rest of the record copied into the adjacent
gt; cells?
gt; --
gt; ray
gt;
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt;gt; Name the range that contains your product codes by selecting all the
gt;gt; cells,
gt;gt; then using Insertgt;Namesgt;Define and typing in a brief name - let's call it
gt;gt; Products
gt;gt;
gt;gt; Now, in the cells adjacent to the cell where you'll input the product
gt;gt; code
gt;gt; use (assuming the product code is entered in A1)
gt;gt;
gt;gt; =VLOOKUP($A$1, Products, 2, 0)
gt;gt;
gt;gt; The 2 instructs Excel to get whatever is in the second column of the
gt;gt; product
gt;gt; table, so change this number to 3, 4, etc as you copy it into other
gt;gt; cells.
gt;gt; The 0 requires an exact match on product code and returns an #NA error if
gt;gt; there is no match
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;rayteachquot; wrote:
gt;gt;
gt;gt; gt; I am using Excel XP. I have a spreadsheet with product codes, textual
gt;gt; gt; description, price, and extension as the column labels and about 15
gt;gt; gt; records.
gt;gt; gt; I want to know if there is a way to (for example) enter the product
gt;gt; gt; code of
gt;gt; gt; an item in a cell in another worksheet (or a different workbook) and
gt;gt; gt; have the
gt;gt; gt; complete record of that item be duplicated.
gt;gt; gt; ray
Thank you so much for your response. That makes it much easier to use the
VLOOKUP function.
--
rayquot;Biffquot; wrote:

gt; gt; I do not see how this is an
gt; gt; advance over copy and paste?
gt;
gt; Using copy/paste, you'd have to do this every time you want to lookup some
gt; data. Using the lookup formulas you do it once by just changing the lookup
gt; value.
gt;
gt; Also, the examples you've been given are rather basic. They can be modified
gt; to give them much more capability.
gt;
gt; For example, you don't need to enter 3 different formulas, one for each
gt; column index number:
gt;
gt; =VLOOKUP($A$1, Products, 2, 0)
gt; =VLOOKUP($A$1, Products, 3, 0)
gt; =VLOOKUP($A$1, Products, 4, 0)
gt;
gt; You can write one formula and as you copy it across to other cells, have the
gt; column index number automatically increment:
gt;
gt; =VLOOKUP($A$1, Products, COLUMNS($A:B), 0)
gt;
gt; Biff
gt;
gt; quot;rayteachquot; gt; wrote in message
gt; news
gt; gt; Your formula worked perfectly but, perhaps I am being thick headed, I
gt; gt; still
gt; gt; need to copy the formula through each cell. I do not see how this is an
gt; gt; advance over copy and paste? There is no way to simply type the product
gt; gt; code
gt; gt; into a cell and then have the rest of the record copied into the adjacent
gt; gt; cells?
gt; gt; --
gt; gt; ray
gt; gt;
gt; gt;
gt; gt; quot;Duke Careyquot; wrote:
gt; gt;
gt; gt;gt; Name the range that contains your product codes by selecting all the
gt; gt;gt; cells,
gt; gt;gt; then using Insertgt;Namesgt;Define and typing in a brief name - let's call it
gt; gt;gt; Products
gt; gt;gt;
gt; gt;gt; Now, in the cells adjacent to the cell where you'll input the product
gt; gt;gt; code
gt; gt;gt; use (assuming the product code is entered in A1)
gt; gt;gt;
gt; gt;gt; =VLOOKUP($A$1, Products, 2, 0)
gt; gt;gt;
gt; gt;gt; The 2 instructs Excel to get whatever is in the second column of the
gt; gt;gt; product
gt; gt;gt; table, so change this number to 3, 4, etc as you copy it into other
gt; gt;gt; cells.
gt; gt;gt; The 0 requires an exact match on product code and returns an #NA error if
gt; gt;gt; there is no match
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;rayteachquot; wrote:
gt; gt;gt;
gt; gt;gt; gt; I am using Excel XP. I have a spreadsheet with product codes, textual
gt; gt;gt; gt; description, price, and extension as the column labels and about 15
gt; gt;gt; gt; records.
gt; gt;gt; gt; I want to know if there is a way to (for example) enter the product
gt; gt;gt; gt; code of
gt; gt;gt; gt; an item in a cell in another worksheet (or a different workbook) and
gt; gt;gt; gt; have the
gt; gt;gt; gt; complete record of that item be duplicated.
gt; gt;gt; gt; ray
gt;
gt;
gt;

You're welcome!

Biff

quot;rayteachquot; gt; wrote in message
...
gt; Thank you so much for your response. That makes it much easier to use the
gt; VLOOKUP function.
gt; --
gt; ray
gt;
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; gt; I do not see how this is an
gt;gt; gt; advance over copy and paste?
gt;gt;
gt;gt; Using copy/paste, you'd have to do this every time you want to lookup
gt;gt; some
gt;gt; data. Using the lookup formulas you do it once by just changing the
gt;gt; lookup
gt;gt; value.
gt;gt;
gt;gt; Also, the examples you've been given are rather basic. They can be
gt;gt; modified
gt;gt; to give them much more capability.
gt;gt;
gt;gt; For example, you don't need to enter 3 different formulas, one for each
gt;gt; column index number:
gt;gt;
gt;gt; =VLOOKUP($A$1, Products, 2, 0)
gt;gt; =VLOOKUP($A$1, Products, 3, 0)
gt;gt; =VLOOKUP($A$1, Products, 4, 0)
gt;gt;
gt;gt; You can write one formula and as you copy it across to other cells, have
gt;gt; the
gt;gt; column index number automatically increment:
gt;gt;
gt;gt; =VLOOKUP($A$1, Products, COLUMNS($A:B), 0)
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;rayteachquot; gt; wrote in message
gt;gt; news
gt;gt; gt; Your formula worked perfectly but, perhaps I am being thick headed, I
gt;gt; gt; still
gt;gt; gt; need to copy the formula through each cell. I do not see how this is an
gt;gt; gt; advance over copy and paste? There is no way to simply type the product
gt;gt; gt; code
gt;gt; gt; into a cell and then have the rest of the record copied into the
gt;gt; gt; adjacent
gt;gt; gt; cells?
gt;gt; gt; --
gt;gt; gt; ray
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;Duke Careyquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Name the range that contains your product codes by selecting all the
gt;gt; gt;gt; cells,
gt;gt; gt;gt; then using Insertgt;Namesgt;Define and typing in a brief name - let's call
gt;gt; gt;gt; it
gt;gt; gt;gt; Products
gt;gt; gt;gt;
gt;gt; gt;gt; Now, in the cells adjacent to the cell where you'll input the product
gt;gt; gt;gt; code
gt;gt; gt;gt; use (assuming the product code is entered in A1)
gt;gt; gt;gt;
gt;gt; gt;gt; =VLOOKUP($A$1, Products, 2, 0)
gt;gt; gt;gt;
gt;gt; gt;gt; The 2 instructs Excel to get whatever is in the second column of the
gt;gt; gt;gt; product
gt;gt; gt;gt; table, so change this number to 3, 4, etc as you copy it into other
gt;gt; gt;gt; cells.
gt;gt; gt;gt; The 0 requires an exact match on product code and returns an #NA error
gt;gt; gt;gt; if
gt;gt; gt;gt; there is no match
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; quot;rayteachquot; wrote:
gt;gt; gt;gt;
gt;gt; gt;gt; gt; I am using Excel XP. I have a spreadsheet with product codes,
gt;gt; gt;gt; gt; textual
gt;gt; gt;gt; gt; description, price, and extension as the column labels and about 15
gt;gt; gt;gt; gt; records.
gt;gt; gt;gt; gt; I want to know if there is a way to (for example) enter the product
gt;gt; gt;gt; gt; code of
gt;gt; gt;gt; gt; an item in a cell in another worksheet (or a different workbook) and
gt;gt; gt;gt; gt; have the
gt;gt; gt;gt; gt; complete record of that item be duplicated.
gt;gt; gt;gt; gt; ray
gt;gt;
gt;gt;
gt;gt;