Last cell with data in a range

I enter weekly data into a spreadsheet with a summary page at the front.  
After every week, when new data is entered, I want the formula at the front 
to use the cell with the new data instead of me having to change the existing 
formula.

For example, my data looks like this:

A  B  C
      276
      300
      421
      175
         0
         0
         0
      
I need a formula that will automatically detect the last number >0 in column 
C.

Thank you for your assistance!
0
Utf
3/24/2010 10:01:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

7 Replies
1309 Views

Similar Articles

[PageSpeed] 22

=Countif(C2:C1000,">0")

so your main page formula might be something like:

=indirect("'Sheet2'!C" & Countif(Sheet2!C2:C1000,">0")

(untested, watch for spelling errors or typos)

"iamjbunni" wrote:

> I enter weekly data into a spreadsheet with a summary page at the front.  
> After every week, when new data is entered, I want the formula at the front 
> to use the cell with the new data instead of me having to change the existing 
> formula.
> 
> For example, my data looks like this:
> 
> A  B  C
>       276
>       300
>       421
>       175
>          0
>          0
>          0
>       
> I need a formula that will automatically detect the last number >0 in column 
> C.
> 
> Thank you for your assistance!
0
Utf
3/24/2010 10:23:01 PM
what i have now is:

=b4/(IF(Paul!D13>0,"1","0")+IF(Trish!D13>0,".6","0")+IF(Ryan!D13>0,"1","0")+IF(Kathy!D13>0,"1","0")+IF(April!D13>0,"1","0"))

Ideally, I want a formula to detect a new number in a column and if it's >0, 
to enter "1", if 0, enter "0".

I'm trying to make this report as automated as possible.

"ker_01" wrote:

> =Countif(C2:C1000,">0")
> 
> so your main page formula might be something like:
> 
> =indirect("'Sheet2'!C" & Countif(Sheet2!C2:C1000,">0")
> 
> (untested, watch for spelling errors or typos)
> 
> "iamjbunni" wrote:
> 
> > I enter weekly data into a spreadsheet with a summary page at the front.  
> > After every week, when new data is entered, I want the formula at the front 
> > to use the cell with the new data instead of me having to change the existing 
> > formula.
> > 
> > For example, my data looks like this:
> > 
> > A  B  C
> >       276
> >       300
> >       421
> >       175
> >          0
> >          0
> >          0
> >       
> > I need a formula that will automatically detect the last number >0 in column 
> > C.
> > 
> > Thank you for your assistance!
0
Utf
3/24/2010 10:35:02 PM
Assuming there are no negative numbers and there are no empty cells *within* 
the range.

=INDEX(C2:C20,COUNTIF(C2:C20,">0"))

-- 
Biff
Microsoft Excel MVP


"iamjbunni" <iamjbunni@discussions.microsoft.com> wrote in message 
news:0CA8D681-78DF-4AB1-B9E2-6BC1333D6B92@microsoft.com...
>I enter weekly data into a spreadsheet with a summary page at the front.
> After every week, when new data is entered, I want the formula at the 
> front
> to use the cell with the new data instead of me having to change the 
> existing
> formula.
>
> For example, my data looks like this:
>
> A  B  C
>      276
>      300
>      421
>      175
>         0
>         0
>         0
>
> I need a formula that will automatically detect the last number >0 in 
> column
> C.
>
> Thank you for your assistance! 


0
T
3/24/2010 10:39:52 PM
Hi,

You may try this

=LOOKUP(TRUE,C2:C8>0,C2:C8)

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP

"iamjbunni" <iamjbunni@discussions.microsoft.com> wrote in message 
news:0CA8D681-78DF-4AB1-B9E2-6BC1333D6B92@microsoft.com...
> I enter weekly data into a spreadsheet with a summary page at the front.
> After every week, when new data is entered, I want the formula at the 
> front
> to use the cell with the new data instead of me having to change the 
> existing
> formula.
>
> For example, my data looks like this:
>
> A  B  C
>      276
>      300
>      421
>      175
>         0
>         0
>         0
>
> I need a formula that will automatically detect the last number >0 in 
> column
> C.
>
> Thank you for your assistance! 

0
Ashish
3/25/2010 3:31:52 AM
On Mar 24, 9:31=A0pm, "Ashish Mathur" <mathurash...@hotmail.com> wrote:

Or this



=3DINDIRECT("N"&MAX(IF($N$80:$N$122<>0,ROW($N$80:$N$122))))

Substitute the "N" for your column and also adjust the ranges.
0
Ziggy
3/25/2010 5:04:13 PM
On Mar 25, 11:04=A0am, Ziggy <ziggy...@xmission.com> wrote:
> On Mar 24, 9:31=A0pm, "Ashish Mathur" <mathurash...@hotmail.com> wrote:
>
> Or this
>
> =3DINDIRECT("N"&MAX(IF($N$80:$N$122<>0,ROW($N$80:$N$122))))
>
> Substitute the "N" for your column and also adjust the ranges.

I should have stated this is an ARRAY formula and needs the Cntrl-Alt-
Enter
0
Ziggy
3/25/2010 7:17:54 PM
On Mar 25, 1:17=A0pm, Ziggy <ziggy...@xmission.com> wrote:
> On Mar 25, 11:04=A0am, Ziggy <ziggy...@xmission.com> wrote:
>
> > On Mar 24, 9:31=A0pm, "Ashish Mathur" <mathurash...@hotmail.com> wrote:
>
> > Or this
>
> > =3DINDIRECT("N"&MAX(IF($N$80:$N$122<>0,ROW($N$80:$N$122))))
>
> > Substitute the "N" for your column and also adjust the ranges.
>
> I should have stated this is an ARRAY formula and needs the Cntrl-Alt-
> Enter

Should be Ctrl-Shift-Enter.. sorru bout that
0
Ziggy
3/26/2010 3:05:32 PM
Reply:

Similar Artilces:

How do I add the last 10 cells
If I have a column labelled, say, temperature, that is often being added to, how do I add up the last 10 cells. I want excel to do it for me automatically. I want a formula that will find the last filled-in (non blank) cell in a column and then add up the 10 entries above that. Assuming your data starts in A10, then in A9 perhaps, try the following:- =SUM(OFFSET($A$10,COUNT(A10:A9995)-10,,10)) If your data started in A15, formula would be:- =SUM(OFFSET($A$15,COUNT(A15:A10000)-10,,10)) This assumes you have no other data below this range in that column (Or at least within the COUNT range)....

Help, Excel Files Being Wiped Out: Data Lost or Corrupted
When I try to open certain Excel files, I get this message: Unable to read file. [OK] Unable to read file. [OK] Errors were detected ... MS was able to open the file by making the repairs below. Save the file to make the repairs permanent. Damage to the file so extensive that repairs were not possible. Excel attempted to recover your formulaes and values, but data may have been lost or corrupted. The resulting file is devoid of any formatting, and some formulaes are gone. It's really no use since it takes so much time to reconstruct these files. I've been saving 4 different v...

'sticky' cell selection
When I select a cell it cannot be deselected and if i move my mouse it selects all cells from the original. I cannot select a singular cell anywhere else on the worksheet or select any other commands. Thus the name sticky. How do I unstick my selection to carry on using the worksheet. yippeekiay, sounds like you are in Extended Mode, EXT on in the right hand corner or the status bay, press the F8 key to get out of it -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always apprecia...

Why subscript out of range?
Hi, why do I get a run-time error number 9 'subscript out of range' message on running this code? Dim vaTest as variant Dim i as integer vaTest = Range("a1:a10").value For i = 1 to Ubound(vaTest) If vaTest(i) = "True" then Msgbox "True" End if Next i ...

Merging Info in Two Cells #2
I did get a partial response, thanks Barb, but now I need to dig into VBA and I stumble. I guess the formatting needs to be done in VBA and the easiest way is by copying each cell and pasting the values (otherwise, I am not sure if I can do partial formatting of a cell, i.e. superscripting parts of it). So the code would read: Range("c5:g5").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False With ActiveCell.Characters(Start:=4, Length:=9).Font ...

How do i get my data to be on the x axis of a chart?
Holly - Chart menu > Source Data > Series tab. Select a series, then use the X Values (or Categories, depending on chart type) edit box to select your range of X values. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ hollybusch5 wrote: ...

Copying Selective Data Between Worksheets
I am setting up a simple accounts workbook I have one sheet with 11 column headings and as each transaction is allocated to a department I then have a worksheet for each department - I am trying to get it so that when an entry is made in the main sheet it also appears in the department sheet also I have used the following formulas but now the main sheet has passed Row 31 the data is not being picked up on the dpartment sheets In Cell B6 on the dept sheet =IF($A$6="","",IF(Summary!B6=A$6,ROW(),"")) In Cell C6 and then copied across the Columns =IF(ROWS(...

Flag row if cell values = something specific
Let's say A1 = top and B1 = Bottom in C1, I want to say that if A1 = top and B1 = bottom then the cell background color of C1 should be red. How can I do this? And I need to do this in a macro. I can't use conditional formating from the menu. I have a recorded macro that does all my formatting and I need to add this. "Some Dude" <sdatt@myplace.com> wrote in message news:e9kw7DrfGHA.4864@TK2MSFTNGP05.phx.gbl... > Let's say A1 = top > and B1 = Bottom > > in C1, I want to say that if A1 = top and B1 = bottom then the cell > background color of...

I'm trying to delete a huge amount of data and says something
Hello. I have a table with a huge amout of data. The data comes from an ODBC table and is copy to a local table. When I have to delete the data of the local table it returns me a message sauing that I have no enough disk free space to cancel the operation. For me is ok, but I have 22gb of free memory. Is it normal this messages? Regards, Marco Marco, How are you trying to delete the records? By selecting in the table and then press delete? If so try to create a delete query that should work. hth -- Maurice Ausum "Marco" wrote: > Hello. I have a table with a huge amo...

Using Indirect Cell References in a Chart
I am trying to create a 'self-sizing' chart, but don't know if I can use indirect cell references in a chart. Here's the scenario: I have a tab with data(DataTable! A2:A20) and a tab with Graphs (Graphs). I use a Max formula to determine the last row of data entered, and I've labled that formulas as 'DataTable!LastRow'. In my 'Graphs' tab, I have a cell called 'Graphs! XAxisLabel' with the formula ='Datatable!A3:A'&(DataTable! LastRow)' that displays the rows of data to be used in the chart. I want to use a formula (=Graph...

Need control to not display its data under certain circumstances
I have a form that saves "99" for a particular field if that field is left blank (which for us means that we tried, but weren't able to collect that information). However, when I load that form for a pre-existing record, I would like these fields to not show the 99, but rather to appear empty. Is there a way to do that through the control properties? Meaning, is there a way to not display the saved value for a particular control if the data saved for it was a 99? Thanks for your help. Try conditional formatting. Create a condition that says, if the value of the control = 99, ...

Serial data
I need to input serial BCD data to microsoft access via a com port. I thought of running a scipt to control the flow of data but am unsure where to put the data once I have loaded it and how to read that data into microsoft access. Has any one done this before or have any ideas? -- Pete G Pete G <PeteG@discussions.microsoft.com> wrote: >I need to input serial BCD data to microsoft access via a com port. I thought >of running a scipt to control the flow of data but am unsure where to put the >data once I have loaded it and how to read that data into microsoft access. >...

Data entry
I have the Table ID Autonumeric CodFarmer String CodSemester Lookup (String) CodAnimal Lookup (String) Quantity Numeric AnimalUnit Numeric AnimalUnit must have the values indicated below: CodAnimal Animal Unit 1 1 2 1 3 1 4 0.25 5 0.5 6 0.75 7 1.25 8 1 9 0.75 10 0.5 11 1.25 12 1 13 1 I would like to be able that when I select a value in CodAnimal the respective value in AnimalUnit field will be filled. How can I do this? Many t...

problems installing outlook client: The Microsoft CRM Data File, M
Problem trying to install Microsoft CRM 3 Outlook desktop client on a PC running windows XP service Pack 2 and Outlook 2003 service Pack 2 connected to exchange. Originally downloaded ISO installation file from Microsoft downloads, and burnt CD. Installation failed, it seems the installation CD was corrupt. We then found our original installation CD, which when we installed it, it came up with two messages: - Microsoft Outlook was not initialized. Please run Outlook and configure your mail account. ---- Outlook is installed and running, there's no problem with this so I do not unde...

How do I import CRM 1.2 live data into CRM 1.2 test server
I was told to use the CRM redeployment tools for this but I got stuck. I ran the "user data export wizard" on my current CRM server but it failed to create the XML documents. I got the error: The wizard could not find the Microsoft CRM originizational unit (OU) that was specified in the database. Make sure the OU exists in Active Directory. I know the OU exsists and I can see it from the CRM machine. I am logging in as domain admin. What do you think I have missed? TIA Eric ...

Selection of date range
I have a small table that covers an entire year of sales I need to make a way to select he date range, and sales Agent type and display the pounds purchased for that week. So I'd like a selection box "enter Date range" (weekly) & type of Customer Affiliate Domestic, Affiliate international, Domestic Customers, International Customers. Should be simple, eh? Any advice? Just add a little form with 2 textboxes: txtStart and txtEnd Then use the following query criteria: For the dates: Between Forms!FormName!txtStart And Forms!FormName!txtEnd...

How to calculate (generate) a cell reference
I need a way to generate a cell address (row,column) from the value in another cell and be able to use the contents of that in a calculation. Here's my situation: I have a table of mileage readings for my car taken at odd intervals. Note that the "[Row]" column is the actual row number in the spreadsheet and not part of the data. It is included because I want to reference it later. [Row] Date Odom Miles Days Mos Yrs M/Dy M/Mo Mi/Yr 13 3/13/05 48,041 1,141 50 1.64 0.14 23 695 8,335 14 7/10/05 50,922 2,881 119 3.91 0.33 24 737 8,843 15 10/29/0...

Return range of cell values based on current date
I have a spreadsheet containing daily sales data for the month. I want today's sales data to display on a different worksheet to save the hassle of someone scanning through an entire month of data. Is this possible and what function do I use to achieve this? Thanks, Wing WHY NOT ADD A WORKBOOK? Providing a workbook will not only get you your answer quicker but will better illustrate your problem, usually when we can see your data (-it can be dummy data but must be of the same type-) and your structure it is far easier for us to give you a tailored, workable answer to ...

Protecting sheet disables import of extetrnal data in Excel 2003
I need to protect the sheet and not disable ability to import of external data in excel 2003? Even when I open a sheet that worked while being protected in previous Excel versions, once I protect it in Excel 2003 the auto update feature for returning external data is disabled. ...

how to setup only one chart and change data from a drop down
Hi, How can I make use of only one chart on a sheet that can be changed dynamicaly by using a drop down to display a new chart. Example data is as follows: Time period is months from July to june Benchmark Threshold Actual monthly value. jul aug sep oct nov dec jan feb mar apr may jun Benchmark 20 20 20 20 20 20 20 20 20 20 20 20 Threshold 10 10 10 10 10 10 10 10 10 10 10 10 actual1 18 23 14 15 Benchmark 100 100 100 100 100 100 100 100 100 100 100 100 Threshold 78 78 78 78 78 78 78 78 78 78 78 78 actual2 67 87 88 78 Benchmark 0.012 0.012 0.012 0.012 0.012 0.012 0.012 0.012...

define a cell with the value of anothe cell
I'm very new to excel and i think i just don't understand a basic function but i couldn't find it under the help menu. how can you define a cell using a letter and then a value of another cell? here is an example W15=5 D5=10 my guess was =D(W15) which i would like to equal 10, but i get a name error Hi Todd Try =INDIRECT("D"&W15) -- Regards Roger Govier Todd Duncombe <Todd Duncombe@discussions.microsoft.com> wrote: > I'm very new to excel and i think i just don't understand a basic > function but i couldn't find it under the help me...

how to set up a simple variable in Excel, but cell location independent
Can you please show me how to set up a simple variable in Excel, but this variable must be cell location independent. Because this worksheet will be sorted and cell location re-arranged and moved. That mean for example, In one worksheet, I set variable numb = 5 other cell at different location value display will have function such as numb*2 = 5*2 = 10 Then, other time, I set this same variable numb = 12 then, the call value will display numb*2 = 12*2 = 24 Thank you. You might try a named formula. From the Menu Bar, <Insert> <Name> <Define> In the "Names In Workboo...

Limit change to specific ranges in VBA routine
Hi, Bob Phillips had a wonderful VBA routine which I have adopted and modified, and it works fine. However, I want to limit the range of the routine to only work if the change is made in the following ranges (D8:D27; D29:D44; D46:D68) rather than to occur anywhere in column D (which is column = 4 in the routine). Any ideas? I am using Excel 2000 Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error Resume Next With Target If .Column = 4 Then If .Value = "a" Then .Value = "q" .Font.Name = "M...

Problem displaying Cell Shading color on Sony VAIO
When I open Excel documents that have Cell Shading the colors are not visible on the LCD screen. When I use the Print Preview I see the colors. I'd like to be able to see the colors while I'm editing on my Laptop. The background colors are visible on my desktop. I have tried several different screen modes with negative results. I've also download and installed all the latest service packs from Microsoft. Please help! Thanks much! MS Article found at http://support.microsoft.com/default.aspx?scid=kb;en-ca;Q320531 OFF: Changes to Fill Color and Fill Pattern Are Not Displa...

Converting custom cell properties to text
Hi, I have a column of cells with telephone numbers in it and I wish to convert - or copy and paste these cells - into text format. For example, I have a telephone number as 083270000 and the cell properties (Format > Cells > Category) is listed as "Custom" and the "Type" is listed as "000000000". When I conduct a copy and paste, I lose the preceding zero before the eight. But I need this to remain and I need all the cells in "Text" format! Any ideas on how to do this? Regards Hnelg Format the column as text-go into Format>cells>cu...