Linking to Last Row

[Brabed]

I am looking for Cell A1 on worksheet 1 to show the results of a calculation of Cell A100 in worksheet 2, which is the bottom row of data. Problem is, data will be added tomorrow, so I will want A1 on Worksheet 1 to now show the results of the refreshed data (A101). How do I get it to automatically link to the last (bottom) row of data? Many thanks.

 

[Joe4]

Enter this formula in cell A1 to return the value in the last populated cell in column A on sheet 2:

=LOOKUP(9.99999999999999E+307,Sheet2!A:A)

 

[Legacy 51064]

This will give you the last row number in a column:

=INDEX(A:A,MATCH(9.99999999999999E307,A:A))

 

[Brabed]

Thank you, I will give it a try.

 

[babycody]

I would create a dynamic named range named MyRange. Do this by going to Insert>Name>Define and pasting =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1) in the refers to: box. At the top name the range MyRange and click add. Now go back to Sheet1 cell A1 and paste this formula =OFFSET(Sheet2!A1,COUNTA(MyRange)-1,0)

There are other ways to do this, but I can never remember all those 9s. Besides if you don't know about dynamic named ranges then you are learning about a very useful tool.

 

[Aladin Akyurek]

I would create a dynamic named range named MyRange. Do this by going to Insert>Name>Define and pasting =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1) in the refers to: box. At the top name the range MyRange and click add. Now go back to Sheet1 cell A1 and paste this formula =OFFSET(Sheet2!A1,COUNTA(MyRange)-1,0)

There are other ways to do this, but I can never remember all those 9s. Besides if you don't know about dynamic named ranges then you are learning about a very useful tool.

No need for such a setup if one just needs the last value. That you can't remember the number of 9's doesn't justify much here. Moreover, you have still to determine the last value:

=LOOKUP(9.99999999999999E+307,Sheet2!A:A)

is better than what you'll need:

=LOOKUP(9.99999999999999E+307,MyRange)

for fetching the last numerical value.

BTW, you might also get into trouble with COUNTA if there are empty cells between the first and the last value.

 

[babycody]

I will always bow to your expertise Aladin. You are far better at this than me. I did test this idea before I posted it however. A1 on sheet1 returned the value of the last cell in the range MyRange when I tested it. As I added more information down the rows the value shown in Sheet1 A1 changed to match the last value. You are correct (as always) in pointing out that a blank space disrupts everything. This was something I had not considered. I wasn't trying to say not to use the 9s. I was trying to use my own immagination to help solve the problem. I have received so much help here that I try on occasion to do the same for others. I had conceived this idea, and thought it was something new to consider. I didn't see the flaws in my approach that you as a more experienced Excel user could more readily see. Next time I post an original technique I will ask the other members their opinions about the approach I used to achieve the solution. I will also state that the method is untested. I know that you put a lot of thought into this formula =LOOKUP(9.99999999999999E+307,Sheet2!A:A) .

 

[Aladin Akyurek]

I will always bow to your expertise Aladin. You are far better at this than me. I did test this idea before I posted it however. A1 on sheet1 returned the value of the last cell in the range MyRange when I tested it. As I added more information down the rows the value shown in Sheet1 A1 changed to match the last value. You are correct (as always) in pointing out that a blank space disrupts everything. This was something I had not considered. I wasn't trying to say not to use the 9s. I was trying to use my own immagination to help solve the problem. I have received so much help here that I try on occasion to do the same for others. I had conceived this idea, and thought it was something new to consider. I didn't see the flaws in my approach that you as a more experienced Excel user could more readily see. Next time I post an original technique I will ask the other members their opinions about the approach I used to achieve the solution. I will also state that the method is untested. I know that you put a lot of thought into this formula =LOOKUP(9.99999999999999E+307,Sheet2!A:A) .

There is no need to be apologetic...

Setting up a dynamic named range with:

[1]

=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)

requires an additional formula to retrieve the last value the OP wants. And you provided:

[2]

=OFFSET(Sheet2!A1,COUNTA(MyRange)-1,0)

Even if you created these two formulas yourself (that's simply great), they are not new (i.e., they are standard, well-known idioms).

You could also have forwarded just the last one (something that is quite similar anyway and something that you already know)...

[3]

=OFFSET(Sheet2!A1,COUNTA(Sheet2!$A:$A)-1,0)

Here a review:

A)

[1] and [2] together are too expensive to invoke for just retrieving the last value.

B)

[3] alone is also expensive compared with other idioms which are available.

C)

COUNTA is impervious to empty cells: The OFFSET formulas invoking this function might potentially calculate wrong results.

D)

[1] and [2] together or [3] alone would return any last value, if not thwarted by empty cells. Alternative idioms, although very fast, are specific to the data type of the last value one needs.