Find the last row in a worksheet

[tuph]

I have tried searching for the answer to this question but the search engine ignores "last" and "row" as being too common or too short!

I need a formula in my Analysis worksheet that will reference column G in the Sales worksheet and return the value from the last cell in the column that contains data. Sales worksheet contains a list of product sales with a total row at the bottom. The row number for the total will change depending on the number of products sold in a given month. (Sample attached.)

Thanks in anticipation!

[corinereyes]

Hi tuph,

I 'am just trying.

Please Login or Register  to view this content.

[broro183]

Hi Trish,

Corinereye's one looks good as it is & I borrowed the idea to give a couple of alternative formula options:

* =INDEX(Sales!$G:$G,MATCH("TOTAL",Sales!$A:$A,0),1)
* =VLOOKUP("TOTAL",Sales!$A:$G,7,FALSE)
(For these it may better to limit the lookup ranges but it's probably not too important in a file this size.)

Or use a named range by:
Firstly, selecting the cell on the sales sheet (G106), press [alt + i + n + d], type a name eg "TotalPercentageVar", [add], check that the "refers to" reference is absolute (ie "=Sales!$G$106"), [ok]
Secondly, selecting the cell on the Analysis sheet, type "=", press [alt + i + n + p], select the name you've just created, [ok] & [enter] to complete the formula.
Note: the named cell will only work if the named range (&/or the actual cell does not get deleted.

Some suggestions which are not specific to your question but may make it irrelevant...
To prevent the cell being deleted & also to make the Sales sheet more user friendly, I suggest:
1) moving the totals to the top of the sheet (eg row 5) - this way the total won't be affected by the number of data rows b/c it doesn't move.
& then
2) scrolling to the top left of the sheet, selecting cell A6, pressing [alt + w + f] to freeze the pane so that the header rows are always visible (no matter how far down you scroll).

hth
Rob

[Richard Schollar]

In a similar vein, but 1 less function call:

=LOOKUP(9.999E+307,Sales!G:G)

Richard

[VBA Noob]

See link for other suggestions

http://www.xldynamic.com/source/xld.LastValue.html

VBA Noob

[tuph]

Wow! Thanks everyone for the time you took to give me such great responses. I like the =VLOOKUP("TOTAL",Sales!$A:$G,7,FALSE) formula (and should have thought of it myself!).

Thanks again. Problem solved!