dynamic cells

  • Thread starter Thread starter ak
  • Start date Start date
A

ak

Hi!

I wonder if somebody could help me with the following problem:

I have two columns with cells containing some values B1..B22, C1..C22
everyday I add a new cell e.g. B23 and C23, B24 and C24,...

I wonder if I use these cells in a forumla like REGR how to write
in order to avoid updating the formula every day? REGR(B1:B22;C1:C22)

TIA
Anders.
 
Anders,

You could create a dynamic named range for each of your
ranges. In your example, if you have no data below your
list you could:

Insert->Name->Define

Input the name then in 'Refers To' type =offset
(B1,0,0,counta(B:B),1)

Do the same for column C.

You can then use the named ranges in your formula. Check
the help for the functions above if you need to.

Mike
 
ak said:
Hi!

I tried that but get an error - error in formula.

I did "define name", and then wrote into the
"refer to" dialog box

"=offset(B1,0,0,counta(B:B),1)"

when I press enter it says the formula is wrong.

is the idea later to use the name as the end of a range
like SUM(A1:MyName) or as the whole range SUM(MyName)?

tia
Anders
Click to expand...

You must use absolute references, otherwise the range will be defined
differently depending on which cell you are in!
=OFFSET($B$1,0,0,COUNTA($B:$B),1)
 
Back
Top