Excel 2002: How to get the first and last reference in a column ?
 

Hi,

Let consider the table below:

A B
8
9
10 R216
11 R217
12 R218
13 R220
250 R368
500
1000

I need two formulas at cell A8 and B8 respectively to pick up the first and
last refrence in column A.

The number of rows may differs from time to time upon pasting of data to
this column.

Thanks

Low





--
A36B58K641

Excel 2002: How to get the first and last reference in a column ?
 

"Mr. Low" wrote in message
...
Hi,

Let consider the table below:

A B
8
9
10 R216
11 R217
12 R218
13 R220
250 R368
500
1000

I need two formulas at cell A8 and B8 respectively to pick up the first
and
last refrence in column A.


As long as there are no gaps in the column you could write a macro and
either assign it to a button that has to be pressed or write it for the
sheet and execute every time there is a change to any cell in column A:

Sub Macro1()
Range("A9").Select
Selection.End(xlDown).Select
Selection.Copy
Range("A8").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
Selection.Copy
Range("B8").Select
ActiveSheet.Paste
End Sub

This assumes that the starting point is below cell A9 but not known.

Excel 2002: How to get the first and last reference in a column ?
 

If the data is text as it appears to be in your sample:

For the first item:

=INDEX(A10:A1000,MATCH("*",A10:A1000,0))

For the last item:

=LOOKUP("zzzzzzzzzz",A10:A1000)

Or:

=LOOKUP(REPT("z",255),A10:A1000)

--
Biff
Microsoft Excel MVP


"Mr. Low" wrote in message
...
Hi,

Let consider the table below:

A B
8
9
10 R216
11 R217
12 R218
13 R220
250 R368
500
1000

I need two formulas at cell A8 and B8 respectively to pick up the first
and
last refrence in column A.

The number of rows may differs from time to time upon pasting of data to
this column.

Thanks

Low





--
A36B58K641

Excel 2002: How to get the first and last reference in a column ?
 

Hi,

The following returns the last non-blank cell

=LOOKUP(1,1/(A:A<""),A:A)

You already have the best method for finding the first nonblank cell.

If this helps, please click the Yes button

cheers,
Shane Devenshire

"Mr. Low" wrote:

Hi,

Let consider the table below:

A B
8
9
10 R216
11 R217
12 R218
13 R220
250 R368
500
1000

I need two formulas at cell A8 and B8 respectively to pick up the first and
last refrence in column A.

The number of rows may differs from time to time upon pasting of data to
this column.

Thanks

Low





--
A36B58K641

Excel 2002: How to get the first and last reference in a column ?
 

=LOOKUP(1,1/(A:A<""),A:A)

You can't use the entire column in the array in versions of Excel prior to
Excel 2007.

=LOOKUP(1,1/(A1:A65535<""),A:A)


--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

The following returns the last non-blank cell

=LOOKUP(1,1/(A:A<""),A:A)

You already have the best method for finding the first nonblank cell.

If this helps, please click the Yes button

cheers,
Shane Devenshire

"Mr. Low" wrote:

Hi,

Let consider the table below:

A B
8
9
10 R216
11 R217
12 R218
13 R220
250 R368
500
1000

I need two formulas at cell A8 and B8 respectively to pick up the first
and
last refrence in column A.

The number of rows may differs from time to time upon pasting of data to
this column.

Thanks

Low





--
A36B58K641

Excel 2002: How to get the first and last reference in a colum
 

Hi Valko,

Thanks for your formulas.

All works well.

Best Regards

Low
--
A36B58K641


"T. Valko" wrote:

If the data is text as it appears to be in your sample:

For the first item:

=INDEX(A10:A1000,MATCH("*",A10:A1000,0))

For the last item:

=LOOKUP("zzzzzzzzzz",A10:A1000)

Or:

=LOOKUP(REPT("z",255),A10:A1000)

--
Biff
Microsoft Excel MVP


"Mr. Low" wrote in message
...
Hi,

Let consider the table below:

A B
8
9
10 R216
11 R217
12 R218
13 R220
250 R368
500
1000

I need two formulas at cell A8 and B8 respectively to pick up the first
and
last refrence in column A.

The number of rows may differs from time to time upon pasting of data to
this column.

Thanks

Low





--
A36B58K641

Excel 2002: How to get the first and last reference in a colum
 

Hi Shane,

Thanks for the formula.

I tested it, however it was not working in Excel 2002 as T. Valko said.

I have modified the formula to =LOOKUP(1,1/(A1:A65535<""),A1:A65535 ) and
it works.

Thank you anyway for sharing with me.

Kind Regards

Low

--
A36B58K641


"Shane Devenshire" wrote:

Hi,

The following returns the last non-blank cell

=LOOKUP(1,1/(A:A<""),A:A)

You already have the best method for finding the first nonblank cell.

If this helps, please click the Yes button

cheers,
Shane Devenshire

"Mr. Low" wrote:

Hi,

Let consider the table below:

A B
8
9
10 R216
11 R217
12 R218
13 R220
250 R368
500
1000

I need two formulas at cell A8 and B8 respectively to pick up the first and
last refrence in column A.

The number of rows may differs from time to time upon pasting of data to
this column.

Thanks

Low





--
A36B58K641

Excel 2002: How to get the first and last reference in a colum
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Mr. Low" wrote in message
...
Hi Valko,

Thanks for your formulas.

All works well.

Best Regards

Low
--
A36B58K641


"T. Valko" wrote:

If the data is text as it appears to be in your sample:

For the first item:

=INDEX(A10:A1000,MATCH("*",A10:A1000,0))

For the last item:

=LOOKUP("zzzzzzzzzz",A10:A1000)

Or:

=LOOKUP(REPT("z",255),A10:A1000)

--
Biff
Microsoft Excel MVP


"Mr. Low" wrote in message
...
Hi,

Let consider the table below:

A B
8
9
10 R216
11 R217
12 R218
13 R220
250 R368
500
1000

I need two formulas at cell A8 and B8 respectively to pick up the
first
and
last refrence in column A.

The number of rows may differs from time to time upon pasting of data
to
this column.

Thanks

Low





--
A36B58K641