Last cell with data in a range
 

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!

Last cell with data in a range
 

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

Last cell with data in a range
 

what i have now is:

=b4/(IF(Paul!D130,"1","0")+IF(Trish!D130,".6","0")+I F(Ryan!D130,"1","0")+IF(Kathy!D130,"1","0")+IF(A pril!D130,"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!

Last cell with data in a range
 

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" wrote in message
...
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!

Last cell with data in a range
 

Hi,

You may try this

=LOOKUP(TRUE,C2:C80,C2:C8)

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"iamjbunni" wrote in message
...
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!

Last cell with data in a range
 

On Mar 24, 9:31*pm, "Ashish Mathur" wrote:

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.

Last cell with data in a range
 

On Mar 25, 11:04*am, Ziggy wrote:
On Mar 24, 9:31*pm, "Ashish Mathur" wrote:

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

Last cell with data in a range
 

On Mar 25, 1:17*pm, Ziggy wrote:
On Mar 25, 11:04*am, Ziggy wrote:

On Mar 24, 9:31*pm, "Ashish Mathur" wrote:

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

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