Last non blank cell in selected cells

[Ron Weaver]

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

 

[T. Valko]

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

 

[Teethless mama]

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

ctrl+shift+enter, not just enter

 

[T. Valko]

=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

 

[Ron Weaver]

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.

 

[Ron Coderre]

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]

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]

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)*ISNUMBER(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

 

[T. Valko]

The formula I suggested does what you want.

Biff

 

[Ron Weaver]

This works ! Thanks for your help.

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)*ISNUMBER(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 Coderre]

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

 

[Aladin Akyurek]

Control+shift+enter:

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