hLookup dynamic range
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 |
hLookup dynamic range
Excel 2007 Tables are dynamic
for both rows and columns. http://www.mediafire.com/file/r3mzo304ntk/03_24_09.xlsx |
hLookup dynamic range
Sorry Unfortunately I have Excel 2003 at my workplace and there's no
way around that. |
All times are GMT +1. The time now is 01:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com