• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

How do I reference the last row in a table for a formula?

Hello experts,

See attached example.  I want to develop a formula that selects all rows of data when the last row varies.  How do I do that?

Thanks, Gary
Training-History-Log---Dev-EE.xls
0
garyrobbins
Asked:
garyrobbins
3 Solutions
 
regmigrantCommented:
the best way is to use a formula that counts the rows in a named range theres an example here
http://support.microsoft.com/kb/830287

0
 
SANTABABYCommented:
Do you have other data in H1...H5 that you want to ignore?
Otherwise, you can use H:H in place of H6:H16

Thanks.
0
 
barry houdiniCommented:
What do you want to do with the formula?

If you want a count or sum with a single criterion and you can use SUMIF or COUNTIF then usually it's easier to use the whole column. Excel automatically only uses the "used range" which means that this is still efficient......but with SUMPRODUCT or array formulas it's best to do what regmigrant says and define a "dynamic range". You could use that directly in the formula but that makes the formulas overly long

regards, barry
0
 
dlmilleCommented:
Since Sumproduct is an array function already, there is NO NEED to hit CTRL-SHIFT-ENTER to make it into an array function (only if you have functions that require that, that are embedded in Sumproduct like MAX or COUNT or IF, whatever, would that be needed).

This is your dynamic range :
=THL!$H$6:INDEX(THL!$H:$H,MATCH(99^99,THL!$H:$H))

You could create a range name like "mySumRange" and then do

=SumProduct(mySumRange)

Otherwise, you should be able to do:

=SUMPRODUCT(THL!$H$6:INDEX(THL!$H:$H,MATCH(99^99,THL!$H:$H)))


Note the dynamic range is built around numeric values.  If the values are text, then replace the 99^99 with rept("z",20) and it will work for text ranges

See attached.

Enjoy!

Dave
Dave
Training-History-Log---Dev-EE-1-.xls
0
 
garyrobbinsAuthor Commented:
Thank you all for your prompt replies.  I appreciate learning about the dynamic range feature.

Hope you find my point allocation equitable.

I love EE...

Gary
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now