How do you write the formula that will display only the latest entry in a succession of entries? For example, if I have 12 months in 12 columns, and the 13th column specifies "Last Update" , if I have made entries for Jan, Feb, Mar so far, I need only the latest and last entry to appear in the 13th column. When I enter April, then it would display in the 13th column cell and so on. Please help. -- Thank you

0 |

3/22/2010 1:20:01 PM

Hi, This returns the rightmost entry in the row A2:M2 =LOOKUP(2,1/(A2:M2<>""),A2:M2) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Steve" wrote: > How do you write the formula that will display only the latest entry in a > succession of entries? For example, if I have 12 months in 12 columns, and > the 13th column specifies "Last Update" , if I have made entries for Jan, > Feb, Mar so far, I need only the latest and last entry to appear in the 13th > column. When I enter April, then it would display in the 13th column cell and > so on. > > Please help. > -- > Thank you

0 |

3/22/2010 1:25:01 PM

Assuming the entries are numbers: =LOOKUP(1,1/(A1:A12<>0),A1:A12) Will no 'jump' blank cells =LOOKUP(REPT("z",255),A1:A12) will return the last text entry of Sheet1 column A best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Steve" <Steve@discussions.microsoft.com> wrote in message news:5C04747A-2B9B-43F6-85CE-67F7566DAA8C@microsoft.com... > How do you write the formula that will display only the latest entry in a > succession of entries? For example, if I have 12 months in 12 columns, and > the 13th column specifies "Last Update" , if I have made entries for Jan, > Feb, Mar so far, I need only the latest and last entry to appear in the > 13th > column. When I enter April, then it would display in the 13th column cell > and > so on. > > Please help. > -- > Thank you

0 |

3/22/2010 1:34:13 PM

Hello, this is my VBA Macro: I want to select "clastrow" as my active cell. is that possible ? Thanks, Sub SumM() Dim clastrow As Long Dim olast As Range clastrow = Cells(Rows.Count, "I").End(xlUp).Row MsgBox clastrow Set olast = Range("I" & clastrow) isum = Application.WorksheetFunction.Sum(Range("I4:I" & olast)) ActiveCell.Value = isum End Sub Not quite Jeff, You get the last row, cLastRow, but then use that cell rather than the row in the formula. Try Sub SumM() Dim clastrow As Long Dim olast As Range Dim iSum clastrow = Cells(Rows.Co...

I have a form that contains NAME in cell A:1, and GENDER in cell B:2. I want to build a library of generic statements along the lines of: NAME tries hard, but if HE/SHE tried harder HIS/HER results would be much better. How can I make these statements get NAME from A:1, and derive HE or SHE and HIS or HER from GENDER in B:2? =A1&" tries hard, but if "&IF(B1="M","he","she")&" tried harder "&IF(B1="M","his","her")&" results would be much better" -- HTH Bob (there's no ...

Excel ver 2000 I would like to use the Sigma symbol as a character of a Column heading. I have two questions: Does Excel have the ability to display these symbols as text? What is the process for doing this? I tried looking at the WingDing fonts but could not find the symbol. Where can I obtain a keyboard mapping of the WingDing fonts? Thank you You can see it in Internet Explorer at http://www.mvps.org/dmcritchie/rexx/htm/fonts.htm you can make Firefox show same if you add fonts, and you will have to redo each time a new version of Firefox is installed. http://www.mvps.or...

Today at work I was writing an Excel formula that solves a cubic formul (i.e. the very advanced, tedious version of quadratic formula where cubic term is also in the equation). Anyway, small errors continually frustrated my attempts to correctl write what is basic algebra, it just requires on the order of 50 or 6 parentheses. So my question: Has someone devised an excel version of the cubic formula? or: Is there a program that automatically writes Excel algebraic formula from some other program where it is easier to write comple mathematical statements? Thanks! Jos -- Message posted f...

Hello, I've inherited a line chart. I successfully changed the data range last month by dragging the outline box. This month, though, I can drag the outline box, but in the chart, the value won't show up. Does this make sense to anyone? Thanks! ...

Hello there - Im looking for some help with a lookup. I have two spreadsheets. One with a simple list of numeric values: Spreadsheet A 1004 24965 66435 25459 7785 1008 26885 The other has multiple values in a single cell: Spreadsheet B 1004,24965,66435,25459 7785 1008 26885 The delimiter does vary, but it is always a non numeric. Id like to be able to find the row number in spreadsheet B where m value from Spreadsheet A appears. Thanks very much for your time. Andrew -- boneshom --------------------------------------------------...

I want to copy formulas without changing the cell references and not relative without the $ signs. Example: In C1 I have =A1+B1 (actually mine is complex). I want to copy it as =A1+B1 to D1 D2 D3 (and many more) is there a clever way to do this. Thanks Mark Mark One way: 0. Make a copy of cell C1 by selecting C1. Select the formula *in the formula bar* and press <Ctrl>c and <Esc> Put the formula in a unused cell with <Ctrl>v 1. Make all references in C1 absolute, i.e. =$A$1+$B$1 (Use <F4> for a fast way of doing it, i.e. in the form...

I wish Microsoft stopped developing "helpful" programs. All their "auto" stuff leads to nothing but problems. Here is an example: I have to process data which come as CSV files and contain entries that look like multiplication e.g "3*4". Damn Excel can't handle these. Every chance it gets, it turns them into "=3*4" and displays "12". Any one knows how to kill this behaviour? Please share the knowledge. Henryk Birecki Henryk Birecki wrote... >I wish Microsoft stopped developing "helpful" programs. All their >"auto&q...

This was your questio Hi I want to link all the points in my xychart with the rows of the data that are in the chart. i.e. after a mouseclick on the chart data the user will be redirected to the data row The idea is to create a link to a text cell, situated in the same row as the data, which explains the details to each point Thanks in advance for your help Mattia ...

How do I put whoever saved last on the footer or better yet whomever is using the file and prints it. Hi Ismael, Do you mean the network user's name or the Excel user name. If the latter, then put this code in the ThisWorkbook code module Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.LeftFooter = Application.UserName End Sub If it's the former, it's a bit more complicated, but not too much Private Declare Function GetUserName Lib "advapi32.dll" _ Alias "GetUserNameA" _ (ByVal lpBuffer As String, _ nSize As Long)...

The fomula is a mix of VLOOKUP and SEARCH: =VSEARCH(A1;table1!A:A;2;false) In the A1 is the Text “Shell GmbH” In the A2 is the Text “Deutsche Shell” In the A3 is the Text “Shell LTD.” In Table1 Colum A it says Shell In Table1 Colum B it says Shell Group The result would be that the formula would give back always “Shell Group”. The option “true” would even accept “Shel” or “Schell” Thanks, Eckhard ...

I have a list of names with email addresses of attendees of a seminar series that I am trying to compile into a list that can be copied and pasted into an email (using BCC as the methodology for emailing). The challenge that I have is that when these files were exported from the database to excel (not sure how), all of the cells contain exactly 100 characters even though the email address may only contain 13-20 characters. Is there a way to "trim" the blank characters after the last character that is used by a letter instead of blank character field? Thanks in advance! PZan --...

I'm using Excel 2000 and XP Pro. I'm formatting a text page whereby users can input text into a column and I want the text to wrap so that whatever the length of their text we see it all. On a default page it works fine. However I tinkered with the row height for font purposes and now the text will wrap but the row won't expand to fit the whole text. How do I reset the row/sheet or whatever to get the display I want? Thanks -- Stilson Snow There is a limit to how much text will display within a cell and how much text displays in the formula bar. Goto excel help and enter...

I need to remove specified data from a single cell and place on the same row in another column. Not all data will be the same eg 100mg, 200mg etc Not too clear; what data? Do you want to extract the number from the text? This will extract 100 from 100mg and 5 from 5mg: =--LEFT(A1,FIND("m",A1)-1) Any help? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "rogera" <rogera@discussions.microsoft.com> wrote in message news:98106D4C-04D2-4A2D-B293-9F5BBF0F04EB@microsoft.com... >I need to remove specified data from a single cell and place on th...

Is there any way of stopping someone getting the values of locked hidden cells on a protected worksheet by linking to them on another worksheet? "John Bundy" <jmbundy@gmail.com(remove)> wrote > The right person can get into anything they want regardless of protection, > the more secure you make it the more people you stop but there are always > some. As far as linking them to another sheet, if they will break > protection > on one it is just as easy to do on two so I wouldn't bother Thanks. It seems ridiculous that you can hide data by protecting the...

I have 2 spreadsheets (Sheet 1 and Sheet 2). Sheet 1 has one header row and sheet 2 has 2 header rows. I am using the following to count the number of rows in Sheet 1 and copy a formula to every row in Sheet 2 based on the count from Sheet 1. With Worksheets("Sheet1") lStop = .Cells(.Rows.Count, "A").End(xlUp).Row End With Worksheets("Sheet2").Range("3:" & lStop + 1).FillDown The formula is perfect except when I only have one entry in Sheet 1. In this case the formula copies the second header row in Sheet 2 instead...

Is it possible to set formulas to deactivate after a given period o time? I email out a number of spreadsheets that have taken many hours/days t assemble to clients. If they choose to change suppliers (as one recentl did) they can simply use my spreadsheets and change the logos and th new supplier gets all the benefits without the pain! The value of the spreadsheets (the useful life of the data) is only few days so it would be useful if the formulas stopped working afte maybe a month. Any ideas/suggestions would be appreciated Ton -- y_no ------------------------------------------------...

Hi all: I was wondering if there was a way to put a conditional formating on a cell based on the values of another cell. What I mean is that, if Cell A is >5 , make cell b Blue, not just cell A. Likewise, if Cell A is <5, make both cell a and b red. On the same topic, is it possible to put more than 3 conditions on a conditional formating? Thanks, Steve o This might point you in the right direction. Sometimes you need a row formatted according to the item in A1. Use a formula in the conditional formatting in type in something like this... =$A1<5 This will apply the cond...

I have a workbook that I created for tst item analysis. On the main worksheet is used to enter sudent's response for every multiple-choice question. on another worksheet which is the summary, I wrote a formula that allows me to sknow how many students choose choice 1, choice 2, choice 3,choice 4 for a given question. My challenge is to click on that number of student in the cell to display the list of students who chose cjoice 1, 2, 3, or 4. How canI do that please? -- Ti Moy Here's a formulas model to achieve the underlying objectives: http://cjoint.com/?chjHCSWehb ...

In COL A, I have a list of 2 and 3 digit charactors. I would like to seperate each string into the three adjacent columns. KQ AJs TT 89s KQ K Q AJs A J s TT T T 89s 8 9 s I know that I can use the Left function for the first col, but cant figure out how to get just the middle or third letters. Thanks in advance, Andrew Check your last post! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------...

Excel 2003. I'd like to change an entire range of cell formulas from relative to absolute. Mark 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 For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlAbsRowRelColumn) ...

Hi, Not sure this is possible but...I have a cell that has a number range in it and based on an amount in another cell want to calculate a new range. For example: Initial Range: A1 = 10 - 12 Calc Amount: A2 = 5 Final Range: A3 = 50 - 60 I think I can get the results by concatenating two formulas I'm just not sure how to enter the original numbers (A1) or how to distinguish between the two in the final formula (A3) Using Excel 2003. Hope this makes sense. Thanks. I would put the range in two different cells (eg A1 and B1). Then the multiplication is easy. If you ...

I am trying to make a barcode that will enter a static time into a cell and move to the next cell (right) I have 3 of 9 barcode fonts. There is no = sign nor is there a symbol for it. Does anyone know how to do this. Other choice would be a button on a toolbar but would like to not have the person return to the computer each time to enter. Any help please. pgriff -- pgriff ------------------------------------------------------------------------ pgriff's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26189 View this thread: http://www.excelforum.com/showth...

I have a simple "if/then/else" formula that I would like to display the result of the "else" portion with no decimal places showing, instead of the default value of the cell. Can I include the format in the formula? Here is my formula: =IF(D12>75,D12/E12,D12) Thanks, -- Zilbandy - Tucson, Arizona USA <zil@zilbandyREMOVETHIS.com> Dead Suburban's Home Page: http://zilbandy.com/suburb/ PGP Public Key: http://zilbandy.com/pgpkey.htm ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Hi, Try =IF(D12>75,D12/E12,INT(D12)) or replace Int with roun...

Hi folks, I have a urgent help request and question. How do I find out the Journal Entry number for an Inventory Adjustment transaction that did not post to G/L? You know when you post an Inventory Adjustment Batch, if you don't check the box "Post to General Ledger" the inventory adjustment will not post in G/L. So how do we find out what the Journal Entry number should have been? Here's the catch, don't have access to SQL Server, so we need a way to find out within GP. Is this possible? Please help...THANKS It sounds like you are trying to find out whether an in...