Function To View Only Latest Set Of Comments For Any Row
Feb 26, 2013
I import a report to an excel template. Any comments which get bought across are date stamped and appended to the end of any pre-existing comments. The date stamp is in the format [DD-MM-YYYY].
I would like a function which would allow me to view only the latest set of comments for any row.
for example
[26-02-2013] Latest comments [01-02-2013] Previous comments.
The function would thus delete anything after the and including the second [ leaving the following and moving onto the next cell in the range.
View 3 Replies
ADVERTISEMENT
Feb 27, 2014
On sheet1, I have numbers and names. These names are also contained in Comments on sheet2, and i would like a function in Column M that matches the numbers with the names in the comments. Cell M1 I have manually filled for demonstration purposes.
Can this be done with a function or is this a VBA task?
View 7 Replies
View Related
Mar 28, 2013
I have this code, which is producing an error that says, "Only comments may appear after End Sub, End Function, or End Property. I understand the error, but what Im unsure about is how to begin the subroutine in a way such that
1. This error goes away
2. What is the correct method name for the next subroutine
(i.e.: Private Sub Declare_Variables()
How to emcompass the subroutine. The error is happening in the below code at the "Public Cat1 as Variant", "Public Cat2 as Variant".
Code:
Option Explicit
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("View Lessons")
[Code] .....
View 4 Replies
View Related
Aug 13, 2013
Why do I get the below error when I open my workbook? The highlighted function is below.
Private Sub CommandButton1_Click()
Dim myText As Variant
If TextBox1.Value = "code" Then
UserForm1.Hide
Else
Me.Label1.Visible = True
Me.Label1.ForeColor = vbRed
End If
End Sub
View 1 Replies
View Related
Oct 9, 2009
I have spreadsheet that I use to display quarterly metrics. Within the workbook, there are two spreadsheets; (1) output (2) data.
Part 1: I want the ability to view data for any quarter of any year. In columns O-P, I have created a dropdown for the year I want to view and option buttons for the quarter I want to view.
The data is organized in one sheet and includes all information by quarter.
As an example, say I want to display data for District 112 and I want to display data for the 3rd Quarter of 2007. I am trying to create a formula that would look in the table as follows: (1) look for district 112 then (2) look for 2007 then (3) look for 3rd quarter then (4) look for units sold. I tried using a modified lookup function but didn’t have any luck.
Part 2: The second part of my question is similar to the first part but in this case I need to summarize data (cells $B$18:$D:$22).
As an example, say I want to display the total number of sales in the U.S. for the 3rd quarter of 2007. In this example, I would look in the table for the following: (1) look for Division then (2) look for 2007 then (3) look for 3rd quarter then (4) sum all based on criteria.
View 4 Replies
View Related
Feb 28, 2013
how to create a function in VBA that works exactly as SUMIF, except it will also add a comment in the cell that references the formula, where the comment would be a list of the individual cells being added? Basically the comment would read "2+4+5+6" or "2,4,5,6" or something along that.
View 1 Replies
View Related
Dec 12, 2013
Excel 2007 - need to adjust protected view settings but the protected view tab is missing from my Trust Centre settings - have the following tabs - trusted publisher / location, add-ins, activeX, macro, message bar, external content and privacy options. Document is a revenue authority download and without being able to adjust the protected view settings, can't input data.
View 3 Replies
View Related
Feb 4, 2014
How would you prevent the copy/paste of cells that have comments?
Also, how would you allow cells with comments to be copied and pasted without pasting the comments?
I also have an aside question about the forum advanced search. When searching for multiple search words, how would you type the search to include all words, for example, "prevent" & "paste" & "comments".
View 7 Replies
View Related
Mar 5, 2007
i want to create a function / macro for this scenario:
if ID123456 has the status open dated 01/05/07 and the user later decided to change the status of ID123456 to closed on 03/05/07. The graph should count the number of closed then lessen the count of open because the previously open ID is already closed.
View 9 Replies
View Related
Aug 28, 2008
I have a list of fixtures and results arranged by date for a soccer league and have a forumla that I drag down beside each game which creates a value for each side that was competing.
I need for the result of those calculations to become the starting point of the next calculation involving each team, and so on. Is this at all possible?
Would I need a macro to do this or can I get excel to recognise these values and enter them for me?
View 9 Replies
View Related
Dec 5, 2009
I've got a multi column sheet containing, amonst other headings, "Date", "Transaction" and then a rolling "Balance". I have multiple rows with the same date and what I'm simply trying to do is have a formula at the top of my spreadsheet that finds the very last row that has data entered so that I can then extract & display the balance as at that point.
Date / Transaction / Value / Balance
01-12-09 / Salary / +£1,000 / +£2,600
01-12-09 / Fuel / -£50 / +£2,550
01-12-09 / Gift / -£100 / +£2,450
03-12-09 / Food / -£30 / +£2,420
03-12-09 / Interest / +£2 / +£2,422
In this example I'd want to return row 5 and then do some kind of lookup to retrieve the value £2,422...
View 9 Replies
View Related
May 30, 2014
see attached for the sample data. I'm looking for: Sat Score (from column F), that is from the latest/max Report Date (column A), for a Project (column B), THAT IS NOT BLANK
In other words: For each project, we want to know the score from the latest report. But sometimes the latest report doesn't have a score, so we want the score from the latest week that actually has a score. If none of the weeks have a score, return blank.
View 4 Replies
View Related
Jun 10, 2014
Sheet 1 is filled with names(column C) and dates(column X). On the sheet 2 I want the latest date for each names. I tried the below formulas. But it is not working.
=IF(ISBLANK(EWS!$X$2:$X$101)," ",MAX(VLOOKUP(Lookup!$D2,EWS!$E$2:$X$101,20,0)))
= MAX(VLOOKUP(Lookup!$D2,EWS!$E$2:$X$101,20,0))[code]....
View 7 Replies
View Related
Aug 4, 2014
I'm needing to finding the latest date, of each unique ID (and Name, Criteria).
In my attachment ('Table'), the "Newest" column with the result of "Yes" is what my calculated result should be.
Any formula here (outside of a Pivot)? Because, I then need to delete all of my "No" results from the list.
View 3 Replies
View Related
Mar 27, 2009
I have a sheet (attached, Excel 2003) that I need an assist on. What I want to do is search the 'All Games' worksheet for the ten latest games a specific player has played.
That by its self might not be so hard, but I want to take those 10 rows (once identified) and take the sum of column G and divide by the sum of column F for those rows only.
I already have a concatenate cell in the 'All Games' sheet for each row that I use to as a link of sorts to get data from column F and G onto the 'Spring 2009' sheet. The way I have the concatenate set up, I can manually sort by it and get the 'latest 10' values I need, but I need to get it all into a formula to place on the 'Spring 2009' sheet without the manual sort.
View 7 Replies
View Related
Apr 22, 2014
Column A | Column B | Column C |
UMB-RPT-01 | 0 | In Review |
UMB-RPT-01 | 0 | Pending Markup |
UMB-RPT-01 | A | Comments - Correct and Resubmit |
UMB-RPT-01 | A | Review Complete |
UMB-RPT-01 | B | Approved |
UMB-RPT-01 | B | Review Complete |
I need to have a macro run through a spreadsheet and look through column a, which is sorted, and then highlight the row that has the latest record. IN the case above it would be 0 with Pending Markup
So the latest value in Column B, whether its alpha or numeric, with a status in Column C of Pending Marking.
View 1 Replies
View Related
Feb 21, 2008
I would like to create a macro that looks in a specific directory "h:data"
It finds the 6 most recently modified files in this directory (csv files)
Then imports these 6 files into seperate worksheets in the same workbook "sorter.xls"
View 9 Replies
View Related
Dec 21, 2008
i have this kind to table
200
100
250
65
and i want the latest value "65" will appear in the follwoing cell
and when more values added like if the value "70" added then in the cell the value "70" will be appear.
View 9 Replies
View Related
Nov 4, 2009
XYZ001
XYZ002XYZ003
XYZ004XYZ003XYZ004XYZ001XYZ002XYZ004
I have data in above format (including Dup). Is there anyway I can get the postion of latest entry. say if it is xyz011, it should return 8. Match will start counting from first cell and go downward. How can do a inverse counting?
View 9 Replies
View Related
Nov 26, 2009
I have a data field of about 2000 rows & 30 columns.
These contain records on accidents for 56 offices.
One of the columns contains the date an individual had the accident.
There can be a number of records for each office as there are a number of employees in each office who have had accidents over the last few years.
I need to be able to run a macro or set up filters or formulae to show the row containing the most recent accidentin each office, by office. I also need to display the total number of days since the last accident occurred at the end of each row.
The offices are listed in Column B & the date of the last accident is listed in Column V.
View 9 Replies
View Related
Mar 14, 2008
If I have a row full of dates and I want to find the most recent date, how do I do that?
View 2 Replies
View Related
Dec 24, 2013
How to find the latest price by each sku in the list, the list contains many skus.
View 13 Replies
View Related
Jan 14, 2014
formula that can pull the latest date for a product, if there are multiple dates attached to the product?
For example, in the attached, I have a Customer and Deal Date list, for Customer A, there are 3 dates, but I only want the latest date: 2/14/2014.
View 4 Replies
View Related
Feb 3, 2014
I have a list of values which also has duplicates. I want to remove the duplicates but after comparing it to the dates. Retain the latest date and remove the old dates Eg. there are two duplicates, one has a date of 31st Jan 2013 and the other one has a date of 25 jan 2013. I want to remove the duplicate which has the older date ( 25 Jan 2013) and keep the one which has the latest date.
View 2 Replies
View Related
Feb 18, 2014
I've attached a quick sample, I have two customer ID's, AB1 and AB2, I want to be able to put a formula into column C that will return the latest date for each customer. So AB1 will show 01/04/2013 and AB2 will show 01/05/2013. I know I can use a MAX formula but I don't know how to connect it to the specific customer ID.
Max Date Query.xlsx
View 3 Replies
View Related
Nov 7, 2013
I have a table like this:
Book, Loaned to, Date
A Tale of 2 Cities, John, 1/1/2010
Oliver Twist, Mary, 2/3/2011
Great Expectations, James, 4/6/2011
Oliver Twist, Greg, 6/2/2010
I want to make it such that, when I input the name of the book, it will tell me the person whom I lent it to last and the date of the transaction. Preferably, this should be done without any assumptions on how the table is ordered. For example:
Book= Oliver Twist
Loaned to= Greg
Date= 6/2/2010
View 2 Replies
View Related
Mar 4, 2014
I have a table as below, and I would like to keep only the latest date for each EMP_NO. For example:
001VICE PRESIDENT A062004-01-01
031SENIOR VICE PRESIDENTA042004-01-01
...
....
....
=======================================
[EMP_NO JOB_TITLE DEPTCHANGE_DATE
014 ASSISTANT VICE PRESIDENT A01 2004-01-01
001 MANAGER A062000-03-01
001 MANAGER A062001-06-01
001 VICE PRESIDENT A062004-01-01
076 VICE PRESIDENT A022003-04-23
013 DIRECTOR OF MARKETING A01 2000-06-19
031 FIRST VICE PRESIDENT A042002-01-01
031 FIRST VICE PRESIDENT A042003-01-01
031 SENIOR VICE PRESIDENT A042004-01-01
=======================================
View 5 Replies
View Related
May 15, 2006
I have a series of rows 14000+ lines strong in an excel sheet.
The series contain caribou location reports from those caribou that have a
collar attached to them.
Along with other information, each row contains three important columns
related to this question. Collar Number, Date and LC (LC = Position Fix
quality or Signal Strength).
I need a formula that will find the best and latest signal strength for each
collar and for each day!. The formula should result in a 0 or 1. (no or yes).
There are about 30 different collars each reporting on certain days. The LC
values are 1 to 7 with 7 being the strongest.
View 14 Replies
View Related
Jul 25, 2007
am looking to find the most recent value in a series of cells that I define. I have set up a worksheet for each month and I want to only pull out the latest total entered for a particular cell reference (cell B16 in each worksheet has the number I need).
I tried using : =LOOKUP(9.99999999E+307,Jul:Jun!B16) but that just gives me a #value error.
I had a search through the archives too and found [url] and [url](neither of which I really understood or could use, although I did have a go), anyone got any ideas? Am sure it's very easy!
Just to clarify, I have 12 worksheets, each with a different month. Each month I enter data into the relevant worksheet. I want a running total on a summary worksheet and need to pull out the latest numbers automatically (they don't need to SUM or anything).
View 9 Replies
View Related
May 4, 2008
I have a list of teams in 2 columns (columns A and B). These teams play matches against each other. The teams repeat themselves but vary in their order in which they play and whether they are home or away; i.e. appear in column A or B.
Each team has a points brought forward value in column C or D which correspond to the teams in column A and B respectively. They also then have a carried forward points value in column E or F produced after the points from the current game (not shown in the attached file).
I need to be able to find a formula which will retrieve the latest (last match) carried forward value for a team and enter it into the brought forward cell. This is made difficult because the team may last appear in columns A or B and there is no logic as to when they last played. It is easy enough to use lookup to find the value from the first row of an array but i cannot work out how to find the value in the last row.
View 10 Replies
View Related
