Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find data in Sheet1 and list them in Sheet2
I have the following range of Data in Sheet 1
Table 1 Table 2 A A1 B B3 C C4 D D1.1 The problem is that I don't know exactly in what column "Table 2" might exist. It can be in A1, B1, C1, D1 etc .... In Sheet2 A1 I want to create a formula that achieves the following: Search Sheet1 range A1: Z 10000, find the heading "Table2" and then copy all of the items below Table2 in cells Sheet2!A2:A1000. Any ideas how to do this? I tried using a combination of Vlookup and Hlookup functions with no luck. Thanks Michael |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find data in Sheet1 and list them in Sheet2
Try this in A2 on sheet 2, then copy it down to A1000
=HLOOKUP("Table 2",Sheet1!$A$1:$Z$1000,ROW(),FALSE) This will only work if you want the data in eg row 2 on sheet 1 to be in row 2 on sheet 2. If not, you'll need to add/subtract an offset to ROW() as this returns the row number it is in. Ian "Michael" wrote in message ... I have the following range of Data in Sheet 1 Table 1 Table 2 A A1 B B3 C C4 D D1.1 The problem is that I don't know exactly in what column "Table 2" might exist. It can be in A1, B1, C1, D1 etc .... In Sheet2 A1 I want to create a formula that achieves the following: Search Sheet1 range A1: Z 10000, find the heading "Table2" and then copy all of the items below Table2 in cells Sheet2!A2:A1000. Any ideas how to do this? I tried using a combination of Vlookup and Hlookup functions with no luck. Thanks Michael |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find data in Sheet1 and list them in Sheet2
Thanks! This worked just great!
-- Michael "Ian" wrote in message ... Try this in A2 on sheet 2, then copy it down to A1000 =HLOOKUP("Table 2",Sheet1!$A$1:$Z$1000,ROW(),FALSE) This will only work if you want the data in eg row 2 on sheet 1 to be in row 2 on sheet 2. If not, you'll need to add/subtract an offset to ROW() as this returns the row number it is in. Ian "Michael" wrote in message ... I have the following range of Data in Sheet 1 Table 1 Table 2 A A1 B B3 C C4 D D1.1 The problem is that I don't know exactly in what column "Table 2" might exist. It can be in A1, B1, C1, D1 etc .... In Sheet2 A1 I want to create a formula that achieves the following: Search Sheet1 range A1: Z 10000, find the heading "Table2" and then copy all of the items below Table2 in cells Sheet2!A2:A1000. Any ideas how to do this? I tried using a combination of Vlookup and Hlookup functions with no luck. Thanks Michael |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find data in Sheet1 and list them in Sheet2
Glad I could help. Thanks for the feedback.
Ian "Michael" wrote in message ... Thanks! This worked just great! -- Michael "Ian" wrote in message ... Try this in A2 on sheet 2, then copy it down to A1000 =HLOOKUP("Table 2",Sheet1!$A$1:$Z$1000,ROW(),FALSE) This will only work if you want the data in eg row 2 on sheet 1 to be in row 2 on sheet 2. If not, you'll need to add/subtract an offset to ROW() as this returns the row number it is in. Ian "Michael" wrote in message ... I have the following range of Data in Sheet 1 Table 1 Table 2 A A1 B B3 C C4 D D1.1 The problem is that I don't know exactly in what column "Table 2" might exist. It can be in A1, B1, C1, D1 etc .... In Sheet2 A1 I want to create a formula that achieves the following: Search Sheet1 range A1: Z 10000, find the heading "Table2" and then copy all of the items below Table2 in cells Sheet2!A2:A1000. Any ideas how to do this? I tried using a combination of Vlookup and Hlookup functions with no luck. Thanks Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
Macro to Copy data from a list in sheet1 and paste into sheet2 | Excel Discussion (Misc queries) | |||
DataVaildationList works in Sheet1, but does not in Sheet2; Why | Excel Discussion (Misc queries) | |||
How to find a data in Sheet1,automatic transfer to Sheet2(Excel) | Excel Discussion (Misc queries) | |||
List a group of repeated data from sheet1 to sheet2 but not repeat | New Users to Excel |