looking up cell address

[Andrew Hodgson]

I want to create a formulae that dynamicaly alters the row references of a
SUM function nested within a INDIRECT function based on a start and end date
variables.

In order to to this I need a lookup that returns the row number and/or cell
adress of the first cell that matches or exceeds the date variable and
utilises the result in a SUM function nested within the the INDIRECT formulae
(If possible I want to avoid inserting an additional column in the original
data table that calculates the row refrence to be used with a simple vlookup)

Any help offered would be appreciated

Kind Regards
Andrew

 

[Paul C]

for your sum you probably wnat to do something like this

=SUM(OFFSET(B4,2,0,3,1))

this will sum b6:b8. If you add a formula to instead of the 2 you can set
the first cell of your range. Then you can change the 3 (height of the
range) to match match the height of the data.

A MATCH formula would probably give you the formula for the first position
and a COUNT - MATCH would give you the height.