Formula, Macro, or VBA code to find and insert
 

I have two worksheets in a workbook. On Worksheet 1, users insert the name of
an item, which is unique. Each item is one of approximately 2000 items in
Worksheet 2, and each item in Worksheet 2 has about 25 attributes. Each
attribute occupies a cell in a row, where the item name is in the first
column of the row.

For example, users might enter the following in Worksheet 1:

Item Name
aaaa
mmmm
ffff

Worksheet 2 has all items and attributes, such as:

Item Name Attribute 1 Attribute 2
etc.
aaaa eudn 1285
bbbb iemg 9583
cccc vobl 5820
etc.

I'd like to find three functions, the first of which would -- when the user
enters an Item Name in Worksheet 1 -- automatically copy a particular
attribute of that Item (say, Attribute 2) from Worksheet 2, and insert it in
a defined destination cell in Worksheet 1 (say, the cell to the right of the
Item just entered by the user).

The second function would -- when the user enters the Item in Worksheet 1 --
automatically copy a set of attributes from that Item (say, Attributes 3, 7,
and 11) from Worksheet 2 and insert them in a defined set of destination
cells in the same row as the Item just entered in Worksheet 1.

The third function would -- again, when the user enters the Item in
Worksheet 1 -- automatically copy all attributes from that Item (in their
current order) from Worksheet 2 and insert them in the same order in the same
row as the Item just entered in Worksheet 1.

I could find, copy, and paste; but would rather automate this process --
preferably using Functions or Macros (which I can only record, since I have
no VBA skills), or VBA code that I could insert in a Macro if someone were
willing to share that.

In the future, we may decide to convert the Worksheet 2 into an Access
table, so (although this is an Excel discussion group) any insights you may
have on how to do this in Access (that is, having users input Item Names in
an Excel worksheet and automatically extract the attributes from an Access
database and insert them into the Excel worksheet) would also be appreciated.

Thanks very much!

--
Keith

Formula, Macro, or VBA code to find and insert
 

Take a look at VLOOKUP() help

If this post helps click Yes
---------------
Jacob Skaria


"Keith" wrote:

I have two worksheets in a workbook. On Worksheet 1, users insert the name of
an item, which is unique. Each item is one of approximately 2000 items in
Worksheet 2, and each item in Worksheet 2 has about 25 attributes. Each
attribute occupies a cell in a row, where the item name is in the first
column of the row.

For example, users might enter the following in Worksheet 1:

Item Name
aaaa
mmmm
ffff

Worksheet 2 has all items and attributes, such as:

Item Name Attribute 1 Attribute 2
etc.
aaaa eudn 1285
bbbb iemg 9583
cccc vobl 5820
etc.

I'd like to find three functions, the first of which would -- when the user
enters an Item Name in Worksheet 1 -- automatically copy a particular
attribute of that Item (say, Attribute 2) from Worksheet 2, and insert it in
a defined destination cell in Worksheet 1 (say, the cell to the right of the
Item just entered by the user).

The second function would -- when the user enters the Item in Worksheet 1 --
automatically copy a set of attributes from that Item (say, Attributes 3, 7,
and 11) from Worksheet 2 and insert them in a defined set of destination
cells in the same row as the Item just entered in Worksheet 1.

The third function would -- again, when the user enters the Item in
Worksheet 1 -- automatically copy all attributes from that Item (in their
current order) from Worksheet 2 and insert them in the same order in the same
row as the Item just entered in Worksheet 1.

I could find, copy, and paste; but would rather automate this process --
preferably using Functions or Macros (which I can only record, since I have
no VBA skills), or VBA code that I could insert in a Macro if someone were
willing to share that.

In the future, we may decide to convert the Worksheet 2 into an Access
table, so (although this is an Excel discussion group) any insights you may
have on how to do this in Access (that is, having users input Item Names in
an Excel worksheet and automatically extract the attributes from an Access
database and insert them into the Excel worksheet) would also be appreciated.

Thanks very much!

--
Keith

Formula, Macro, or VBA code to find and insert
 

All of your functions can be accomplished using the VLOOKUP function. Basic
structure will look like:
=VLOOKUP($A2,Sheet2!$A:$Z,column_index,FALSE)

column_index indicates the column from your table that you want the return
value to come from. Thus, if wanting attribute #2, which is in column C, the
column_index number would be 3. You can either set these manually, or if you
want a straight "copy" could do:

=VLOOKUP($A2,Sheet2!$A:$Z,COLUMN(B2),FALSE)

because the cell reference in the COLUMN function will "shift" when you copy
this cell to the right, it will then in turn change the column_index value.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Keith" wrote:

I have two worksheets in a workbook. On Worksheet 1, users insert the name of
an item, which is unique. Each item is one of approximately 2000 items in
Worksheet 2, and each item in Worksheet 2 has about 25 attributes. Each
attribute occupies a cell in a row, where the item name is in the first
column of the row.

For example, users might enter the following in Worksheet 1:

Item Name
aaaa
mmmm
ffff

Worksheet 2 has all items and attributes, such as:

Item Name Attribute 1 Attribute 2
etc.
aaaa eudn 1285
bbbb iemg 9583
cccc vobl 5820
etc.

I'd like to find three functions, the first of which would -- when the user
enters an Item Name in Worksheet 1 -- automatically copy a particular
attribute of that Item (say, Attribute 2) from Worksheet 2, and insert it in
a defined destination cell in Worksheet 1 (say, the cell to the right of the
Item just entered by the user).

The second function would -- when the user enters the Item in Worksheet 1 --
automatically copy a set of attributes from that Item (say, Attributes 3, 7,
and 11) from Worksheet 2 and insert them in a defined set of destination
cells in the same row as the Item just entered in Worksheet 1.

The third function would -- again, when the user enters the Item in
Worksheet 1 -- automatically copy all attributes from that Item (in their
current order) from Worksheet 2 and insert them in the same order in the same
row as the Item just entered in Worksheet 1.

I could find, copy, and paste; but would rather automate this process --
preferably using Functions or Macros (which I can only record, since I have
no VBA skills), or VBA code that I could insert in a Macro if someone were
willing to share that.

In the future, we may decide to convert the Worksheet 2 into an Access
table, so (although this is an Excel discussion group) any insights you may
have on how to do this in Access (that is, having users input Item Names in
an Excel worksheet and automatically extract the attributes from an Access
database and insert them into the Excel worksheet) would also be appreciated.

Thanks very much!

--
Keith

Formula, Macro, or VBA code to find and insert
 

Thanks very much, Jacob, for the VLOOKUP tip -- and especially to Luke, for
the detailed VLOOKUP recommendation. Works like a charm!
--
Keith


"Luke M" wrote:

All of your functions can be accomplished using the VLOOKUP function. Basic
structure will look like:
=VLOOKUP($A2,Sheet2!$A:$Z,column_index,FALSE)

column_index indicates the column from your table that you want the return
value to come from. Thus, if wanting attribute #2, which is in column C, the
column_index number would be 3. You can either set these manually, or if you
want a straight "copy" could do:

=VLOOKUP($A2,Sheet2!$A:$Z,COLUMN(B2),FALSE)

because the cell reference in the COLUMN function will "shift" when you copy
this cell to the right, it will then in turn change the column_index value.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Keith" wrote:

I have two worksheets in a workbook. On Worksheet 1, users insert the name of
an item, which is unique. Each item is one of approximately 2000 items in
Worksheet 2, and each item in Worksheet 2 has about 25 attributes. Each
attribute occupies a cell in a row, where the item name is in the first
column of the row.

For example, users might enter the following in Worksheet 1:

Item Name
aaaa
mmmm
ffff

Worksheet 2 has all items and attributes, such as:

Item Name Attribute 1 Attribute 2
etc.
aaaa eudn 1285
bbbb iemg 9583
cccc vobl 5820
etc.

I'd like to find three functions, the first of which would -- when the user
enters an Item Name in Worksheet 1 -- automatically copy a particular
attribute of that Item (say, Attribute 2) from Worksheet 2, and insert it in
a defined destination cell in Worksheet 1 (say, the cell to the right of the
Item just entered by the user).

The second function would -- when the user enters the Item in Worksheet 1 --
automatically copy a set of attributes from that Item (say, Attributes 3, 7,
and 11) from Worksheet 2 and insert them in a defined set of destination
cells in the same row as the Item just entered in Worksheet 1.

The third function would -- again, when the user enters the Item in
Worksheet 1 -- automatically copy all attributes from that Item (in their
current order) from Worksheet 2 and insert them in the same order in the same
row as the Item just entered in Worksheet 1.

I could find, copy, and paste; but would rather automate this process --
preferably using Functions or Macros (which I can only record, since I have
no VBA skills), or VBA code that I could insert in a Macro if someone were
willing to share that.

In the future, we may decide to convert the Worksheet 2 into an Access
table, so (although this is an Excel discussion group) any insights you may
have on how to do this in Access (that is, having users input Item Names in
an Excel worksheet and automatically extract the attributes from an Access
database and insert them into the Excel worksheet) would also be appreciated.

Thanks very much!

--
Keith