Hi, I am having trouble figuring out how to make a range dynamic both
horizontally and vertically in my sheet. I am using offset formula in
the format =OFFSET(Data!$CL$1,0,0,COUNTA(Data!$CL:$CL),1) to make the
range dynamic as more rows of data are added but I also need the
column to be dynamic. For instance I need this range (PriceToEarnings)
to always reference the column from the 'data' sheet that contains
"PE" in row 3. I have tried the following formula with no luck.

=OFFSET(CELL("Address",INDEX(Data!$CA$2:$DD$2,MATC H("PE",Data!$CA$2:$DD
$2,0))),0,0,COUNTA(CELL("Address",INDEX(Data!$CA$2 :$DD$2,MATCH
("PE",Data!$CA$2:$DD$2,0)))),1)

Basically I want to search for "PE" count the columns in that row and
have that data be my range for analysis. The column that contains "PE"
may change and the amount of rows may change which is why I'm trying
to make this dynamic in two directions (if that makes sense).

Thanks a lot to anyone that can help!
Brian

Excel 2007 Tables are dynamic
for both rows and columns.
http://www.mediafire.com/file/r3mzo304ntk/03_24_09.xlsx

Sorry Unfortunately I have Excel 2003 at my workplace and there's no
way around that.