OFFSET cell references

[Gary Thomson]

I have the following data in Sheet 1:

A B
1 St. David's - Unit 1 £500
2 St. David's - Unit 2 £600
3 St. David's - Unit 3 £300
4 Dalkeith - Unit 1 £200
5 Dalkeith - Unit 2 £300
6 SEN - Unit 1 £700
7 SEN - Unit 2 £700

Now in Sheet 2, I have the following data




A B C D E
1 1-Feb 2-Feb 3-Feb 4-Feb . . . . .
2 St. David's ab be bd cd
3 St. David's abc me
4 St. David's bdc
5 Dalkeith f
6 Dalkeith me f
7 SEN n
8 SEN xxx m
..
..
..
..

(This is a simplified version - the labels in column A can
only be St. David's - Unit "x", Dalkeith - Unit "x" or
SEN - Unit "x", but there are 500 entries in this column).


In Sheet 3, I have a formula in cell C9 as follows:

=SUMPRODUCT((ISERROR(FIND(B9,OFFSET('Sheet2'!
$B$2,,,3,$E$4)))=FALSE)*('Sheet1'!$B$1:$B$3))

where B9 contains "a".

This formula gets copied down for "b", "c", etc

$E$4 contains the number of days for the given month (i.e.
for February it will contain 29 this year), so what the
above formula effectively does is count the number of
times "a" appears in the "last day of the month column"
for all Units within St. David's.

In Cell D9, I have the formula

=SUMPRODUCT((ISERROR(FIND(B9,OFFSET('Sheet2'!
$B$5,,,2,$E$4)))=FALSE)*('Sheet1'!$B$4:$B$5))

which calculates the number of times "a" appears in the
last day of the month column for all Units within Dalkeith.

A similar formula is used for SEN.

However, the problem I have is that the number of Units
within each school is still constantly changing, and
because all schools are listed on a consecutive vertical
list, the "SUMPRODUCT" formula loses its value (due to the
OFFSET function), as it will pick up occurences of "a"
from the wrong set of Units.

Therefore, I could do a COUNTIF to count the number of
times each of the Schools appears within the list. (in
the case above, St. David's appear 3 times, Dalkeith and
SEN twice).

I would then know:

the starting position for the OFFSET
the length/height of the OFFSET search
the search range in sheet 1

But how do I translate these into cell references??

I.e. given the example above,

For Dalkeith, I would want the starting position of the
OFFSET to be B5 (since this is B2 + 3 :the number of Units
within St. David's)

AND

the length of the OFFSET search woould be 2 (the number of
Units within Dalkeith)

the search range in sheet 1 would be 'Sheet1'$B$4:$B$5
(where $B$4 is found by $B$1 plus 3 = $B$4 :the 3 being
the number of Units within St. David's, and the $B$5 is
found by $B$1 plus 3 plus (2-1) = $B$5 :the 3 being the
number of Units within St. David's and the (2-1) the extra
required to account for the Units within Dalkeith).


I realise this is an extremely long post and it may not
make sense, but there may be a really easy way to solve
this.

 

[Jonathan Rynd]

In Sheet 3, I have a formula in cell C9 as follows:

=SUMPRODUCT((ISERROR(FIND(B9,OFFSET('Sheet2'!
$B$2,,,3,$E$4)))=FALSE)*('Sheet1'!$B$1:$B$3))

where B9 contains "a".

$E$4 contains the number of days for the given month (i.e.
for February it will contain 29 this year), so what the
above formula effectively does is count the number of
times "a" appears in the "last day of the month column"
for all Units within St. David's.

I don't see how this does what you say it does. Inside the SUMPRODUCT
function you are supposed to have two arrays. But the first item,
(ISERROR(FIND(B9,OFFSET('Sheet2'!$B$2,,,3,$E$4)))=FALSE)
, is not an array. It is going to be TRUE or FALSE.

I don't think you understand what the FIND function does. Please read
the help/documentation on FIND.

 

[Jonathan Rynd]

In Sheet 3, I have a formula in cell C9 as follows:

=SUMPRODUCT((ISERROR(FIND(B9,OFFSET('Sheet2'!
$B$2,,,3,$E$4)))=FALSE)*('Sheet1'!$B$1:$B$3))

where B9 contains "a".

This formula gets copied down for "b", "c", etc

$E$4 contains the number of days for the given month (i.e.
for February it will contain 29 this year), so what the
above formula effectively does is count the number of
times "a" appears in the "last day of the month column"
for all Units within St. David's.

Oops, I didn't see those extra parameters to the OFFSET. Please ignore
my other post.

I still think this formula doesn't do what you say it does. It looks
like it adds the prices in pounds of the units (from sheet 1) when
Sheet2!B2:AE4 contains "a". It doesn't look like it counts.

To answer your question, I think you should be using MATCH.

First row of St. David's:
2
Last row of St. David's:
MATCH("Dalkeith - Unit 1",Sheet2!A2:A501,0)
First row of Dalkeith:
MATCH("Dalkeith - Unit 1",Sheet2!A2:A501,0)+1
Last row of Dalkeith:
MATCH("SEN - Unit 1",Sheet2!A2:A501,0)

etc.

Things would be even simpler, with less duplicated code, if you defined a
dynamic range for each school using OFFSET and MATCH.

 

[Peo Sjoblom]

I don't see how this does what you say it does. Inside the SUMPRODUCT
function you are supposed to have two arrays. But the first item,
(ISERROR(FIND(B9,OFFSET('Sheet2'!$B$2,,,3,$E$4)))=FALSE)
, is not an array. It is going to be TRUE or FALSE.

I don't think you understand what the FIND function does. Please read
the help/documentation on FIND.

Maybe you don't understand what sumproduct can do?
Do a google search and you'll see it can be used to count values by using
true and false and
by using an operand to coerce them into 1s and 0s

=SUMPRODUCT(--(A2:A10="a"))

will count "a"

Having said that I have no idea if the OPs formula works and I would
probably use [isnumber(find]
as opposed to [iserror(find] but nevertheless you are wrong in your
statement

Regards,

Peo Sjoblom