Prev: Excell Spreedsheets
Next: Segrigation of different datas from one colum
From: iamjbunni on 24 Mar 2010 18:01
I enter weekly data into a spreadsheet with a summary page at the front.
After every week, when new data is entered, I want the formula at the front
to use the cell with the new data instead of me having to change the existing
formula.

For example, my data looks like this:

A B C
276
300
421
175
0
0
0

I need a formula that will automatically detect the last number >0 in column
C.

Thank you for your assistance!
From: ker_01 on 24 Mar 2010 18:23
=Countif(C2:C1000,">0")

so your main page formula might be something like:

=indirect("'Sheet2'!C" & Countif(Sheet2!C2:C1000,">0")

(untested, watch for spelling errors or typos)

"iamjbunni" wrote:

> I enter weekly data into a spreadsheet with a summary page at the front.
> After every week, when new data is entered, I want the formula at the front
> to use the cell with the new data instead of me having to change the existing
> formula.
>
> For example, my data looks like this:
>
> A B C
> 276
> 300
> 421
> 175
> 0
> 0
> 0
>
> I need a formula that will automatically detect the last number >0 in column
> C.
>
> Thank you for your assistance!
From: iamjbunni on 24 Mar 2010 18:35
what i have now is:

=b4/(IF(Paul!D13>0,"1","0")+IF(Trish!D13>0,".6","0")+IF(Ryan!D13>0,"1","0")+IF(Kathy!D13>0,"1","0")+IF(April!D13>0,"1","0"))

Ideally, I want a formula to detect a new number in a column and if it's >0,
to enter "1", if 0, enter "0".

I'm trying to make this report as automated as possible.

"ker_01" wrote:

> =Countif(C2:C1000,">0")
>
> so your main page formula might be something like:
>
> =indirect("'Sheet2'!C" & Countif(Sheet2!C2:C1000,">0")
>
> (untested, watch for spelling errors or typos)
>
> "iamjbunni" wrote:
>
> > I enter weekly data into a spreadsheet with a summary page at the front.
> > After every week, when new data is entered, I want the formula at the front
> > to use the cell with the new data instead of me having to change the existing
> > formula.
> >
> > For example, my data looks like this:
> >
> > A B C
> > 276
> > 300
> > 421
> > 175
> > 0
> > 0
> > 0
> >
> > I need a formula that will automatically detect the last number >0 in column
> > C.
> >
> > Thank you for your assistance!
From: T. Valko on 24 Mar 2010 18:39
Assuming there are no negative numbers and there are no empty cells *within*
the range.

=INDEX(C2:C20,COUNTIF(C2:C20,">0"))

--
Biff
Microsoft Excel MVP


"iamjbunni" <iamjbunni(a)discussions.microsoft.com> wrote in message
news:0CA8D681-78DF-4AB1-B9E2-6BC1333D6B92(a)microsoft.com...
>I enter weekly data into a spreadsheet with a summary page at the front.
> After every week, when new data is entered, I want the formula at the
> front
> to use the cell with the new data instead of me having to change the
> existing
> formula.
>
> For example, my data looks like this:
>
> A B C
> 276
> 300
> 421
> 175
> 0
> 0
> 0
>
> I need a formula that will automatically detect the last number >0 in
> column
> C.
>
> Thank you for your assistance!


From: Ashish Mathur on 24 Mar 2010 23:31
Hi,

You may try this

=LOOKUP(TRUE,C2:C8>0,C2:C8)

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"iamjbunni" <iamjbunni(a)discussions.microsoft.com> wrote in message
news:0CA8D681-78DF-4AB1-B9E2-6BC1333D6B92(a)microsoft.com...
> I enter weekly data into a spreadsheet with a summary page at the front.
> After every week, when new data is entered, I want the formula at the
> front
> to use the cell with the new data instead of me having to change the
> existing
> formula.
>
> For example, my data looks like this:
>
> A B C
> 276
> 300
> 421
> 175
> 0
> 0
> 0
>
> I need a formula that will automatically detect the last number >0 in
> column
> C.
>
> Thank you for your assistance!

 |  Next  |  Last
Pages: 1 2
Prev: Excell Spreedsheets
Next: Segrigation of different datas from one colum