Displaying last filled cell before a blank cell

[30percent]

Hi,

I have a list of data on Sheet 1, on column B. I wonder how do I display the last filled cell on column B BEFORE A BLANK CELL onto another cell say Sheet 2, cell B2.

Thank you!!

 

[Matt Rogers]

Hi 30percent.

Is it ok?

Excel Workbook
	B	C
1	10	40
2	20
3	30
4	40
5
Sheet

greeting

 

[T. Valko]

Hi,

I have a list of data on Sheet 1, on column B. I wonder how do I display the last filled cell on column B BEFORE A BLANK CELL onto another cell say Sheet 2, cell B2.

Thank you!!

There are several ways to do this. The best solution depends on what type of data is in the range.

Is the data in the range:

text
numbers
dates
times
logical values like TRUE and/or FALSE

Are there any formulas in the range?
Do any of these formulas return formula blanks (="")?
Do any of these formulas return errors?
Could the data in the range be all of the above?

A generic method...

Book1
	B	C	D
2	12	_	14
3	17	_	_
4	37	_	_
5	_	_	_
6	_	_	_
7	_	_	_
8	91	_	_
9	83	_	_
10	Text	_	_
11	96	_	_
12	51	_	_
13	Data	_	_
14	42	_	_
15	33	_	_
16	64	_	_
17	_	_	_
18	_	_	_
19	7	_	_
20	14	_	_
Sheet1

This array formula** entered in D2:

=INDEX(B:B,MAX(IF(B2:B20<>"",ROW(B2:B20))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[30percent]

Hi,

I have the data list on sheet 1, and I would like to display it on a cell on sheet 2. I modified the formula to factor it in.

I pressed on CTRL, Shift and Enter on the cell I want to display and paste the following formula:
=INDEX(Sheet1!B3:B24,MAX((Sheet1!B3:B24<>"")*ROW(1:1000)))

However I have this displayed on the cell #VALUE!

Please advise

 

[Matt Rogers]

Hi.

sheet 1

Is it
Sheet 1 or Sheet1 or sheet 1 or sheet1 ?

 

[30percent]

Hi.

Is it
Sheet 1 or Sheet1 or sheet 1 or sheet1 ?

It is Sheet1

 

[T. Valko]

It is Sheet1

Try this array formula**:

=INDEX(Sheet1!B:B,MAX(IF(Sheet1!B3:B24<>"",ROW(Sheet1!B3:B24))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[30percent]

Hi,
sorry, It still doesn't work.

I copied the following formula:
=INDEX(Sheet1!B:B,MAX(IF(Sheet1!B3:B24<>"",ROW(Sheet1!B3:B24))))

Pressed on CTRL, Shift and Enter keys.

I right clicked on my mouse and pasted the formula.

I ended up getting #VALUE!

Please advise, thank you!

 

[T. Valko]

Hi,
sorry, It still doesn't work.

I copied the following formula:
=INDEX(Sheet1!B:B,MAX(IF(Sheet1!B3:B24<>"",ROW(Sheet1!B3:B24))))

Pressed on CTRL, Shift and Enter keys.

I right clicked on my mouse and pasted the formula.

I ended up getting #VALUE!

Please advise, thank you!

Here's a small sample file that demonstrates this.

zzz30percent.xls 14kb

http://cjoint.com/?AIDxLvua8YQ

 

[Matt Rogers]

Hi 30percent.

Pressed on CTRL, Shift and Enter keys.
I right clicked on my mouse and pasted the formula.

First: I right clicked on my mouse and pasted the formula.
And then: Pressed on CTRL, Shift and Enter keys.
(in formula-editor)