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