Last cell with data in a range

[iamjbunni]

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!

 

[ker_01]

=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]

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.

 

[T. Valko]

Assuming there are no negative numbers and there are no empty cells *within*
the range.

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

 

[Ashish Mathur]

Hi,

You may try this

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

 

[Ziggy]

Or this



=INDIRECT("N"&MAX(IF($N$80:$N$122<>0,ROW($N$80:$N$122))))

Substitute the "N" for your column and also adjust the ranges.

 

[Ziggy]

Or this

=INDIRECT("N"&MAX(IF($N$80:$N$122<>0,ROW($N$80:$N$122))))

Substitute the "N" for your column and also adjust the ranges.

I should have stated this is an ARRAY formula and needs the Cntrl-Alt-
Enter

 

[Ziggy]

I should have stated this is an ARRAY formula and needs the Cntrl-Alt-
Enter

Should be Ctrl-Shift-Enter.. sorru bout that