Offset formula help!!

  • Thread starter Thread starter Vlad999
  • Start date Start date
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)
 
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.
 
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)
 
Thank you I will try both solutions, but i suspect you are right and the
data output needs to be redesigned.

Thanks
 
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)
 
Back
Top