copy entire row of data from separate w/s
 

hi--i'm trying to come up with either a function or a macro, & would
appreciate any input!

i have 2 worksheets, one with a list of production outputs for 12 production
machines. the 2nd worksheet is the crew that ran the lines. in the outputs,
each line has different values for each product code. (code, description, and
then specs.)

i'd like to set it up so that on the crew worksheet, a dropdown list will
provide the product code in the leftmost column. (that part i've got figured
out).

once the product code is selected, i'd like excel to lookup the row of data
related to that product code in the "outputs", and copy the entire row to the
"crew" worksheet. there are 21 columns to be copies, not including the 1st
column with the product code. i can't use a vlookup because the product codes
in the outputs sheet are not in numerical order (and can't be rearranged,
because there are several macros linked to other worksheets); i tried an
index/match formula but it only returns one cell value at a time. there are
also occasionally blank lines within each section's product codes.

any function/macro suggestions would be very welcome!
thanks
steph
--
steph

copy entire row of data from separate w/s
 

The VLOOKUP function does not require that the Lookup Table be in numerical
order if one uses the FALSE option at the end of the formula......such as

=VLOOKUP(A1,G1:H10,2,FALSE)

Vaya con Dios,
Chuck, CABGx3



"steph" wrote:

hi--i'm trying to come up with either a function or a macro, & would
appreciate any input!

i have 2 worksheets, one with a list of production outputs for 12 production
machines. the 2nd worksheet is the crew that ran the lines. in the outputs,
each line has different values for each product code. (code, description, and
then specs.)

i'd like to set it up so that on the crew worksheet, a dropdown list will
provide the product code in the leftmost column. (that part i've got figured
out).

once the product code is selected, i'd like excel to lookup the row of data
related to that product code in the "outputs", and copy the entire row to the
"crew" worksheet. there are 21 columns to be copies, not including the 1st
column with the product code. i can't use a vlookup because the product codes
in the outputs sheet are not in numerical order (and can't be rearranged,
because there are several macros linked to other worksheets); i tried an
index/match formula but it only returns one cell value at a time. there are
also occasionally blank lines within each section's product codes.

any function/macro suggestions would be very welcome!
thanks
steph
--
steph

copy entire row of data from separate w/s
 

ok. can you write a vlookup that pulls data from an entire row (21 columns)?
--
steph


"CLR" wrote:

The VLOOKUP function does not require that the Lookup Table be in numerical
order if one uses the FALSE option at the end of the formula......such as

=VLOOKUP(A1,G1:H10,2,FALSE)

Vaya con Dios,
Chuck, CABGx3



"steph" wrote:

hi--i'm trying to come up with either a function or a macro, & would
appreciate any input!

i have 2 worksheets, one with a list of production outputs for 12 production
machines. the 2nd worksheet is the crew that ran the lines. in the outputs,
each line has different values for each product code. (code, description, and
then specs.)

i'd like to set it up so that on the crew worksheet, a dropdown list will
provide the product code in the leftmost column. (that part i've got figured
out).

once the product code is selected, i'd like excel to lookup the row of data
related to that product code in the "outputs", and copy the entire row to the
"crew" worksheet. there are 21 columns to be copies, not including the 1st
column with the product code. i can't use a vlookup because the product codes
in the outputs sheet are not in numerical order (and can't be rearranged,
because there are several macros linked to other worksheets); i tried an
index/match formula but it only returns one cell value at a time. there are
also occasionally blank lines within each section's product codes.

any function/macro suggestions would be very welcome!
thanks
steph
--
steph

copy entire row of data from separate w/s
 

Not a single formula.......each column reqires it's own........

In B1 put =VLOOKUP(A1,G1:J10,2,FALSE)
In C1 put =VLOOKUP(A1,G1:J10,3,FALSE)
In D1 put =VLOOKUP(A1,G1:J10,4,FALSE)
....etc etc

If necessary, you can also use ABSOLUTE cell references where needed and
copy and paste and/or insert the formulas by macro, if it's something you
have to do frequently.....all the details would be specific to each
application

Vaya con Dios,
Chuck, CABGx3



"steph" wrote:

ok. can you write a vlookup that pulls data from an entire row (21 columns)?
--
steph


"CLR" wrote:

The VLOOKUP function does not require that the Lookup Table be in numerical
order if one uses the FALSE option at the end of the formula......such as

=VLOOKUP(A1,G1:H10,2,FALSE)

Vaya con Dios,
Chuck, CABGx3



"steph" wrote:

hi--i'm trying to come up with either a function or a macro, & would
appreciate any input!

i have 2 worksheets, one with a list of production outputs for 12 production
machines. the 2nd worksheet is the crew that ran the lines. in the outputs,
each line has different values for each product code. (code, description, and
then specs.)

i'd like to set it up so that on the crew worksheet, a dropdown list will
provide the product code in the leftmost column. (that part i've got figured
out).

once the product code is selected, i'd like excel to lookup the row of data
related to that product code in the "outputs", and copy the entire row to the
"crew" worksheet. there are 21 columns to be copies, not including the 1st
column with the product code. i can't use a vlookup because the product codes
in the outputs sheet are not in numerical order (and can't be rearranged,
because there are several macros linked to other worksheets); i tried an
index/match formula but it only returns one cell value at a time. there are
also occasionally blank lines within each section's product codes.

any function/macro suggestions would be very welcome!
thanks
steph
--
steph

copy entire row of data from separate w/s
 

nuts. is the vlookup a better solution than using an index/match formula?
--
steph


"CLR" wrote:

Not a single formula.......each column reqires it's own........

In B1 put =VLOOKUP(A1,G1:J10,2,FALSE)
In C1 put =VLOOKUP(A1,G1:J10,3,FALSE)
In D1 put =VLOOKUP(A1,G1:J10,4,FALSE)
...etc etc

If necessary, you can also use ABSOLUTE cell references where needed and
copy and paste and/or insert the formulas by macro, if it's something you
have to do frequently.....all the details would be specific to each
application

Vaya con Dios,
Chuck, CABGx3



"steph" wrote:

ok. can you write a vlookup that pulls data from an entire row (21 columns)?
--
steph


"CLR" wrote:

The VLOOKUP function does not require that the Lookup Table be in numerical
order if one uses the FALSE option at the end of the formula......such as

=VLOOKUP(A1,G1:H10,2,FALSE)

Vaya con Dios,
Chuck, CABGx3



"steph" wrote:

hi--i'm trying to come up with either a function or a macro, & would
appreciate any input!

i have 2 worksheets, one with a list of production outputs for 12 production
machines. the 2nd worksheet is the crew that ran the lines. in the outputs,
each line has different values for each product code. (code, description, and
then specs.)

i'd like to set it up so that on the crew worksheet, a dropdown list will
provide the product code in the leftmost column. (that part i've got figured
out).

once the product code is selected, i'd like excel to lookup the row of data
related to that product code in the "outputs", and copy the entire row to the
"crew" worksheet. there are 21 columns to be copies, not including the 1st
column with the product code. i can't use a vlookup because the product codes
in the outputs sheet are not in numerical order (and can't be rearranged,
because there are several macros linked to other worksheets); i tried an
index/match formula but it only returns one cell value at a time. there are
also occasionally blank lines within each section's product codes.

any function/macro suggestions would be very welcome!
thanks
steph
--
steph

copy entire row of data from separate w/s
 

As long as the data you are retrieving is to the RIGHT of the lookup column,
yes..........

Vaya con Dios,
Chuck, CABGx3


"steph" wrote in message
...
nuts. is the vlookup a better solution than using an index/match formula?
--
steph


"CLR" wrote:

Not a single formula.......each column reqires it's own........

In B1 put =VLOOKUP(A1,G1:J10,2,FALSE)
In C1 put =VLOOKUP(A1,G1:J10,3,FALSE)
In D1 put =VLOOKUP(A1,G1:J10,4,FALSE)
...etc etc

If necessary, you can also use ABSOLUTE cell references where needed and
copy and paste and/or insert the formulas by macro, if it's something
you
have to do frequently.....all the details would be specific to each
application

Vaya con Dios,
Chuck, CABGx3



"steph" wrote:

ok. can you write a vlookup that pulls data from an entire row (21
columns)?
--
steph


"CLR" wrote:

The VLOOKUP function does not require that the Lookup Table be in
numerical
order if one uses the FALSE option at the end of the
formula......such as

=VLOOKUP(A1,G1:H10,2,FALSE)

Vaya con Dios,
Chuck, CABGx3



"steph" wrote:

hi--i'm trying to come up with either a function or a macro, &
would
appreciate any input!

i have 2 worksheets, one with a list of production outputs for 12
production
machines. the 2nd worksheet is the crew that ran the lines. in the
outputs,
each line has different values for each product code. (code,
description, and
then specs.)

i'd like to set it up so that on the crew worksheet, a dropdown
list will
provide the product code in the leftmost column. (that part i've
got figured
out).

once the product code is selected, i'd like excel to lookup the
row of data
related to that product code in the "outputs", and copy the entire
row to the
"crew" worksheet. there are 21 columns to be copies, not including
the 1st
column with the product code. i can't use a vlookup because the
product codes
in the outputs sheet are not in numerical order (and can't be
rearranged,
because there are several macros linked to other worksheets); i
tried an
index/match formula but it only returns one cell value at a time.
there are
also occasionally blank lines within each section's product codes.

any function/macro suggestions would be very welcome!
thanks
steph
--
steph

copy entire row of data from separate w/s
 

You can create a *single* Vlookup formula, which when copied along a row,
*across* columns, will *automatically* increment the column index number for
you, so that you can easily return those 21 columns of data.

Try something like this:

=VLOOKUP($A1,$B$1:$V$200,COLUMNS($A:B),0)

And copy across and down to get your 21 cells of data.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"steph" wrote in message
...
nuts. is the vlookup a better solution than using an index/match formula?
--
steph


"CLR" wrote:

Not a single formula.......each column reqires it's own........

In B1 put =VLOOKUP(A1,G1:J10,2,FALSE)
In C1 put =VLOOKUP(A1,G1:J10,3,FALSE)
In D1 put =VLOOKUP(A1,G1:J10,4,FALSE)
...etc etc

If necessary, you can also use ABSOLUTE cell references where needed and
copy and paste and/or insert the formulas by macro, if it's something you
have to do frequently.....all the details would be specific to each
application

Vaya con Dios,
Chuck, CABGx3



"steph" wrote:

ok. can you write a vlookup that pulls data from an entire row (21
columns)?
--
steph


"CLR" wrote:

The VLOOKUP function does not require that the Lookup Table be in
numerical
order if one uses the FALSE option at the end of the
formula......such as

=VLOOKUP(A1,G1:H10,2,FALSE)

Vaya con Dios,
Chuck, CABGx3



"steph" wrote:

hi--i'm trying to come up with either a function or a macro, &
would
appreciate any input!

i have 2 worksheets, one with a list of production outputs for 12
production
machines. the 2nd worksheet is the crew that ran the lines. in the
outputs,
each line has different values for each product code. (code,
description, and
then specs.)

i'd like to set it up so that on the crew worksheet, a dropdown
list will
provide the product code in the leftmost column. (that part i've
got figured
out).

once the product code is selected, i'd like excel to lookup the row
of data
related to that product code in the "outputs", and copy the entire
row to the
"crew" worksheet. there are 21 columns to be copies, not including
the 1st
column with the product code. i can't use a vlookup because the
product codes
in the outputs sheet are not in numerical order (and can't be
rearranged,
because there are several macros linked to other worksheets); i
tried an
index/match formula but it only returns one cell value at a time.
there are
also occasionally blank lines within each section's product codes.

any function/macro suggestions would be very welcome!
thanks
steph
--
steph