Need help finding an Excel Formula

Try this formula (in cell B1):

=INDIRECT("R1" & "C" & 2+COUNTA(C1:AZ1),FALSE)

This will count the number of non-empty cells in the range C1 to AZ1 (make this larger if needed), add 2 (since it starts at column C) and create an R1C1 reference to the last non-empty cell. The reference is then resolved using Indirect().

If you want to be able to copy this formula down the column, use this version:

=INDIRECT("R" & ROW(B1) & "C" & 2+COUNTA(C1:AZ1),FALSE)

This has Row(B1) instead of a hardcoded 1. When you copy this down, the cell in Row() will change, so it will always refer to the current row.

One thing - this will only work if you enter prices in continuous columns. If you skip a column, counta+2 won't be the same as the last column in the range.

Hope this helps.

The only other way I know is to use a custom function.

From Excel, Select Tools -> Macro -> Visual Basic Editor

Press Ctrl+R to open the Projects Window

Select your workbook in the Projects Window and from the main menu select Insert -> Module

Double Click on the new module to open it

In the code window, paste this code:

Public Function GetLastValue(arLastCell As Range)
Dim lOffset As Long

'--make sure this function gets recalced when the workbook recalcs
Application.Volatile

lOffset = 0
Do Until arLastCell.Offset(0, lOffset).Value > 0
lOffset = lOffset - 1
Loop
GetLastValue = arLastCell.Offset(0, lOffset).Value

End Function

To use the function, enter (in cell B1):

=GetLastValue(AZ1)

Pass it the last cell you want to check for values.

You should be able to copy this down the column, but if there is not any data to the right of the cell, you might get a circular reference error since the last cell with a value will be the cell the formula is in.

If you have a large workbook, you can remove the line Application.Volatile from the function and recalc manually (F9).

Hello Matt,

1) Formula or (user defined) function would be a little difficult because the calculation requires cell navigation which formula or function can't do.
2) You can however have a procedure and assign a keyboard shortcut to it. You haveto run this procedure for every calculation you do. You also need some rules to follow:
a) The top row should have labels and every column must have a labels. These labels should be contiguous with no intermittent blanks.
b) The cells that containno data must be 'null', not even spaces (which may be typed un-intentionally).

Following procedure will work if the above two conditions are ensured.

Sub FindLatestPrice()
'
' FindLatestPrice Macro
' Macro created on 14/05/2001 by SanganakSakha

Dim intCurRo As Integer
Dim intCurCol As Integer
Dim intTopRo As Integer
intCurRo = ActiveCell.Row
intCurCol = ActiveCell.Column
Selection.End(xlUp).Select
intTopRo = Selection.Row
Selection.End(xlToRight).Select
Selection.Offset(intCurRo - intTopRo, 1).Select
Selection.End(xlToLeft).Select
Cells(intCurRo, intCurCol + 1) = Selection.Value
Cells(intCurRo, intCurCol).Select
End Sub

I have successfully tried this code and would be happy to help you successfully implement this code.

Your feedback would be highly appreciated.

With best regards,

SanganakSakha,
Your Friend In Need.

Ok give me some time to check it out to see if it works I should be able to get back to u all tomorrow night.

Here?s a simple formula. Just copy it down the column.

=OFFSET(B1,0,(COUNTA(C1:IV1)))

This question was auto closed due to inactivity