Last cell in row range with a result

I need to see if anyone can tell me if it's possible to find the last 
cell in a range that has a result and not just a formulas. We have this 
file that has a row of formulas as each cell it a different day of the 
month. In another file I need to pull the current cell and at this time 
I have to change the cell reference. Please let me know, thanks
0
email_ges (4)
4/4/2007 4:43:24 AM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
797 Views

Similar Articles

[PageSpeed] 1

if you have a row of data , say row 3, with no included blanks, then the last 
value in that row is:

=INDEX($3:$3,COUNTA($3:$3))


-- 
Gary''s Student - gsnu200713
0
GarysStudent (1572)
4/4/2007 9:26:01 AM
Gary''s Student wrote:
> if you have a row of data , say row 3, with no included blanks, then the last 
> value in that row is:
> 
> =INDEX($3:$3,COUNTA($3:$3))
> 
> 
How are formulas that have no results treated.
0
email_ges (4)
4/4/2007 11:41:44 PM
That cell will be reported if it is the last cell in a row.  For example if 
the last cell in a row contains:

=INDEX($3:$3,COUNTA($3:$3))

then a blank will be reported.
-- 
Gary''s Student - gsnu200713


"George" wrote:

> Gary''s Student wrote:
> > if you have a row of data , say row 3, with no included blanks, then the last 
> > value in that row is:
> > 
> > =INDEX($3:$3,COUNTA($3:$3))
> > 
> > 
> How are formulas that have no results treated.
> 
0
GarysStudent (1572)
4/5/2007 7:10:03 PM
You might also try:
=LOOKUP(2,1/($3:$3<>""),$3:$3)

"George" wrote:

> Gary''s Student wrote:
> > if you have a row of data , say row 3, with no included blanks, then the last 
> > value in that row is:
> > 
> > =INDEX($3:$3,COUNTA($3:$3))
> > 
> > 
> How are formulas that have no results treated.
> 
0
jmb (270)
4/6/2007 12:22:01 AM
Gary''s Student wrote:
> That cell will be reported if it is the last cell in a row.  For example if 
> the last cell in a row contains:
> 
> =INDEX($3:$3,COUNTA($3:$3))
> 
> then a blank will be reported.
Thanks
0
email_ges (4)
4/6/2007 1:49:29 AM
JMB wrote:
> You might also try:
> =LOOKUP(2,1/($3:$3<>""),$3:$3)
> 
> "George" wrote:
> 
>> Gary''s Student wrote:
>>> if you have a row of data , say row 3, with no included blanks, then the last 
>>> value in that row is:
>>>
>>> =INDEX($3:$3,COUNTA($3:$3))
>>>
>>>
>> How are formulas that have no results treated.
>>
Thanks
0
email_ges (4)
4/6/2007 1:49:52 AM
Reply:

Similar Artilces:

Find the last, 2nd last and 3rd last data in a column
I have data listed in several colums and need to create a formula to detail the last (most recent) 2nd last and 3rd last piece of data in the column. I have used the following formula to display the last but cant edit this or create another formula to get the 2nd and 3rd last pieces of data. =LOOKUP(2,1/(A:A<>0),A:A) Thanks in advance Jamie If there are no empty cells in the range: last cell: =INDEX(A:A,COUNTA(A:A)) penultimate: =INDEX(A:A,COUNTA(A:A)-1) semi-penultimate: =INDEX(A:A,COUNTA(A:A)-2) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme r...

Deleteing cells in Excel
I've pasted the same info off a web page into three columns on a spreadsheet. I neet to get a title, hyperlink and decription to go across the page, not down. By pasting it three times, I just have to clean it up. (The titles all say "Title:" in front of them.) But cleaning up is a real pain. Is it possible to delete from the auto sort drop down box? (Or any other way?) That way, in the third column, I could delete all the titles (since they only go in the first column). If I am correct in assuming that the data Title, Hyperlink and Description, could be copied onl...

Email format changing when moving to next cell
I have an excel spreadsheet I created for when we get a new customer. The user opens the protected sheet and enters info by tabbing from cell to cell. I have changed the format of the email address cell to be Arial Narrow and bold, when I enter info and tab or enter out of that field the formatting changes to Arial and unbolded. How can I keep the format I want instead of what excel wants to default to? Thanks for the help! Kat in WI On Mon, 2 Apr 2007 16:17:49 -0500, "Kat" <kat@nospam.com> wrote: >I have an excel spreadsheet I created for when we get a ...

control chart scale with cell value in excel
I would like to control the Min. and Max. values for the x & y axes in a XY scatter chart with cell values. Does anybody know how to do this? Excel 2003 jjw: Here's a post that shows a simple[le VBA approach. http://processtrends.com/pg_charts_set_x_axis_min__max_dates.htm Kelly O'Day http://processtrends.com "jjw" <jjw@discussions.microsoft.com> wrote in message news:B743C713-7053-42F4-B005-2C7B594AA86F@microsoft.com... >I would like to control the Min. and Max. values for the x & y axes in a XY > scatter chart with cell values. > Does anybody...

How can I display worksheet summary page ie title/size/last updat.
I'm running MSExcel 2003. I am often working with large workbooks with mutiple worksheets. It would be v helpful if I could see/sort and print off a summary that shows worksheet properties similar to Explorer eg: workbook :xxxx ---------------- worksheet last update size names 1/2/04 64k companies: 5/2/03 893k etc... any ideas? Jon I wrote this sometime ago and it works as a workbook_open event (Needs to be placed in the ThisWorkbook code module). It could be adapted to work otherwise though. It adds a sheet and lists all the built-in and c...

How long will my capital last
Hi If I had a capital sum of say �100,000 and I was taking an annua income of 5% and I was getting 3.5% interest annually - Can you help m demonstrate how long my capital last? Thank you Dust -- Message posted from http://www.ExcelForum.com Dusty, =3DNPER(3.5%,-5000,100000,0) If, by 5%, you mean 5% of the original investment (=A35.000 each year), = your money will last for approximately 35 years at 3.5% interest. See Excel Help for more info on NPER(). Regards, Anders Silven "Durhamr" <Durhamr.yajun@excelforum-nospam.com> skrev i meddelandet = news:Durhamr.yajun@excelf...

Multi-select listbox question
I have a multi-select listbox and wish to obtain data from the last clicked record in the listbox (you'll notice that if you make multiple selections then only the last-clicked record has a dotted line around it). What, if any, listbox property identifies this record as last-clicked? Thanks Dave -- Posted via a free Usenet account from http://www.teranews.com ...

Cells(#,#).Value conversion #2
Hey I want to grab a serialized date and turn it into an integer in my vba what is the proper conversion for this ? example: Dim startDate As Integer Dim endDate As Integer startDate = ActiveRow.Cells(1, 2).Value endDate = ActiveRow.Cells(1, 3).Value A date is already an integer, just format it as General. -- HTH RP (remove nothere from the email address if mailing direct) "Alexandre Brisebois (www.pointnetsolutions.com)" <alexandre.brisebois@gmail.com> wrote in message news:1122998952.048872.181990@g44g2000cwa.googlegroups.com... > Hey I want to grab a serialized...

why is my last digit converted to zero?
I'm using Excel 2003 and when I enter a 16-digit number or cut and paste a 16-digit number, the last digit is converted to zero. This does not happen with 15 digits or fewer. Excel's numeric precision is 15 decimal digits. If you need to calculate with the number, you probably won't miss anything. If the number is a key, such as a creditcard number, add an apostroph (single quote, " `") before it; it will not show, but it will cause the number to be treated as text. Another way is to format the cell as text before entering the number -- Kind Regards, Niek Otten ...

condition format for surrounding cells
Need help, I have column A the have 3 conditional format that is color coded depending value, I would like to have columns B, C, D,E (which are text) that could changes to the same color as column A when that changes. If anyone could help that would be greatly appriciated. Hi try something like the following: - select A1:E1 - in the conditional format dialog enter a formula such as =$A1="condition1" -- Regards Frank Kabel Frankfurt, Germany "little rusty with excel" <little rusty with excel@discussions.microsoft.com> schrieb im Newsbeitrag news:E9563E6B-7BCF-4B2...

Display result depending on value entered
I want a quick way to enter a time in a cell. example: F5 can either be 00:45 or 00:30 If the value 1 is entered into G5 then F5 will return 00:45 and if the value 2 is entered into G5 then F5 will return 00:30 what formula do I need for F5 Thank you, your help is appreciated. "Gotroots" <Gotroots@discussions.microsoft.com> wrote in message news:65A14660-7E8F-4801-96FD-F06BCAE5BF88@microsoft.com... >I want a quick way to enter a time in a cell. > > example: > > F5 can either be 00:45 or 00:30 > > If the value 1 is entered into ...

How do I convert some data in the row to columns?
I have an issue that I need to resolve. I'm currently getting data in a row format, but I need to convert some of the data into columns, for importing into Access. For example...... Item Description Jan 05 Feb 05 Mar 05 Apr 05 ........... ABC123 Widget A 100 250 175 210 ............ XYZ123 Widget B 50 75 100 76 ............ To convert to........ Column1 Column2 Column3 Column4 Item Description Date Qty ABC123 Widget A Jan 05 100 ABC123 Widget A ...

Populating cells
I am creating a worksheet. In it, one cell has a drop-down to select 1 of 9 values. Once one of these nine values is selected, I want other cells to auto-populate from other information within the worksheet. I tried the "IF" function but could only nest seven functions within that function. Is there some other function I can use to have these other cells auto-populate based on the selection of that initial cell? Any help is greatly appreciated. If you have a sheet that has data in say column A and column B and column C you could use a vlookup function =VLOOKUP(A1,Sheet2!A32:...

Auto fit wrapped and merged cells
Using Excel 2003 if you have a number of cells that are merged and wrapped how can you get the rows to auto fit height - I am sure this used to work on previous versions of excel but does not seem to now (similarly for columns). Is there a fix for this or an option I need to select or deselect? Hi Elmo, See this macro from Jim Rech: http://tinyurl.com/4c5l6 --- Regards, Norman "elmo2" <elmo2@discussions.microsoft.com> wrote in message news:8F22A71D-4E92-4AFF-B5DC-646926FE3AF1@microsoft.com... > Using Excel 2003 if you have a number of cells that are ...

Sum cell value across multiple worksheets.
Do sum up cell A1 of each worksheet from 1 to 6 I would use this function: =SUM(Sheet1:Sheet6!A1) BUT what if I don't know how many worksheets I will be having. Maybe 50 or 100. How can I write a function to sum up all the worksheets? When I use this formula: =SUM('*'!A1) It works if you know how many you will have, but not if you keep adding more. Because that function above turns into this =SUM(Sheet1:Sheet6!A1) - depending how many worsheets you have, so if you would add a 7th, it would not count that. Anyone have any suggestions? Thank you My soluti...

format a cell for a timesheet
What format should I use if I want the time sheet to show totals in quarter hours such as 8:25 instead of 8:15. The current format is [H]:mm and it's returning the 8:15 but I want it to return 8:25 Thank you Louis Louis, In an adjacent column, use = A1*24 and format to dec places. You will get 8.25, not 8:25, but is that okay? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Louis" <anonymous@discussions.microsoft.com> wrote in message news:3e9a01c3fe02$2f54c460$a101280a@phx....

insert image into a cell.
Is there any way to insert an image (jpeg or gif) into a cell? You can insert a picture onto a worksheet, and position it over a cell, but it can't be inserted into a cell. Another option is to place the picture into a cell's comment. It will appear when the user points to the cell. There are instructions here: http://www.contextures.com/xlcomments02.html#Picture Robert Kent wrote: > Is there any way to insert an image (jpeg or gif) into a cell? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html "Debra Dalgleish" &...

Need help in searching last occurrence of string in text file
Hi , I am not good in file handling in excel macro..i need some help from u. I have one huge text file and i want to search last occurrence of string in that file and then cut the data after that searched line till end of file and paste it into a new text file. Any help appreciated. thanks in advance. How huge is "huge" (in Megs)? -- Rick (MVP - Excel) <sameer2211@gmail.com> wrote in message news:8cb08938-cf0e-4925-95bb-725f72df9f8d@25g2000prz.googlegroups.com... > Hi , > > I am not good in file handling in excel macro..i need some help from > u. > I hav...

imbed excel cell text in a shape
I need to insert a number into a shape that corresponds to a cell in excel. Kinda like the exploded parts view in a car repair manual. A callout shape with a text value "123" that is linked to cell A1 in file ***.xls. It would be nice to be able to auto generate the callouts from the excel file. shape 123 is cell A1, shape 124 is cell A2 and so on. Thanks what version of visio are you running? al "mystified" <mystified@discussions.microsoft.com> wrote in message news:80B4E390-190A-41D9-AC4A-F96C1DA6FB6E@microsoft.com... >I need to insert a number into a s...

change value in a cell according to another cells value
Please help me.... Example: I need to make 1 = $50 and 2 = $100 and 3 = $120 etc I need to do this by entering 1, 2 or 3 in column A and the value automatically appearing in column D, is this possible? Also I would like to hide a column and only show cells from that column in the row that I apply value to in another cell of the same row. Essentially hiding the whole column and only showing value in a row when the row is in use. Please help me, I want to create something for a job interview and can't work it out! Thank you so much whoever can answer this. "Kylie Rose" wro...

Last logged on by
Hello! Using exchange 2003. When I look under Adminstrative groups -> Server -> "name" -> First storage group -> mailbox store -> mailboxes you can se all the mailboxes that you have and last logged on by. Under last logged on by I can se that one person (me) have logged on on to diffrent acconts, even if I havent don it. WHAT is this? Best regards Micke Micke: Did you use your account as the service account for anti-virus or spam? Also, IIRC, even if you view someone's Outlook Calendar, it would mark the mailbox as being logged on by you. Regards...

Can't edit cells in Graph
I'm on a MBPro, 2GB, OS 10.4.7, Office 11.2.5. Every Time I use Graph and quit, I have to go back and delete the prefs files or I can't go in and edit cells for any chart. I found the "delete prefs" on an older thread, which works, until I quit. Is there a permanent workaround? Dan Try this advice from Andrew Chiang [MS]: ------------------ Go into your ~/Library/Preferences/Microsoft folder and delete or move (to another location) the two files below. By ~, I mean your home directory/folder. com.microsoft.Graph.prefs.plist Microsoft Graph Toolbars (11) Afterwards,...

Trying to find the fourth blank cell in a column
Hello I'm trying to find th fourth blank cell in a column and select a range based on it. Should be simple but for some reason I can't makeit work. Anyone tell me what I'm doing wrong? I'd be most grateful. For background, I have some data followed by two blank cells, followed by more data, folowed by a blank cell, followed by more data followed by another blank cell, followed by more data followed by blank cells to the end. It's the start of these final blanks I'm trying to find programatically. Problem is that after firstqun,the rest of the variables show as ...

Extracting an exact phrase from a Cell
:eek: :confused: I have column A with 600 entries of Text such as: Invalid cfg: DIMXPS-G3 REQUIRES MG MONITOR >= 1 Some cells would say "MONITOR" some would say "Keyboard" and some woul say "Speakers". How can I extract entries with only Monitor in the text -- mazala ----------------------------------------------------------------------- mazalam's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2676 View this thread: http://www.excelforum.com/showthread.php?threadid=40118 What do you mean by extract? -- Dnereb ----...

Getting the longest lengh in range of cells
I am trying to get the length of the longest cell in a range and use the array {=len(a1:a1)}. What happens is that it picks up the length of cell A1. Is there a way in a formula to determine the longest cell length in a range, or through VBA. Thanks in advance Hi Try this formula array (please amend with last row number): {=MAX(LEN(A1:A7))} HTH Cordially Pascal "Geoff" <gh@bob.com> a �crit dans le message de news:uMKoCQiSFHA.1176@TK2MSFTNGP10.phx.gbl... > I am trying to get the length of the longest cell in a range and use the > array {=len(a1:a1)}. What happen...