Audio update formula

**ttvpaul** · 05-27-2010, 09:20 AM

Hi everyone,

I have been trying to find the following info on the net and its driving me insane!
There must be a simple explanation to this, so any help would be greatly appreciated.

A1=1
A2=2
A3=3
A4=4

I need help with:

=COUNTIF(sheet1!A1:sheet1!A4,"<2")
Answer = 1

My probelm is that if i want to continually manually keep add data to rows A5, A6, A7, A8 with various numeric values.

How can i have the A4 (Highlighted in red in the formula above) update automatically with the newest latest row reference number, for example, A5=3,A6=7,A7=11,A8=34 etc.. is this possible?

I guesss i would describe this as an formula auto update

Have i made myself clear? What are your thoughts please?

Cheers
Paul

**arthurbr** · 05-27-2010, 09:23 AM

Why not use Dynamic Ranges ?
See this link[

**ttvpaul** · 05-27-2010, 09:52 AM

Many thanks for your reply.
I must add I am a beginner, so do not understand how to do adapt this formula to my worksheet. Any ideas please?

**ChemistB** · 05-27-2010, 09:56 AM

Define your name as "whatever" =OFFSET($A$1,0,0,COUNT($A$1:$A$1000))
then in your formula
=COUNTIF(whatever,"<2")
Does that work for you?

**ttvpaul** · 05-27-2010, 10:45 AM

Many thanks, i got that to work.

However cells A5, A6, A7, A8 etc...already have formulas in them that are at zero until other data is entered elsewhere on the sheet to populate them, so my <2 is including all the zeros as well.

I am trying to:

=COUNTIF(sheet1!A1:sheet1!A4,"<2")

Have the A4 above increase by one as as I add data to the rows.

Have i made myself understandable?