Last cell in a range

Can anyone tell me the way to display the latest entry into a range of cells 
in a downward entry
e.g
A1 3
A2 5
A3 6
A4 5


I would like a cell to report the last cell in the column regardless of 
whether it is the highest or lowest in the range.

Any help is as always Appreciated.

Andrew. 


0
me994 (146)
1/29/2007 9:37:20 PM
excel 39879 articles. 2 followers. Follow

2 Replies
919 Views

Similar Articles

[PageSpeed] 13

Andrew

Will return the last entry in column A no matter what type of data.

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)

For Numeric entry only..............

=LOOKUP(9.99999999999999E+307,A:A)

For Text entry only....................

=LOOKUP(REPT("z",255),A:A)


Gord Dibben  MS Excel MVP

On Mon, 29 Jan 2007 21:37:20 GMT, "@Homeonthecouch" <me@home.com> wrote:

>Can anyone tell me the way to display the latest entry into a range of cells 
>in a downward entry
>e.g
>A1 3
>A2 5
>A3 6
>A4 5
>
>
>I would like a cell to report the last cell in the column regardless of 
>whether it is the highest or lowest in the range.
>
>Any help is as always Appreciated.
>
>Andrew. 
>

0
Gord
1/29/2007 9:55:03 PM
Dear Gord

Well that worked a treat !

As always many thanks for your help.

Andrew

"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:fuqsr2pp22ql8bkqp46c1t6ltejdjiukji@4ax.com...
> Andrew
>
> Will return the last entry in column A no matter what type of data.
>
> =LOOKUP(2,1/(A1:A65535<>""),A1:A65535)
>
> For Numeric entry only..............
>
> =LOOKUP(9.99999999999999E+307,A:A)
>
> For Text entry only....................
>
> =LOOKUP(REPT("z",255),A:A)
>
>
> Gord Dibben  MS Excel MVP
>
> On Mon, 29 Jan 2007 21:37:20 GMT, "@Homeonthecouch" <me@home.com> wrote:
>
>>Can anyone tell me the way to display the latest entry into a range of 
>>cells
>>in a downward entry
>>e.g
>>A1 3
>>A2 5
>>A3 6
>>A4 5
>>
>>
>>I would like a cell to report the last cell in the column regardless of
>>whether it is the highest or lowest in the range.
>>
>>Any help is as always Appreciated.
>>
>>Andrew.
>>
> 


0
me994 (146)
1/29/2007 11:45:28 PM
Reply:

Similar Artilces:

changing many cells reference to absolute
I have a huge spread sheet and need to change the cells from relative to absolute reference. In other words the cell reads =A2 and I need to change it to =$A$2. I have hundreds of cells. Anyway to not do this manually,going into each one? Thanks Woody You would need VBA to make global changes to cell references. Here are four........ Sub Absolute() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlAbsolute) End If Next End Sub Sub AbsoluteRow() Dim cell As Range ...

Different Cell Sizes in the Same Column?
Is it possible to have different cell sizes in the same column If it is possible, how do you size the cells differently? All cells in a column have the same width (they can have different heights). Cells in adjacent columns can be merged to form a wider cell unit. This can cause problems with sorting/formatting/copy&pasting, so I rarely recommend it. In article <24D650A3-564F-440D-8926-480AF023D208@microsoft.com>, Zaebos <zaebos@kalvikmedia.com> wrote: > Is it possible to have different cell sizes in the same column? > > If it is possible, how do you size t...

How do I correct scroll bar range?
I have a worksheet where the scroll bar range is A through WVL where all I need is A through W. Is there a way to correct this? Debra Dalgleish shares a few ways here: http://contextures.com/xlfaqApp.html#Unused Tom Freeman wrote: > > I have a worksheet where the scroll bar range is A through WVL where all I > need is A through W. Is there a way to correct this? -- Dave Peterson Tom - If there is nothing in the spreadsheet to the right of column W, then highlight columns V through WVL and then Edit | Delete. Then Save the worksheet. When you open it, th...

Display ranges in a Pivot
Hi all, I have a column called IncomeRange. I need to display the value in ranges like 0 - 100, 101-200 etc., How can I convert a column in a pivot report to display as ranges? Is this an option or do I need to do some kind of formulas or macros? Thanks for all help Just wanted to explain a little more. Hello, My requirement is to create a pivot report in which a column called indexrange is to be displayed as n number of intervals eg: 0-9, 10-19 etc. The source of data for the pivot is a SQL Server query. I am not sure where or how can I do it, so I am posting this in both g...

Autofilling next empty cell in column?
Other than copying formulas manually to the next row of empty cells, can a function within the preceding data entry cell do the work upon completion, <enter>, or auto summing? All cells within the range will have entries, so no isolated empty cells are present to mess up the integrity. These are scientific, engineering functions with no limit to range of entries. Having searched through several manuals and this forum, I'm not too optimistic for a solution, but keep trying... Thanks, -- Bruce McC You can create a macro that does this. Otherwise, if you double click on th...

Formatting cells for worksheet data transfer
Sheet 1 contains two columns. Each column lists a hockey roster. Column A is the "White" team. Column B is the "Dark" team. There are 22 spots consisting of regulars and numerous substitutes. Any one player may appear on "White or "Dark" from week to week. At the bottom of the rosters will appear the score from a given nights game. On Sheet 2 is a list of all players (regulars and all subs) in column A. Column B is "Wins". Column C is "Losses". Column D is "Ties". My question is: how do I format this s...

EXCEL can't sum cell with SPACE " "
I have imported data from some text file to do sum in EXCEL, as it is always easy to SUM with EXCEL. But here I surprise EXCEL can't do that simple sum, B4 has value "339 " in cell seen as 339 B5 has value "2079 " in cell seen as 2079 Now i tried with =SUM(B4:B5), yield 0.000 I have checked what's wrong with in base cell, found excess space" � after last digit. I have billions of data to sum; I can't remove that space manually. i am attaching that file for your ref. i have also tried with TRIM() function but that also didn't work Any function o...

Concatenate text & number cell
Hi all, I need to concatenate a word and a number cell. eg, Concatenate("Limit = $",A1) where A1 = $100,000. It shows up like this: "Limit = $100000". However I want the number to format with the comma between the thousand, ie. "Limit = $100,000" Does any one know how to do this. Thanks in advance. regards, John. one way: ="Limit = " & TEXT(A1,"$#,##0") In article <2285c01c45d99$ff299070$a301280a@phx.gbl>, "John" <anonymous@discussions.microsoft.com> wrote: > Hi all, > I need to concatenate a wo...

Link cell to another workbook
Hi everyone, In Excel, how would I go about linking one cell to another cell in a different workbook? I understand I can type = then point it to the cell in the other workbook, however my problem is everytime the location of the file changes, the links mess up. For example, I have this command to link to a file called grid: =[grid.xlsx]Sheet1!$A$1 But when I want to move both the original file and the grid file to a different location (for example on a memory stick), the link fails and tries to point back to where the file was originally stored. Is it possible to use an ...

How to put Time or Date Only in Cell 1) Time HHMMSS 2) Date YYYYMMDD
I need to have a cell = the current time in the format HHMMSS And when I export as Comma Delimited or Fixed Length Ascii, I need for it to remain in that format. Ditto for Date as YYYYMMDD I've played around with both =TIME and =DATE but no luck. Can anyone tell me how to do this? AND, once the forumula is in the cell... how can I get it to update to the current time or date? thanks for any help I formatted my times as hhmmss and my dates as yyyymmdd and then did a file|saveAs. I chose the comma separated values (*.csv) and I got what you wanted. I opened it in notepad to ver...

Deleting #N/A from cells...
I have values in cells C1-C162. However, some of the cells have #N/A in them. Is there a quicker/better way to delete them all from the cells instead of highlighting them all and deleting them? Thanks =IF(ISNA(your_formula),"",your_formula) Regards Trevor "Jambruins" <Jambruins@discussions.microsoft.com> wrote in message news:FB5013B5-43CB-4E68-8589-82894CA6A6C5@microsoft.com... >I have values in cells C1-C162. However, some of the cells have #N/A in > them. Is there a quicker/better way to delete them all from the cells > instead of highlighting...

Display last row entered
Hi, on a large Spreadsheet, with Data added daily, I would like the last row of Data added to display on Row 1 at A1, B1 etc. Please advise on the best option, is it by using the Vlookup formula?? Cheers Paul -- tallpaul ------------------------------------------------------------------------ tallpaul's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=8453 View this thread: http://www.excelforum.com/showthread.php?threadid=496781 From my notes copied from another message I know that: =LOOKUP(9.99999999999999E+307,Sheet1!A:A) will return the last numeric value ...

Compare dates (one cell not in date format)
Hi all, I have one column of cells in date format (6/14/2005) and anothe column of cells which also contains a date pulled from a database bu this second column is not in date format, it comes from the databas like this 2005Jan20. I need to compare the two dates to work out th number of days between the dates but I can't convert the second date t date format. Is there any way I can work out the number of days between the tw dates? Thanks in advance Crai -- craigcs ----------------------------------------------------------------------- craigcsb's Profile: http://www.excelforum.com/...

Referenced cell displays formula, not data
I downloaded a form template and linked it to my data; when I use the = sign to insert my data into the template, some – though not all – cells display the formula of the cell reference rather than the data that is actually contained in the cell Example: Cell D10 in datasheet contains text; instead of displaying the text in the template, it shows the following formula that is the reference address: ='[data sheet.xlsx]'!$D$10 -- MZ Press CNTRL + TILDE Key whch is available above the Left Tab Key. Tilde key (`) or (~) Remember to Click Yes, if this post helps! ...

I want to type a text in a cell, but I want that text to represen.
I want to type text into a cell (actually the checkmark symbol in winding2) but I want that text to represent a number. So if I have 3 cells each with the checkmark and each of those cells represents the number 1 when I auto sum I will get a total of 3. If they represent 1 you can just count them with countif =COUNTIF(Range,"character") regards, Peo Sjoblom "ExcelQ" wrote: > I want to type text into a cell (actually the checkmark symbol in winding2) > but I want that text to represent a number. So if I have 3 cells each with > the checkmark and eac...

how to split cells?
Can anyone help me? -- wsijbesma ------------------------------------------------------------------------ wsijbesma's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35063 View this thread: http://www.excelforum.com/showthread.php?threadid=548078 hi on the tool bar... data>text to columns. follow the wizard. caution.... this deletes data to the right when the cell is split you can also use formulas. cell A1 has the word "text in it. in cell B1... =right(a1,2) will return "xt" in cell C1... =left(a1,2) will return "te" in cell D1....

Cells formated as text do not always display properly
I am using Office XP and Windows XP. I have formatted a column of cells as text and word wrap is checked, horizontal alignment is Left, Vertical alignment is Bottom. I use these cells to type a short narrative. The problem is some of the cells will not display properly, all they show is ######. The row height will expand to hold the text sometimes but not always but it will still only show ######## instead of the text I typed. If I change the cell type to General all the text is visible. This does not happed to evey cell, only some of them. I can type several rows of data with a narr...

Protect cells with formulas only
What is the best way to only protect cells with formulas in them? Many thanks -- John F Excel 2007 XPproSP2 Not sure if there is an easier way, but you can: *Select the entire sheet (you can do this by clicking on the icon above row 1 and to the left of column A) *Right click in the sheet and select FORMAT CELLS *Go to the Protection tab and UNCHECK LOCKED *Now, go back to your sheet, right click on the cell(s) you want to protect and again select format cells *Go to the protection tab and CHECK LOCKED *When you are done selecting the cells to be locked go to the ribbon and select t...

Locked cells are still able to be selected with proper protection
An excel worksheet I'm developing has rows that alternates between locked and unlocked cells. When I protect the worksheet with the option for users to select unlocked cells and format cells checked I'm still able to select and format locked cells by drag the cursor vertically from one unlocked cell to another. Is there a way to prevent this from happening? Any help would be appreciated! Hi Jesse Select the whole w/sheet < format < format cells < protection < uncheck then check locked < ok Select the areas you need to allow access Format < format cel...

Move to a specific cell on exit of current
I'd like to move to a specific cell on exit of the current cell. Can do this? Thanks -- jafo ----------------------------------------------------------------------- jafo1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2459 View this thread: http://www.excelforum.com/showthread.php?threadid=38184 Private Sub Worksheet_Change(ByVal Target As Range) Range("B3").Select End Sub Put this in the appropriate sheet module. -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "jafo1" <jafo1.1r41ms_111958238...

autoformat the column of a cell area
Hi, Is there any way to format one cell are so that that the width of a cell area (something like format column width to contents) but without formating the cells above or bellow the area I want to format I have tried autoformat but it really does not give me what I want ! Somehow I cannot remove the autofilter that appears in the formated area. Thank you, best Hugo Personal.xls. Personal.xls is located in the XLStart directory, and is used to store macros and things that you want to be available to all workbooks, whenever you start Excel. By default, it is hidden in Excel, but if yo...

Color a certain range of cell if a cell contain specified word
E.g a b c d e f g h 1 2 3 Group1 4 5 6 7 8 9 Group2 if a cell contain the word "Group" + a single char, i want the row which is the same the cell containing "Group" to be autohighted In tis case, it will be Cell A3-H3, and Cell A9-H9 to be auto-highlight! Put cursor on col A>format>conditional format>formula is>=left($a4,5)="group">format as desired>copy format (use format tool) to col e then copy format the row down/up as desired. -- Don Guillett SalesA...

Cell Notes
I have inherited a spreadsheet that has a cell that whenever you put the cell pointer over the cell a note pops up with a description of the cell and notes about the cell that someone apparently entered. Its not a comment and can not be edited by comments. I have never seen this before. Does anyone know how to edit these notes or delete these notes? Thanks -- Ray Maybe it's from Data|Validation|Input message tab rayd wrote: > > I have inherited a spreadsheet that has a cell that whenever you put the cell > pointer over the cell a note pops up with a description of the ...

Jumping to a hidden cell on enter
I've got 3 drop down lists in which the source cells are located i hidden rows above the data cell. Each of these 3 data cells are righ on top of the other (of the unhidden cells) and every time I use one o them and hit enter, rather than going to the next unhidden cell down it goes to a hidden cell. I can see it displaying the cell destinatio and it's contents in the input window. I try to unhide then rehide an it's always the same cell it goes to upon enter. Does anyone have an ideas about what is causing this glitch? Thanks Trace ---------------------------------------------...

Data validation master cell
Is it possible to change what list is in a cell by selecting the name o that list from a "master" cell? For instance, say the master cell is A1, and it's validation lis contains the list "cats, dogs, horses". The list in B1 would be based on what list is selected in A1. Fo instance, if A1 was set to "cats", then the choices in B1 would b something like, "Calico, Tomcat, housecat, Siamese". What I really want to do though, is only have one "master" cell an have dependent cells that change depending on the master. So, if A1 is the master,...