Last non blank cell in selected cells
 

Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have
Excel find the last non blank cell in the group? These are numeric values
that update a report weekly.
Thanks

Last non blank cell in selected cells
 

What do you mean by "find" the last non blank cell in the group?

Do you want a formula that returns the value from that cell?

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDEX(A1:A20,MAX((MOD(ROW(A1:A20),5)=0)*(A1:A20< "")*(ROW(A1:A20))))

Biff

"Ron Weaver" wrote in message
...
Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and
have
Excel find the last non blank cell in the group? These are numeric values
that update a report weekly.
Thanks

Last non blank cell in selected cells
 

=IF(COUNT(A1:A20)<5,A1,MAX(IF(MOD(ROW(A1:A20),5)=0 ,A1:A20)))

ctrl+shift+enter, not just enter


"Ron Weaver" wrote:

Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have
Excel find the last non blank cell in the group? These are numeric values
that update a report weekly.
Thanks

Last non blank cell in selected cells
 

=IF(COUNT(A1:A20)<5,A1,MAX(IF(MOD(ROW(A1:A20),5)=0 ,A1:A20)))

That returns the max value from the specified cells, not the last value from
the specified cells. Also, what if A10 is the only entry in the range?

Biff

"Teethless mama" wrote in message
...
=IF(COUNT(A1:A20)<5,A1,MAX(IF(MOD(ROW(A1:A20),5)=0 ,A1:A20)))

ctrl+shift+enter, not just enter


"Ron Weaver" wrote:

Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and
have
Excel find the last non blank cell in the group? These are numeric values
that update a report weekly.
Thanks

Last non blank cell in selected cells
 

I will try to make this a little more clear. On week one I will put a figure
in A1. I want this figure to show in B1. On week two I will put a figure in
A5. Now I want this figure to show in B1. On week three I will put a figure
in A10. Now I want this figure to show in B1, etc. To complicate things,
there will be other values in A2,A3,A4,A6,A7,A8,A9.

"T. Valko" wrote:

=IF(COUNT(A1:A20)<5,A1,MAX(IF(MOD(ROW(A1:A20),5)=0 ,A1:A20)))

That returns the max value from the specified cells, not the last value from
the specified cells. Also, what if A10 is the only entry in the range?

Biff

"Teethless mama" wrote in message
...
=IF(COUNT(A1:A20)<5,A1,MAX(IF(MOD(ROW(A1:A20),5)=0 ,A1:A20)))

ctrl+shift+enter, not just enter


"Ron Weaver" wrote:

Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and
have
Excel find the last non blank cell in the group? These are numeric values
that update a report weekly.
Thanks

Last non blank cell in selected cells
 

Try something like this:

B1:
=INDEX(A1:A1000,MAX(INDEX((MOD(ROW(A1:A1000),5)=0) *ISNUMBER(A1:A1000)*ROW(A1:A1000),0)))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Ron Weaver" wrote:

Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have
Excel find the last non blank cell in the group? These are numeric values
that update a report weekly.
Thanks

Last non blank cell in selected cells
 

That works perfectly. Now, what if I want to do the same thing across:
Formula in B1
Values in A1,E1,J1,N1, etc.
Thanks

"Ron Coderre" wrote:

Try something like this:

B1:
=INDEX(A1:A1000,MAX(INDEX((MOD(ROW(A1:A1000),5)=0) *ISNUMBER(A1:A1000)*ROW(A1:A1000),0)))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Ron Weaver" wrote:

Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have
Excel find the last non blank cell in the group? These are numeric values
that update a report weekly.
Thanks

Last non blank cell in selected cells
 

OK....
But, with one caveat:
The formula can't be in Row_1 or you'll have a circular ref.

So
B2:
=INDEX(1:1,MAX(INDEX((MOD(COLUMN(1:1),5)=0)*ISNUMB ER(1:1)*COLUMN(1:1),0,0),1))

BTW: Same issue for the formula referencing Col_A...
It can't be in Col_A and...
If the formula will be in a row other than Row_1
Use this version:
=INDEX(A1:A1000,MAX(INDEX((MOD(ROW(A1:A1000),5)=0) *ISNUMBER(A1:A1000)*ROW(A1:A1000),0),1))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Weaver" wrote:

That works perfectly. Now, what if I want to do the same thing across:
Formula in B1
Values in A1,E1,J1,N1, etc.
Thanks

"Ron Coderre" wrote:

Try something like this:

B1:
=INDEX(A1:A1000,MAX(INDEX((MOD(ROW(A1:A1000),5)=0) *ISNUMBER(A1:A1000)*ROW(A1:A1000),0)))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Ron Weaver" wrote:

Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have
Excel find the last non blank cell in the group? These are numeric values
that update a report weekly.
Thanks

Last non blank cell in selected cells
 

The formula I suggested does what you want.

Biff

"Ron Weaver" wrote in message
...
I will try to make this a little more clear. On week one I will put a
figure
in A1. I want this figure to show in B1. On week two I will put a figure
in
A5. Now I want this figure to show in B1. On week three I will put a
figure
in A10. Now I want this figure to show in B1, etc. To complicate things,
there will be other values in A2,A3,A4,A6,A7,A8,A9.

"T. Valko" wrote:

=IF(COUNT(A1:A20)<5,A1,MAX(IF(MOD(ROW(A1:A20),5)=0 ,A1:A20)))

That returns the max value from the specified cells, not the last value
from
the specified cells. Also, what if A10 is the only entry in the range?

Biff

"Teethless mama" wrote in
message
...
=IF(COUNT(A1:A20)<5,A1,MAX(IF(MOD(ROW(A1:A20),5)=0 ,A1:A20)))

ctrl+shift+enter, not just enter


"Ron Weaver" wrote:

Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and
have
Excel find the last non blank cell in the group? These are numeric
values
that update a report weekly.
Thanks

Last non blank cell in selected cells
 

This works ! Thanks for your help.

"Ron Coderre" wrote:

OK....
But, with one caveat:
The formula can't be in Row_1 or you'll have a circular ref.

So
B2:
=INDEX(1:1,MAX(INDEX((MOD(COLUMN(1:1),5)=0)*ISNUMB ER(1:1)*COLUMN(1:1),0,0),1))

BTW: Same issue for the formula referencing Col_A...
It can't be in Col_A and...
If the formula will be in a row other than Row_1
Use this version:
=INDEX(A1:A1000,MAX(INDEX((MOD(ROW(A1:A1000),5)=0) *ISNUMBER(A1:A1000)*ROW(A1:A1000),0),1))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Weaver" wrote:

That works perfectly. Now, what if I want to do the same thing across:
Formula in B1
Values in A1,E1,J1,N1, etc.
Thanks

"Ron Coderre" wrote:

Try something like this:

B1:
=INDEX(A1:A1000,MAX(INDEX((MOD(ROW(A1:A1000),5)=0) *ISNUMBER(A1:A1000)*ROW(A1:A1000),0)))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Ron Weaver" wrote:

Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have
Excel find the last non blank cell in the group? These are numeric values
that update a report weekly.
Thanks

Last non blank cell in selected cells
 

Thanks for the feedback, Ron.....
I'm glad I could help on that one (plus, it was fun to figure out)


***********
Regards,
Ron

XL2002, WinXP


"Ron Weaver" wrote:

This works ! Thanks for your help.

"Ron Coderre" wrote:

OK....
But, with one caveat:
The formula can't be in Row_1 or you'll have a circular ref.

So
B2:
=INDEX(1:1,MAX(INDEX((MOD(COLUMN(1:1),5)=0)*ISNUMB ER(1:1)*COLUMN(1:1),0,0),1))

BTW: Same issue for the formula referencing Col_A...
It can't be in Col_A and...
If the formula will be in a row other than Row_1
Use this version:
=INDEX(A1:A1000,MAX(INDEX((MOD(ROW(A1:A1000),5)=0) *ISNUMBER(A1:A1000)*ROW(A1:A1000),0),1))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Weaver" wrote:

That works perfectly. Now, what if I want to do the same thing across:
Formula in B1
Values in A1,E1,J1,N1, etc.
Thanks

"Ron Coderre" wrote:

Try something like this:

B1:
=INDEX(A1:A1000,MAX(INDEX((MOD(ROW(A1:A1000),5)=0) *ISNUMBER(A1:A1000)*ROW(A1:A1000),0)))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Ron Weaver" wrote:

Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have
Excel find the last non blank cell in the group? These are numeric values
that update a report weekly.
Thanks

Last non blank cell in selected cells
 

Control+shift+enter:

=LOOKUP(9.99999999999999E+307,IF(MOD(COLUMN(A1:Z1)-COLUMN(A1),5)=0,IF(ISNUMBER(A1:Z1),A1:Z1)))

Ron Weaver wrote:
That works perfectly. Now, what if I want to do the same thing across:
Formula in B1
Values in A1,E1,J1,N1, etc.
Thanks

"Ron Coderre" wrote:

Try something like this:

B1:
=INDEX(A1:A1000,MAX(INDEX((MOD(ROW(A1:A1000),5)=0) *ISNUMBER(A1:A1000)*ROW(A1:A1000),0)))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Ron Weaver" wrote:

Is it possible to specify a group of cells like A1,A5,A10,A15,A20, and have
Excel find the last non blank cell in the group? These are numeric values
that update a report weekly.
Thanks