Linking to Last Row

Brabed

Board Regular
Joined
Oct 27, 2005
Messages
54
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.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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)
 
Upvote 0
This will give you the last row number in a column:

=INDEX(A:A,MATCH(9.99999999999999E307,A:A))
 
Upvote 0
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. :biggrin: Besides if you don't know about dynamic named ranges then you are learning about a very useful tool.
 
Upvote 0
babycody said:
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. :biggrin: 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. :devilish: 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.
 
Upvote 0
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) .
 
Upvote 0
babycody said:
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.
 
Upvote 0

Forum statistics

Threads
1,216,172
Messages
6,129,291
Members
449,498
Latest member
Lee_ray

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top