The parameters of =offset are:
OFFSET(reference,rows,cols,height,width)
the "Reference" is the Base Cell
Rows is the number of rows from the base cell to start the selection.
and Height is the number of rows to select.
So...
YOU have:
OFFSET(
Reference = Sheet1!$B$8,
rows = 0,
cols = 1,
height = MATCH(“Hello”,Sheet1!$B$8:$B$265,1),
width = 1)
Shouldn't it be:
OFFSET(
Reference = Sheet1!$B$8,
rows = MATCH(“Hello”,Sheet1!$B$8:$B$265,1),
cols = 1,
height = COUNTA(Sheet1!$A$8:$A$256)-MATCH("Hello",Sheet1!$A$8:$A$256,0)+1,
width = 1)
Paul
----- Original Message ----
> From: 0 1 <hhholme...@gmail.com>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Fri, December 3, 2010 10:56:38 AM
> Subject: $$Excel-Macros$$ Dynamic range based on matching text in adjacent
>column
>
> I'm trying to create a dynamic range to identify the rows in Column C
> that have the text "Hello" in the corresponding row in column B.
>
> For example:
>
> B C
> 8 ABC 3
> 9 EFG 5
> 10 XYZ 12
> 11 Hello 16
> 12 Hello 17
> 13 Hello 23
>
> In this example, the dynamic range needs to select C11:C13 (i.e., the
> cells containing 16, 17, and 23). Instead, it's selecting C8:C13
> (i.e., *all* of the cells in Column C):
>
> =OFFSET(Sheet1!$B$8,0,1,MATCH(“Hello”,Sheet1!$B$8:$B$265,1),1)
>
> Any ideas? If it helps to know: the values in column B are always
> text, the rows that have "Hello" are always grouped together, and the
> rows that have "Hello" are always at the end of the list.
>
> Thanks for any tips.
>
> --
>----------------------------------------------------------------------------------
>-
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
>http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
