Last cell with data in a range

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!
 
K

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)
 
I

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

T. Valko

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

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

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.
 
Z

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top