Offset formula help!!

V

Vlad999

I am trying to define ranges each of my ranges is characterised by an
identifier in this case "D1" then directly below it are property
descriptions. So my sheet looks like this:

D1
XXX
XXX
XXX

D2
XXX
XXX
XXX

My problem is that when i use this formula it counts all text values so
when i want to select range D1 rather than selecting D1 and the three
rows below it, it selects D1 and 8 rows below D1. Any ideas on how I
can make the formula select the range properly?

=OFFSET(Sheet1!$A$1,MATCH("D1",Sheet1!$A:$A,0)-1,0,*COUNTA(Sheet1!$A:$A*),5)
 
H

Harlan Grove

Vlad999 wrote...
I am trying to define ranges each of my ranges is characterised by an
identifier in this case "D1" then directly below it are property
descriptions. So my sheet looks like this:

D1
XXX
XXX
XXX

D2
XXX
XXX
XXX

My problem is that when i use this formula it counts all text values so
when i want to select range D1 rather than selecting D1 and the three
rows below it, it selects D1 and 8 rows below D1. Any ideas on how I
can make the formula select the range properly?
Click to expand...

If there's actually a blank line between the 4th line in the D1 section
and the top line in the D2 section, try

INDEX(A:A,MATCH("D1",A:A,0)):INDEX(A:A,MATCH("D1",A:A,0)
+MATCH("",(INDEX(A:A,MATCH("D1",A:A,0)+1):A$65536)&"",0)-1)

which requires array entry in formulas.
 
A

Arvi Laanemets

Hi

It'll be much easier for you to redesign the sheet to a table:
Identifier Descr1 Descr2 Descr3
D1 XXX XXX XXX
D2 XXX XXX XXX

Now, when you need descriptions for some identifier, you can use a simple
VLOOKUP formula. P.e. to return Descr3 for D2:
=VLOOKUP(D2,$A$2:$C$100,4,0)
 
V

Vlad999

Thank you I will try both solutions, but i suspect you are right and the
data output needs to be redesigned.

Thanks
 
V

Vlad999

Hay can you explain to me exactly how this formula works? What part of
the formula relates to the column selection?


INDEX(A:A,MATCH("D1",A:A,0)):INDEX(A:A,MATCH("D1", A:A,0)
+MATCH("",(INDEX(A:A,MATCH("D1",A:A,0)+1):A$65536) &"",0)-1)
 
Top