Last cell with data in a range

  • Thread starter Thread starter iamjbunni
  • Start date Start date
I

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!
 
=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)
 
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.
 
Assuming there are no negative numbers and there are no empty cells *within*
the range.

=INDEX(C2:C20,COUNTIF(C2:C20,">0"))
 
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.
 
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.
Click to expand...

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