Formula to find last used cell

I want to populate a cell in sheet1 by going to sheet 2 column F and finding 
the value of the last filled cell that is not a zero 


0
9/20/2006 1:55:42 AM
excel 39879 articles. 2 followers. Follow

3 Replies
253 Views

Similar Articles

[PageSpeed] 48

I'm assuming all values in column F are numbers.

=LOOKUP(2,1/(Sheet2!F1:F65535<>0),Sheet2!F1:F65535)

You can't use entire column references in this portion: 
1/(Sheet2!F1:F65535<>0).

I would use a more reasonable range size.

Biff

"Yogi_Bear_79" <nospam@spamsux.com> wrote in message 
news:PYadnelLcYpqAo3YnZ2dnUVZ_u2dnZ2d@comcast.com...
>I want to populate a cell in sheet1 by going to sheet 2 column F and 
>finding the value of the last filled cell that is not a zero
> 


0
biffinpitt (3172)
9/20/2006 4:12:19 AM
Thanks, it works, however when the colum is blank it returns the column 
header from F1, I'd rather it return a zero or nothing in this case.

=LOOKUP(2,1/(Sheet2!F2:F27<>0),Sheet2!F2:F65535)

"Biff" <biffinpitt@comcast.net> wrote in message 
news:uods4rG3GHA.1568@TK2MSFTNGP03.phx.gbl...
> I'm assuming all values in column F are numbers.
>
> =LOOKUP(2,1/(Sheet2!F1:F65535<>0),Sheet2!F1:F65535)
>
> You can't use entire column references in this portion: 
> 1/(Sheet2!F1:F65535<>0).
>
> I would use a more reasonable range size.
>
> Biff
>
> "Yogi_Bear_79" <nospam@spamsux.com> wrote in message 
> news:PYadnelLcYpqAo3YnZ2dnUVZ_u2dnZ2d@comcast.com...
>>I want to populate a cell in sheet1 by going to sheet 2 column F and 
>>finding the value of the last filled cell that is not a zero
>>
>
> 


0
9/21/2006 2:48:03 AM
>Thanks, it works, however when the colum is blank it returns the column 
>header from F1, I'd rather it return a zero or nothing in this case.
>=LOOKUP(2,1/(Sheet2!F2:F27<>0),Sheet2!F2:F65535)

If that's the actual formula you're using there is no possible way for it to 
return a header from cell F1!

Try this to return a blank:

=IF(ISNA(LOOKUP(2,1/(Sheet2!F2:F27<>0),Sheet2!F2:F27)),"",LOOKUP(2,1/(Sheet2!F2:F27<>0),Sheet2!F2:F27))

If you want a 0 instead, replace the double quotes "" with a 0.

Biff

"Yogi_Bear_79" <nospam@spamsux.com> wrote in message 
news:BOudnZofKNVKYIzYnZ2dnUVZ_qCdnZ2d@comcast.com...
> Thanks, it works, however when the colum is blank it returns the column 
> header from F1, I'd rather it return a zero or nothing in this case.
>
> =LOOKUP(2,1/(Sheet2!F2:F27<>0),Sheet2!F2:F65535)
>
> "Biff" <biffinpitt@comcast.net> wrote in message 
> news:uods4rG3GHA.1568@TK2MSFTNGP03.phx.gbl...
>> I'm assuming all values in column F are numbers.
>>
>> =LOOKUP(2,1/(Sheet2!F1:F65535<>0),Sheet2!F1:F65535)
>>
>> You can't use entire column references in this portion: 
>> 1/(Sheet2!F1:F65535<>0).
>>
>> I would use a more reasonable range size.
>>
>> Biff
>>
>> "Yogi_Bear_79" <nospam@spamsux.com> wrote in message 
>> news:PYadnelLcYpqAo3YnZ2dnUVZ_u2dnZ2d@comcast.com...
>>>I want to populate a cell in sheet1 by going to sheet 2 column F and 
>>>finding the value of the last filled cell that is not a zero
>>>
>>
>>
>
> 


0
biffinpitt (3172)
9/21/2006 5:35:19 AM
Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Extending formulas
Subject: Extending formulas Hi, For my application that uses Excel for calculations. I need to be able to extend the forula base of Excell with complex scientifc functions. Is there a way to add new functions to the Excel function base? Thanks Spx. MS has provided Visual Basic for Applications (VBA) to customize Excel with new functions, commands, forms, menus, etc. Tools|Macro|Visual Basic Editor From the VBA editor Insert Module Then write your functions in VBA. Details of writting functions in VBA is a very big topic, http://www.fontstuff.com/vba/vbatut01.htm may help y...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula ($L$1). Currently the formul is:=VLOOKUP($A$1,$AD$7:$AG$44,IF($L$1="January",2,IF($L$1="February",2,IF($L$1="March",2,IF($L$1="April",2,IF($L$1="MAY",4,IF($L$1="June",3,IF($L$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

Subquery
Hi, I'm trying to code a subquery on a table but to no avail...it's a very simple table that has Date (short date, just recording the day) and NAV which is a number (double). All I'm wanting to do is have a query that displays the Date, the NAV and the NAV from the day before - only recording business dates. In other words, the subquery number I want is the NAV from the record preceeding the current one. I've got as far as the below, but that just gets the same NAV number as today's: SELECT tblNAV.Date, tblNAV.NAV, (SELECT LAST(x.NAV) FROM tblNAV as X LEFT JOIN tblNAV on...

Formula Problem?
I am using Excel 2000 with Windows XP. I am having a problem. I am on Sheet 2 of my workbook. I have SSN on a sheet named Employees in the same workbook. I need to take the numbers on the Employees Sheet and transfer it to the sheet 2. I know how to do this. It just won't work. This is a copy of my formula. =SUM(Employees!C3) This should take the SSN that is in the C3 cell on the employees sheet and place it at the cell where the formula is typed. When I put this formula in the cell I am getting just a "0". Please help. =Employees!C3 -- Kind regards, Niek Otten...

formula auditing/macro
Can anyone give me the sytax to goto - special - precedents so I can create a macro so I can assign to a hotkey and dont have to go through 4 steps ? Thanks, Yosef With A1=1 and D2=2*A1, and D1 as active cell: I recorded a macro for these steps: Edit|GoTo->Special->Precedence And the macro contained just one line: Selection.DirectPrecedents.Select best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ynissel" <ynissel@discussions.microsoft.com> wrote in message news:DA544BDE-3717-4953-A5E3-06191BC28373@microsoft.com... > Can anyone...

find instance of IE for server socket in activex component
Hi I have an activex component that has a server socket (using casyncsocket class) ..The problem is that when I have multiple instances of the IE open only the first instance (created first) recieves the messages the rest do not recieve the message. Is there a way to solve this problem .(i want the server send the reply to that instance of ie that sent the message) .Do I need to pass something like a pointer of hte javascript object .If so how Thanks When you say "server socket", that suggests a socket that does a Listen followed by an Accept. Otherwise it is a client socke...

formula to have 0 to 6.5 only in ref to other cell val of 0<=6.5
...

find data and autopaste when found
Hi, Can someone help me how to do this : For checken the backorders of our customers we can extract a list fro our SAP system. this list is always different and shows us ever product per customer in Back order. ex. Customer A has product 1 en in backorder. This gives 2 lines in the xls file. can excel put th name of the customer on a form and it's backorders automatically. Ca it create for each customer showing in the list a new form? thanks koenraa -- Message posted from http://www.ExcelForum.com ...

SQL query / Formula for Reorder Point?
Is it possible to issue an SQL command(s) that would: 1. Set the Restock Level for all items to be equal to the current on-hand quantity for each item. 2. Set the Reorder point for all items to be equal to 1/3 of that items Restock Level. I'm currently doing this manually, or using a formula in excel when items are initially imported into the DB, but there are still several thousand that need to be retro-fitted like this... Thanx in advance! -=RFM=- UPDATE Item SET RestockLevel=(Quantity - QuantityCommitted) UPDATE Item SET ReorderPoint=ROUND(RestockLevel/3,0) - Evan Culver Ne...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...