## Display the Last Filled Cell from Rows in Column

About cells and acts on them (selection, naming, filling, moving, etc.) I wrote already on this website - tutorials (see the content of topics on this site). In this tutorial, I will show how we can show which should result data from the past (last) filled cells was that the number, value or text.

In this example, the picture below you can spot two ways of

The first way I how to

=INDEX(A1:A15;MAX(IF(LEN(A1:A15)>0;ROW(A1:A15);0));1)

This ARRAY formula ignores blank cells (unfilled cells). So you see that this formula in cell B2 returned result from the last filled cell in column 'A' (Google).

**example 1**In this example, the picture below you can spot two ways of

**displaying data from the last filled cell**in a certain Column.The first way I how to

**display data from the last filled cell in column**'A'. The data contained in the column can be values, numbers, text, symbols and the like. Using the ARRAY formula below we can show that the information is to filled in the last cell in column 'A'. The formula you finish with**Ctrl+Shift+Enter**=INDEX(A1:A15;MAX(IF(LEN(A1:A15)>0;ROW(A1:A15);0));1)

This ARRAY formula ignores blank cells (unfilled cells). So you see that this formula in cell B2 returned result from the last filled cell in column 'A' (Google).

Another way is to return as a result of the value or number in mixed data contained in the column. Look at the picture above and the column 'D'. In cell E5 is of the following formula. This formula returns the number or value that we entered in the last column. The formula ignores the text.

=OFFSET(D5;MATCH(1E+306;D5:D18;1)-1;0)

=LOOKUP(99^99;D5:D18)

=LOOKUP(MAX(D5:D18)+1;D5:D18)

=LOOKUP(REPT("Z";255);A2:A15)

=LOOKUP(2;1/(D5:D18<>"");D5:D18)

=OFFSET(D5;MATCH(1E+306;D5:D18;1)-1;0)

## Display the last number in the column - the last filled cell with numerical values

If you want to display last filled out cell, which contains a number or value you use this formula below.=LOOKUP(99^99;D5:D18)

=LOOKUP(MAX(D5:D18)+1;D5:D18)

## Display text from the last filled cell in column

If you have a column that contains text and you want to return the text of the last filled cell then use this formula below=LOOKUP(REPT("Z";255);A2:A15)

## Return the last filled cell regardless of the type of data

If you are interested in copying, or display data from the last filled or non-empty cell, regardless of the type of data (text or number or value) then use the formula below=LOOKUP(2;1/(D5:D18<>"");D5:D18)

## Display data from the last non-empty cell in column from several other worksheets

**example 2**

If you have a need to copy the last digit entered or values from another worksheet from that column then use the formula below. In the picture below you see the situation. On the worksheet 'Master' we have in column 'A' names worksheets used in the formula. In the column 'B' is the result of a formula, which

**displays the last filled cell**with a specific worksheet.

The formula for

=OFFSET(INDIRECT(A1&"!"&"$A$1");MATCH(1E+306;INDIRECT(A1&"!"&"$A$1"&":$A$41");1)-1;0)

Note that the formula used in the worksheet name in column 'A' which is integrated into the formula. This formula set in cell 'B1' on the worksheet 'Master' and then copy down.

**copying data from the last filled cell**for that column from multiple worksheets is.=OFFSET(INDIRECT(A1&"!"&"$A$1");MATCH(1E+306;INDIRECT(A1&"!"&"$A$1"&":$A$41");1)-1;0)

Note that the formula used in the worksheet name in column 'A' which is integrated into the formula. This formula set in cell 'B1' on the worksheet 'Master' and then copy down.

## Return data from the last the filled worksheet from a certain cell

**example 3**

The situation in the picture below you see 'Master' worksheet on which we need display data from the cell 'A1' from other worksheets, but so the formula returns the

**last filled cell of a worksheet**where the data is located. The calculation includes all five worksheets. In this example, the worksheet 'Sheet4' cell 'A1' contains data ie number 400

If you have a need as the situation below then use this formula as shown in the 'B1'

=INDIRECT("Sheet"&COUNTA(Sheet1:Sheet5!A1)&"!A1")

## Display the last date for the two conditions

**example 4**

Let us take a situation where

**we need to go back last date if we have two conditions**as shown below. Also, you notice that the conditions are not unique data which further complicates the search results, therefore, appear to be duplicated. In the picture below you see the data.

- Card issued product

- A product that is issued from storage

- Date of issue of products from warehouses

In cells C13 and C14 notice two conditions (blue font)

In cell C15 is ARRAY formula that you need finish with

**Ctrl+Shift+Enter**

=MAX((A2:A11=C13)*(B2:B11=C14)*(C2:C11))

## Copy data or value from each of the fifth column in the same row

**example 5**

In a situation where

**we need to go back or display data from each cell of the fifth (5th) from the same row**or data from each of the fifth column in the same row then we can use the formula below. Note the formulas that return data from

**every 5th cell**in the same row as well as formulas that

**return data from the last filled column in the same row**.

The formula in Cell C5:

=IF(OFFSET($A2;;COLUMN(A:A)*5+1)=0;"";OFFSET($A2;;COLUMN(A:A)*5+1))

The formula in Cell C6:

=IF(OFFSET($A3;;COLUMN(A:A)*5+1)=0;"";OFFSET($A3;;COLUMN(A:A)*5+1))

ARRAY formula in Cell C8: =INDEX(5:5;LARGE((5:5<>"")*(COLUMN(5:5));1))

ARRAY formula in Cell C9: =INDEX(6:6;LARGE((6:6<>"")*(COLUMN(6:6));1))

Instead of the formulas in cells C8 and C9, we can use the following formula

The formula in Cell C11: =OFFSET(A5;0;MATCH(MAX(A5:W5)+1;A5:W5;1)-1)

The formula in Cell C12: =OFFSET(A6;0;MATCH(MAX(A6:W6)+1;A6:W6;1)-1)

=IF(OFFSET($A2;;COLUMN(A:A)*5+1)=0;"";OFFSET($A2;;COLUMN(A:A)*5+1))

The formula in Cell C6:

=IF(OFFSET($A3;;COLUMN(A:A)*5+1)=0;"";OFFSET($A3;;COLUMN(A:A)*5+1))

ARRAY formula in Cell C8: =INDEX(5:5;LARGE((5:5<>"")*(COLUMN(5:5));1))

ARRAY formula in Cell C9: =INDEX(6:6;LARGE((6:6<>"")*(COLUMN(6:6));1))

Instead of the formulas in cells C8 and C9, we can use the following formula

The formula in Cell C11: =OFFSET(A5;0;MATCH(MAX(A5:W5)+1;A5:W5;1)-1)

The formula in Cell C12: =OFFSET(A6;0;MATCH(MAX(A6:W6)+1;A6:W6;1)-1)