ok i have worked out how to create the actual text reference, but how do i
now convert this so it can be used in a formula?

=("A"&(MATCH("CH",'Logistics
sheet'!A1:A2000,0)))&":"&("A"&(MATCH("CH",'Logisti cs
sheet'!A1:A2000,0)+COUNTIF('Logistics sheet'!A:A,"CH")))

the answer to this is A5:A85, how do i then get the answer into a formula to
calculate, such as =SUM(A8:A85) ?

"BBB" wrote:

hi,

i need a formula to search a column for a range of results and return the
top and botom reference for this range.
for example i have some information broken down by categories, these
categories each have their own code in column A, i would like to know that
from A1:A26 is the first category, and if someone adds rows to this the
formula can change to tell me that it is now from A1:A32 (this also needs to
work for consecutive categories after, ie A27:A51).

I need to know the actual cell references not just the amount of rows that
it counts, because i want to include this in another part of a formula?

any help is appreciated.