Dynamic Range

[jluft]

All,
is there a way, WITHOUT vba, to create the following formula?

Create a reference cell that contains a # that varies according to user preference.

Target Range

A1 = 5
A2 = 5
A3 = 5
A4 = 5
A5 = 5
A6 = 5
A7 = 5
A8 = 5
A9 = 5
A10 = 5

in column b i would like to create some sort of formula that knows to take the sum of the corresponding Row in column B, and a particular # of cells previous to that depending on the value of the user variable

i am thinking that there may be some way to use index or some form of a sum to accomplish this

for example, if user variable =3 , cell b8 would sum the contents of a8, a7 and a6
if it were 4, cell b8 would some a8, a7, a6 and a5

thanks

 

[xlbo]

If the start cell is always constant at A8 then

=SUM(INDIRECT("A" & 8 - B10):A8)

where your user variable is in B10

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question