How do I reference last cell having an entry in a column?

Have a problem: trying to create formula for a cell to display the last entry 
in a column.  Example:  column has 100 cells, 48 of which have data.  The 
48th entry in the column was "556".  How do I get 556 displayed in a cell by 
the top of the column (used as  a summary)?
0
Sailor (13)
10/23/2004 7:23:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
91 Views

Similar Articles

[PageSpeed] 43

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

--
Regards
Frank Kabel
Frankfurt, Germany

"USNavy Sailor" <USNavy Sailor@discussions.microsoft.com> schrieb im
Newsbeitrag news:CBE88CFD-AA3F-487D-8C44-F3F3CE418D5B@microsoft.com...
> Have a problem: trying to create formula for a cell to display the
last entry
> in a column.  Example:  column has 100 cells, 48 of which have data.
The
> 48th entry in the column was "556".  How do I get 556 displayed in a
cell by
> the top of the column (used as  a summary)?

0
frank.kabel (11126)
10/23/2004 7:52:33 PM
=LOOKUP(9.99999999999999E+307,F:F)

Will return the last number in column F.

For more options on returning last cells see Bob Phillips' site.

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

Gord Dibben Excel MVP

On Sat, 23 Oct 2004 12:23:01 -0700, "USNavy Sailor" <USNavy
Sailor@discussions.microsoft.com> wrote:

>Have a problem: trying to create formula for a cell to display the last entry 
>in a column.  Example:  column has 100 cells, 48 of which have data.  The 
>48th entry in the column was "556".  How do I get 556 displayed in a cell by 
>the top of the column (used as  a summary)?

0
Gord
10/23/2004 7:57:36 PM
=LOOKUP(9.99999999999999E+307,A:A)

USNavy Sailor Wrote: 
> Have a problem: trying to create formula for a cell to display the last
> entry
> in a column.  Example:  column has 100 cells, 48 of which have data. 
> The
> 48th entry in the column was "556".  How do I get 556 displayed in a
> cell by
> the top of the column (used as  a summary)?


-- 
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4165
View this thread: http://www.excelforum.com/showthread.php?threadid=271828

0
10/23/2004 8:00:17 PM
Reply:

Similar Artilces:

"If in last week" formula
I have a number of simple two column sheets, one column contains dates. Basically each sheet is an employee, and the items list the date and notes for any "one on one meetings" that I have with that employee. I want to have a summary sheet that lets me know when the most recent meeting was. I think it would be ideal to check sheet1-5, if date is older than 1 week, output false or true. I can't find any formula example on the net that uses dates as a condition of true or false. Can anyone point me in the right direction? Does =IF(A1<(TODAY()-7),true,false) work for what ...

See if cell is in Range
Thanks for taking the time to read my question. I'm passing a string that is a cell reference to a function. In that function I want to determine if that cell reference is within a predetermined range. Not sure how to do that. Right now I have: Function CheckRange(TheSheet As String, TheCell As Range) As Boolean If TheSheet = "Sheet1" Then if TheCell In Range("B4:B30") then 'This line is red as it is incorrect CheckRange = True End If What do I use instead of "In"? Thanks, Brad Brad Use something like: If In...

Editing in a cell
I am having a problem with a newly created workbook. When I go to a cell that has data in it and try to add more data to the existing string I lose all the previous information. The work sheet that I am working in is protected and I have "edit directly in cell" checked in the options section. It is almost like my double click (as well as F2) are being treated as a single click. Thank you I figured it out. For some reason "Hide" is selected for the unlocked cells. "John English" wrote: > I am having a problem with a newly created workbook. > > Wh...

Money signs appear in my Cell and I don't want them there
Im trying to enter the numbers 2.9 in cell like AD and it turns it into $2.90. That is not what I need, how do I stop that? Format as General instead of currency -- Regards, Peo Sjoblom http://nwexcelsolutions.com "kate" <kate@discussions.microsoft.com> wrote in message news:E9EEA936-437C-486F-A2D3-2385EF6BD2D0@microsoft.com... > Im trying to enter the numbers 2.9 in cell like AD and it turns it into > $2.90. That is not what I need, how do I stop that? ...

How do I get a cell to remember the last entry and add to it?
I am using Excel 2000. I want my cell to remember the number in it and add an additional number each time I need to retotal. Jab Sounds like you want that cell to be an accumlator cell. Simple question with complex results. You can have a cumulative total in a cell if you have a separate source cell for adding a new total to the original. Use at your own risk. I am Posting this just to show you how it can be done, not as a good solution. You would be much better off to have another column so you can keep track of past entries. Goes like this: =IF(CELL("address")="$C$4&...

How do I enter the date last modified in the footer of an Excel d.
How do I enter the date last modified in the footer of an Excel document? I want to be able to distribute a monthly or weekly report in Excel to users and have it automatically update the date last modified in the footer each time I update the report. You need to do that with a Before_Print event macro. If you search the newsgroups from Google you will find lots of examples. On Fri, 4 Feb 2005 09:33:04 -0800, "Last modified date in footer" <Last modified date in footer@discussions.microsoft.com> wrote: >How do I enter the date last modified in the footer of an Excel doc...

Vlookup only the last 6 characters
I have a spreadsheet starting in cell A4. I want to lookup only the last 6 digits in column A and then lookup the value in column J and return the appropriate value from column K. The values in column A are 000322341; 000333456; etc. The values in column J are 322341; 333456; etc. Here is my current non working formula... =VLOOKUP(RIGHT(A4,6),$J$4:$K$7,2,0) Any help would be appreciated. Thanks. The RIGHT function is returning a text value, and I'm guessing that the values in column J are true numbers. So you'd need to covert the text back to numbers in order to...

Bold report column based on month
I have a report with a column for each month. (Jan, Feb, Mar...) I'd like to bold the data in the column that the report is run for. I placed this code in the on Format event of the report to test, the code is executing ok but the columns end up all in normal weight. m = DatePart("m", dt) Select Case m Case 1 Me.Jan.FontWeight = vbBold Me.Feb.FontWeight = vbNormal Me.Mar.FontWeight = vbNormal Me.Apr.FontWeight = vbNormal Me.May.FontWeight = vbNormal Me.Jun.FontWeight = vbNormal Me.Jul.FontWeight = vbNormal Me.Aug.FontWeight = vbNormal Me...

looking for range of text in a single cell
I just started a new job and my company already had a file with abou 16,000 records in it (called Products). This file contains all of th parts numbers that they carry. In this file there is one column calle "description". This cell contains the dimensions, color name and som other information such as if the piece is flat or rounded. I hav another file that has about 400 records and this file tells me "colo name" as well as the "type" of stone that it is (called Stone_Type). What I am trying to do is add a field to the Products file that wil say stone type. ...

To have a 'fixed cell' be equal to the last data entered cell in a column
Good Evening All, I have a worksheet, example below. I have frozen the panes to always show rows 1-3. I wish the cell A3 to be the same as the last 'non-blank' cell in Column A.(See explanation below). A B 1 2 Header Header 3__________________ 4 M1004 5 M1005 6 M1002 7 M1003 8 M1006 9 M1001 10 So in this case, at present, A3 would be M1001,(A9), but when I enter a new value in A10, (eg M1008), I would like A3 to automatically update to A10 ie M1008. I think that maybe INDIRECT or OFFSET maybe involved but am fairly unfamiliar with ...

Comparing columns and extracting data
I've got two columns with lots of data in each. Some unique, some the same. Short example: Col A cat cow dolphin dog fish horse snake zebra Col B bear cow dolphin dog fish hamster monkey zebra I would like to create additional columns with the following stipulations: Col C (what's common in both A & B) Col D (what's in A but not B) Col E (what's in B but not A) Any help is appreciated. These are all array formulas. **Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) > Col C (what's common in both A & B) Ass...

sales transaction batch entry stays after saving
After you save a sales transaction assigned to a batch, the batch previously entered stays there and if you try to pull up another transaction you have to delete the blank sales transaction screen before it will pull up the transaction you are trying to pull up. It seems to me to reduce key strokes when you save a sales transaction the batch field should then be blank and the system should allow you to pull up another transaction from the magnifing glass and not have to delete something that is not even there. ---------------- This post is a suggestion for Microsoft, and Microsoft respo...

How do I have a cell update itself only if it's currently blank?
I need to track to retain the first value of a date field. Each time I load the worksheet the date field has the potential to change, I need to keep track of the initial date in addition to the current date that is in the field. I have date columns called ASSIGNED and 1st ASSIGNED. The initial value of both will be blank. At some point the ASSIGNED will be populated with a date. When it is populated for the first time I want to save this value in the 1st ASSIGNED column. I need something like: IF ( ISBLANK(ASSIGNED), "", ASSIGNED ) in my 1st Assigned cell, however, I do not ...

Changing Font Colour if cell is certain word?
Can I make the font of a certain cell change it's font colour to Red if the word in the cell is, example, "Fail"? I want the change to apply from B2:F23. -- wuming79 ------------------------------------------------------------------------ wuming79's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36037 View this thread: http://www.excelforum.com/showthread.php?threadid=560169 Use "Conditional Formatting". It's on the Format menu -- MartinShor Software Tester &amp; Musicia -----------------------------------------------------...

Can I link *.jpg files to Excel cells?
I would like to connect pictures to cells on a spreadsheet. Ideally, I'd like to connect more than one to a cell. Or to its row. That would work OK also. Can I do it? TIA LAS You can hyperlink a cell to an image stored elsewhere or to an image stored within the workbook. How you do it depends upon the purpose of the linking and the ultimate results you are looking for. Gord Dibben MS Excel MVP On Fri, 25 Jun 2010 17:26:13 -0400, "LAS" <FakeMail@Hotmail.com> wrote: >I would like to connect pictures to cells on a spreadsheet. Ideally, I'd >lik...

GP10 Transaction Lists don't include a BatchID column
GP10 has alot of new functionality within the 'Lists' area via the use of ribbons etc. to perform changes to documents outside of the batch environment. However the logical way to enter and group Transactions is still via the use of Batches. Within the list area I can't find a way to filter or sort my transactions by BatchID or at least see which batch the transaction belongs to without drilling into the document. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the ...

Adding random cells togethr
I want to be able to add y2,aa2,ac2,ae2,ag2,a12,ak2,am2,ao2,aq2,as2,au2,av2,ax2,az2 together and return the result in bb2 I am using =sum(y2,aa2,ac2,ae2,ag2,a12,ak2,am2,ao2,aq2,as2,au2,av2,ax2,az2) however I only see the actual formula returned in the cell not the result. I have been able to do this in the past. Can anyone help with this problem? -- Kaylean Hi Kaylean, Perhaps BB2 was formatted as text before you entered the formula. Select it and hit F2 and Enter. HTH Regards, Howard "Kaylean" <Kaylean@discussions.microsoft.com> wrote in message news:0DF3C2DC-8918-...

cell ranges in formulas
When referencing a cell range in a formula, is there a way to represent the cell range by referencing another cell. What I want to do is this, for example: say cells a1 thru a100 contain a number of values. I want to sum a certain subset of these today, but tomorrow I want to change that subset, so I'd like to enter the starting and ending point for my sum range into another cell, say b1 & b2 So if I enter the text "a6" in cell b1, and "a25" in cell b2, my sum formula should look like =sum(a6:a25). This give me the flexibility to change my sumrange very e...

Rename a sheet tab when cell contents change
Hi all, I would like to rename a sheet tab when a specific cell's contents change...is this possible to code? I am using Excel 2003 (but need some backward compatibilty...) TIA, Miki activesheet.name=range("b1") -- Don Guillett SalesAid Software donaldb@281.com "Michelle" <miki@spam_me.net> wrote in message news:tHmzb.290244$275.1012789@attbi_s53... > Hi all, > > I would like to rename a sheet tab when a specific cell's contents > change...is this possible to code? I am using Excel 2003 (but need some > backward compatibilty...) > >...

Diagonal Cells format
How do I format the Label Row of a spreadsheet to be 45 diagonally instead of the standard horizontal format? Thanks Ross Hi Ross select the cells, choose format / cells - go to the alignment tab and play with the little dial on the right hand side of the dialog box (or type numbers in the box underneath). Cheers JulieD "Ross" <Ross@discussions.microsoft.com> wrote in message news:DD627596-2F68-4886-A058-AE7EBE2CB427@microsoft.com... > How do I format the Label Row of a spreadsheet to be 45 diagonally instead of > the standard horizontal format? > > Thanks &g...

Separatting Data in Columns
Thank you to all in advance for your clever assistance. Problem: I've data separated by a comma in a column which needs to be in two columns. Is there a quick to achieve this? Data>Text to columns>Separated>Commas -- Kind Regards, Niek Otten Microsoft MVP - Excel "G. Allen" <anonymous@discussions.microsoft.com> wrote in message news:E68847DD-080E-428F-BE5B-E393A5AE40A4@microsoft.com... > Thank you to all in advance for your clever assistance. Problem: I've data separated by a comma in a column which needs to be in two columns. Is there a quick to ...

Is there a quick reference guide for excel 2003 -2007
I think I have seen one somewhere but can't find it. Is there is a reference guide showing how to find find things in excel 2007 that are not in the same place as they are in 2003? you can give this a try http://www.officelabs.com/ribbonhero of download this xls workbook http://office.microsoft.com/download/afile.aspx?AssetID=AM101864291033 -- Gary Keramidas Office 2010 "Noella" <Noella@discussions.microsoft.com> wrote in message news:45DF2F13-504E-45CD-8FB7-39433847656F@microsoft.com... >I think I have seen one somewhere but can't fi...

Formatting and Grouping Columns
In Office 2000 in Excel when you select two columns and right click it included the option to "Unhide" or "Hide". In Office XP in Excel I can not select two columns it automatically expands the selection to include the Merged Row above it which includes all the columns below - not just the two I selected. When I select just two cells the right click does not include the option to "Unhide" or "Hide". I have to go to the format menu, select column and select "Unhide" or "Hide". Is there a way to select columns without including...

Command button on data-entry form
Is there a way to add a command button to a form that displays data for reference? I'd like the user of the form I'm creating to choose a chemical abbreviation from a dropdown but be able to click a button and display a table containing the full chemical name and unit. Or else have a dropdown with three columns? It's easy to do in Access, but I can't seem to figure out how to do either of these things in Excel.) It might be easiest to use a range of 2 columns (why 3???). Then you can make your combobox have multiple columns--including both the abbreviation and long name....

How to separate numbers within a cells into column
Hi I have search all the web but still cannot find a solution to this problem. I have a single cell containing the number -> 12345 I wanted to either separate the number 1 into column A, 2 into Column B, 3 into Column C and so on. Otherwise, if inserting a space between 1 and 2 and 3 and 4 and 5 is also fine. How to go about it? Any EXCEL experts here can please advise me? Thank you very much. alvynng If your value is in B1, then in A1 enter: =MID($A2,COLUMN(),1) and copy across -- Gary''s Student - gsnu200787 Sorry I should have said the value nust be in A2, not B1 ...