| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Retrieve multiple data rows data from a very long list and copy t | Excel Discussion (Misc queries) | |||
| ranking query | Excel Discussion (Misc queries) | |||
| Excel 2002; copy formulas to one workbook to another diff data | Excel Worksheet Functions | |||
| How to separate data.... | Excel Discussion (Misc queries) | |||
| How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) | |||
Hybrid Mode
