Finding last row in a column and extracting the data in the cell

[Coach316]

I need a formula that will find the last row of data in a worksheet and return the value of a cell in a particular column into another worksheet in the same workbook.

The worksheet is updated with new information daily so the number of rows will vary. But it will never be greater than 31 (most # days in any month). Also, certain columns are MTD running totals so formulas populate all of the cells in that column through day 31. I will need to use Column A (which contains the date/time data was entered) to establish my "last row".

For example: If the last row of information is in Sheet 1 is 12, I want to return the value in column C row 12 to a specific cell in Sheet 2.

Also, I am using Excel 2007 and I'm sharing the spreadsheet among multiple users on Google Docs and cannot use VBA code or a macro to resolve this problem.

 

[T. Valko]

I need a formula that will find the last row of data in a worksheet and return the value of a cell in a particular column into another worksheet in the same workbook.

The worksheet is updated with new information daily so the number of rows will vary. But it will never be greater than 31 (most # days in any month). Also, certain columns are MTD running totals so formulas populate all of the cells in that column through day 31. I will need to use Column A (which contains the date/time data was entered) to establish my "last row".

For example: If the last row of information is in Sheet 1 is 12, I want to return the value in column C row 12 to a specific cell in Sheet 2.

Also, I am using Excel 2007 and I'm sharing the spreadsheet among multiple users on Google Docs and cannot use VBA code or a macro to resolve this problem.

What type of data is entered in column A? Is it text? Numeric? Could be either?

 

[number kruncher]

You could try, using Counta to see which rows are 'used', eg.

=COUNTA(C:C)

 

[Coach316]

Column A is a timestamp of the date the spreadsheet is updated. So it is a date type in the format of: 3/29/2011 4:24:20

If that makes things difficult, I can use Column B (numerical data) to determine the last row used.

Cant use Counta....all that does is return the number of the last row used.

 

[T. Valko]

Column A is a timestamp of the date the spreadsheet is updated. So it is a date type in the format of: 3/29/2011 4:24:20

If that makes things difficult, I can use Column B (numerical data) to determine the last row used.

Cant use Counta....all that does is return the number of the last row used.

Ok, let's start with this and then go from there...

This formula will return the value from the cell that is in the last row and the 3rd column from the range A:G.

=INDEX(A:G,COUNTA(A:A),3)

Assumes that there are no empty/blank cells within the data range of column A.

 

[Coach316]

That works beautifully. There will never any empty cells as the information is being brought over from a form and all items are required to have an entry even if it is 0.

Now the trick to get that amount to populate in another worksheet in the same workbook I have: =INDEX(Sheet4!$A:$G,COUNTA(Sheet4!$A:$A),2)

It works, but I want to make sure that it looks sound to you.

 

[T. Valko]

That works beautifully. There will never any empty cells as the information is being brought over from a form and all items are required to have an entry even if it is 0.

Now the trick to get that amount to populate in another worksheet in the same workbook I have: =INDEX(Sheet4!$A:$G,COUNTA(Sheet4!$A:$A),2)

It works, but I want to make sure that it looks sound to you.

The syntax is correct.

That formula is getting the result from column B of Sheet4.

 

[Coach316]

The syntax is correct.

That formula is getting the result from column B of Sheet4.

Two thumbs up then! Thanks for all your help....Cheers!