Lasts Non Blank Cell

[billisnice]

I used

{=MATCH(1,--(A1:A1000=""),0)-1}

to calculate the number of rows in A till Blank.

My question is how to add it to B1:B? where ? is the calculated formula above. If the formula above returns 40 I would like to B1:B40

Thanks

[shg]

{=b1:index(b:b, match(true, a1:a1000="", 0) -1)}

[mehmetcik]

This should work for numbers or text in column A

Please Login or Register  to view this content.

This sums the returned range

Please Login or Register  to view this content.

[billisnice]

I still am having trouble

I want everywhere there is a $B$4 to be $B:Lasts Non Blank Cell calculated


=OFFSET($B$1,MOD(ROW()-ROW($G$1),ROWS($B$1:$B$4)),TRUNC((ROW()-ROW($G$1))/ROWS($B$1:$B$4)),1,1)

[AlKey]

not quite sure I understand what you need but maybe this:

use this formula for numeric values

="B:"&MATCH(10^308,B:B)

for text use this one

="B:"&MATCH(REPT("z",255),B:B)

Data Range

	B	C	D
1	1		B:10
2	45
3	3
4	0
5	45
6	3
7	0
8	5
9	0
10	0

[billisnice]

Where do I put it in my formula? How do I replace the 4? I need to double it by 2.

=OFFSET($B$1,MOD(ROW()-ROW($G$1),ROWS($B$1:$B$4)),TRUNC((ROW()-ROW($G$1))/ROWS($B$1:$B$4)),1,1)4