0

I want a function that refers to the last entered cell in a particular column. For example, consider column A and the entries are as follows.

1st entry in column is in cell A1, 2nd in A2, 3rd in A5, 4th in A8, 5th in A7.

Now i want a function that refers to the cell A7.

leppie
  • 115,091
  • 17
  • 196
  • 297
Krish Mahi
  • 11
  • 2
  • 1
    What programming language are you using? Is it a web page? Because in most programming languages when defining a table you need to specify the dimensions of the table/matrix. Thus you would know the ''last'' column. – Pio May 04 '14 at 10:10
  • check this: http://stackoverflow.com/questions/5441885/last-non-empty-cell-in-a-column/22192270#22192270 – Dmitry Pavliv May 04 '14 at 10:28
  • I donot know anything about the programming language. But, in the link posted by you, gives the last cell in that column ignoring the blank cells. But what i need is, the last entered cell to be displayed. For example, if my 1st entry is in A8 and last is in A6, i need A6 to be displayed as that is the last entered cell. – Krish Mahi May 04 '14 at 17:09

2 Answers2

0

With data in column A use

=LOOKUP(2,1/(A:A>0),A:A)

For example:

snap

Will display Albert

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • What i need is, the last entered cell to be displayed. For example, if Albert is entered in A7 followed by George in A6. I need George to get displayed. – Krish Mahi May 04 '14 at 17:11
0

Here is the macro approach. The macro monitors changes made to column A and records the last value in cell B1

Insert this event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A As Range, B As Range
    Set A = Range("A:A")
    Set B = Range("B1")
    If Intersect(A, Target) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        B = Target.Value
    Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

  1. right-click the tab name near the bottom of the Excel window
  2. select View Code - this brings up a VBE window
  3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE windows as above
  2. clear the code out
  3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Actually, i am using using single use dropdown list (each element in the dropdown list can be used only once. Once an element is used, it will be out of this list). After pasting the code, single use dropdown is not working. Even after selecting a value, it is being displayed in the dropdown. Suggest a solution for that – Krish Mahi May 04 '14 at 18:19
  • I even tried different codes for locking the cell that is once entered. but, once if I paste the code in the work sheet, single use dropdown is not working. So, I require either a function or a code that does not the single use of the dropdown. Awaiting for your reply – Krish Mahi May 05 '14 at 04:41