Is it possible to set something up on a spreadsheet which shows who last updated a cell and when they did it? I have a huge spreadhseet with lots of data on which gets updated by a number of users, they update a lot of different cells and this leads to errors being made with the data in the spreadsheet.
is a way I can show who updated a cell and what date - is this doable?
I have created a spreadsheet that displays new dates in column D based on provided dates in Column B and recurrence intervals selected in column C. The dates in column D are when a training deadline occurs. I have been trying to get a macros written so that when a cell in column d is updated, an outlook appointment message is sent to the person doing the updating. The idea here is that anyone can use this without future coding (so their address would be placed in cell say A1)
So far all i have gotten is excel to send an email to me with the excel spreadsheet as an attachment, not really what I want. Again, I dont want somethign specific to my computer settings, I want to be able to send the spreadsheet out with simple instructions for the user to change their email in cell A1 and then every time a cell changes in column D, it automatically generate an appointment reminder for the new date.
I currently have a spreadsheet that my deparment use at work. Its just a full time equivallent sheet, nothing fancy. However I added code onto it to prevent users opening it as I was updating it.
However I went to open it and It wouldnt open. It wouldn't recognise me as being the exception. Its just a code on the workbook open that says if user is "slb090" (which is me) then do nothing, else msgbox and just a quickie sayign come back later".
I have attached the spreadsheet, in the hope someone can open this for me and remove the message so I can open it!
I have a spreadsheet which connects to a SQL Server and updates a data set.
Is there a way to place a function or a formula I can use to show when the data set was last updated?
I have 2 sheets in my excel spreadsheet. One tracks data for a number of projects five different employees are working on. The other sheet is where I want to total up the number of minutes each employee has worked on their individual projects. I tried writing an IF statement like below but I am only getting the total in the first field even if the employee's name is not Employee 1....
[Code] .....
How I can write this so their totals show up in the correct row?
I am working on Excel 2010. I want to find a way to link data from one spreadsheet to another one and whenever I update the first spreadsheet, the second one will be automatically updated?
View 2 Replies View RelatedI am trying to create a spreadsheet that will show vacation time; accrued, taken, leftover. There are different accrual rates for years of service, and caps on carryover.
I have been trying to figure this out, however the accruals for employees over 1 year are higher than they should be.
Breakdowns are as follows:
1-2 years - 1 week vacation - 40 hours eligible for carryover each year
3-4 years - 2 weeks vacation - 80 hours eligible for carryover each year
5-9 years - 3 weeks vacation - 120 hours eligible for carryover each year
10+ year - 4 weeks vacation - 160 hours eligible for carryover each year
If in Cell A2 I have a function that updates it's number value automtically (i.e. From 5 to 7) and in cell B2 I have a value I wanted subtracted to the value Cell A2 updated by. Is this task possible?
In the I.e example, cell A2 had an updated value by 2, will it be possible to subtract that value in Cell B2 and continue doing so if Cell A2 kept updating?
I need to keep a cell (or sheet) updated second by second. is there a way to do this?
View 9 Replies View RelatedI have code which when run thru manually in VBA says it has updated the contents of the cell in a sheet. Indeed the code all reacts as if it has updated the cell in memory, however the actual spreadsheet cell has not been updated. This has happened in the past and then magically starts working wthout change.
View 6 Replies View RelatedI am trying to show the date in a spreadsheet as the month and the year.
The date is in B2 30/04/2012 and i want it to show in C2 as Month/Year.
I am currently using =month(B2) which shows it as a no ( 4 ). Can i make it show as month / year?
I have a spreadsheet where vehicle milage is entered at the end of each month. On another sheet I have the vehicle fleet numbers and the the months along the top. What I need to happen is; when the mileage cell is updated I need the cell in the other sheet to be automatically populated, when the mileage is updated the next month I need the next cell(February)to be populated with the new milage and so on. The aim is to calculate monthly milage for each vehicle without having to enter the date at the beginning and end of the month.
Everyone I work with is completely computer illiterate and only knows how to enter the data the same way they always have(just the once at the end of the month) changing it up will only cause issues.
i want to link my stock portfolio to a spreadsheet to make various calculations on historic performance.
my problem is that it is the same cell thats get updated whenever a change occurs, so that there is no history.
let's say i put the DDE-link in cell A1, i would like B1 to be filled with that value
and then when the value changes i would like B2 to be filled with the new value
and the next time B3, and so on.
I am trying to do a query against my database using MsQuery, but I am having a problem with it...
One of the tables in my database stores information as Binary (Bits) instead of actual text.... When I create the Query in MsQuery, the text is displayed as desired...but when I send it to Excel that column is not displayed..
Does anyone have any ideas on how I can get it to send the column to Excel?
See attached for what I mean. The Bits column shows in MSQuery, but not in Excel.
I want to show numbers on my excel spreadhseet in thousands. However, the challenge is I want to show negtive numbers in red within brackets. For instance, I have a number -104,784,089. I want to display it as (104,784) in red color. Similarly, I want to show 577,090 as 577. Is there a customer formatting I can set up in excel that do that?
View 2 Replies View RelatedI wish to Automatically copy the TEXT that is written from Spreadsheet 1 cells D5 to F5 to Spreadsheet 2 cells F5 to J5 .... a similar range of cells.
Is there a formula I can use or do I need to venture into the programming side of things.
1. I created a "Top Category Data Dependent Validation List for sub-categories” in the attached spreadsheet. The selection from the Category drop-down list in column B, then drives or limits the choices in the Sub-category drop-down list in column C.
Credits: To achieve the above technique, I used the "Dependent List Validation" document as a technical reference.
the document is available from http://www.ozgrid.com/Excel/dependent-lists.htm
2. If the user updates the Category selection in Column B, then the value in the corresponding row in Column C should be "reset to a ( NULL/Clear) value," as to automatically prevent any human-error in forgetting to also update the Sub-category data in Column C.
(Optional Reading--the following steps are not related to steps 1 & 2 above, just notes regarding other data validation process created in this spreadsheet
3. I also created another column (D) that gets automatically populated with the Category ID,
based on the sub-category drop-down list selection in C.
4. Also in this spreadsheet, a macro checks for any "missing required data
before allowing the end-user to save this worksheet. As an example, In Column A, if there is a "RecordId" that exists in the same row, then the macro checks if the user has entered required values in Column B, C, "AND/OR" D. If the end-user did not enter any of the required values,
then an error message pops-up with this message:
"Cannot Save this file due to Missing Required Data. Please review highlighted record and complete missing data."
Credits:
Special thanks to:
-OzGrid Business Applications for writing code for a UDF (function that returns last word) that I used/modified a little bit to achieve Step 3.
-Carl (member name" carlmack") for his help in the methods I used in Step 4 above.
Contents formula =countif(B1:D1,"YES").
This works fine.
Column1 has simple formula to count if text is equal to 'YES'.
But when I double click any cell in column 1 to open userform and update values in column 2,3,4 then the formula in column1 does not work.
Sample book attached : UFM.xlsm‎
I am using the worksheet change routine for conditional formating of more than three items.
The problem is that it only changes if I manually type within the page and does not update if the cells are updated via formulas.
To explain a little more, I have three sheets that I combine into one to help find the best rate from three companies using the following formula:
=IF(AND(MIN(PMI!C4,UG!C4,GE!C4)=PMI!C4,MIN(PMI!C4,UG!C4,GE!C4)=UG!C4,MIN(PMI!C4,UG!C4,GE!C4)=GE!C4),FIXED(PMI!C4,2)&" All",IF(AND(MIN(PMI!C4,UG!C4,GE!C4)=PMI!C4,MIN(PMI!C4,UG!C4,GE!C4)=UG!C4),FIXED(PMI!C4,2)&" PMI/UG",IF(AND(MIN(PMI!C4,UG!C4,GE!C4)=PMI!C4,MIN(PMI!C4,UG!C4,GE!C4)=GE!C4),FIXED(PMI!C4,2)&" PMI/GE",IF(AND(MIN(PMI!C4,UG!C4,UG!C4)=GE!C4,MIN(PMI!C4,UG!C4,GE!C4)=GE!C4),FIXED(UG!C4,2)&" GE/UG",IF(MIN(PMI!C4,UG!C4,GE!C4)=PMI!C4,FIXED(PMI!C4,2)&" PMI",IF(MIN(PMI!C4,UG!C4,GE!C4)=UG!C4,FIXED(UG!C4,2)&" UG",IF(MIN(PMI!C4,UG!C4,GE!C4)=GE!C4,FIXED(GE!C4,2)&" GE","ERROR")))))))
Please feel free to condense this if you like.
So when I update a reference on one of the first three sheets, the cells update on this sheet but the cell colors never change. This is the code for the worksheet change
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel1 As Range
Dim Rng1 As Range
On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
I am having rows of data, that i will be updating from time to time. I want excel to move the latest updated rows, in any column if updated, to move to the top, to easiy know that i updated those records. It should be that when i updated more rows than one, then the first updated cell would be in lower, in order, than the latest updated cells. I do not want any cumbersome vba. I want in formula or in conditional formatting. The row no may be total not limited to some rows.
Because, you naturally would have updated the 200 th record and would have saved. It saved as it is, so when you next opens it it is there, but how can i know that that is the last row of data i edited.
It is my general understanding that the change event system within Excel vba is fairly particular as to what will fit the mold of a qualifying change event.
For example, changes that the user imparts to the worksheet and other written code are legitament candidates for change events. However copying down data and cells changing their values indirectly rather than directly may not be considered in the Microsoft change event design.
I would like to know 2 things:
1) Does a cell updated by a market data feed mechanism qualify for a change event?
2) Any list of qualifying change event types. It seems that Microsoft does not have this information.
I have a Schedule Grid that I've created. When a cell gets updated, I'd like for another cell to be changed (as to trigger some conditional formats that I have going on). I figured it'd be rather easy, and it was... sort of.
This is the code that I am using:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 4 Or Target.Column > 10 Then Exit Sub
If Target.Row < 4 Or Target.Row > 422 Then Exit Sub
Target.Offset(0, 9).Value = 1
End Sub
The code worked perfectly, up until a random moment. Not sure why, but now, the code only works when the cell is updated by a manual entry or deletion. If I use a validation list (which is the primary form of entry), it runs every line of code EXCEPT the Target.Offset(0,9).Value=1.
Here's the strange part. I took it home, and it works fine there. It used to work fine on my computer at work, now it doesn't. Nor does it work on the computer that I need to present this on tomorrow.
I've tried entering in a MsgBox() event both before and after the Target.Offset line. The first message box appears, so I know the code is passing through the restriction lines. But the second message box is not appearing. The code for whatever reason exits the sub at the Target.Offset.
I have a spreadsheet on sheet 1 with a list of customers and their information. So on column A I have the customer number (i.e. k968, e37, p528,...), on column B i have the customer's name, on column C the street's name, on Column D the house number, on column E the zip code and finally the city on column F.
Right now there are around 600 customers in this list.
I have made a userform with a combobox in which I want to select an existing customer (pulled from the spreadsheet). On the same userform I have textboxes (customer number, name, street, number, zip, city). When I select a customer in the combobox, I want this customer's info to show up in the textboxes. I want to be able to change the info and hit Next to store the changes in the spreadsheet. When I do not select a customer from the combobox, I want to add new info in the textboxes and hit Next to store this info as a new customer. The userform also has a delete button. Then I select a customer in the combobox, this customer (and it's info) should be deleted from the spreadsheet when i hit Delete. So the spreadsheet is variable in length.
I have a column of data held in column B.
I am required to show the following.
If there is data in the cell then it is to be left. If there is no data in the cell then I would like to show the value 0.
I have tried using a circular reference, using the formula =IF(ISBLANK(B1),0,B1) and other similar formulas but they dont work as the formula overwrites the data in it.
do I need a macro?, or conditional formatting?
I want a macro in one worksheet to run when any cell (in a given range)on a different worksheet (dataentry) is updated. I have spent along time trying to make it work with no avail. The code I use to start my macro is as follows.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target(1, 1).Address = "dataentry!H5:IV72" Then
If Not Intersect(Target(1, 1), Range("dataentry!H5:IV72")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
I have created a spreadsheet that has information about networks, subnets etc..
The top header for both companies in (CSV format) reads:
Network Namenet_addr1net_addr2net_addr3mask_lengthorg_idcidrlast_upd_dt
I basically split the spreadsheet to show both side by side.
I have this information for both networks that I am trying to compare what subnets, names, IP address etc... over-lap.
We are merging two companies into one, and I need to identify or highlight what information on both companies over-lap.
how do i show a userform in Excel in the spreadsheet environment? I used to "run macro" but unable to run userform code "private sub".
View 2 Replies View RelatedI have 2 different formulas that I need changed in a similar way.
The first formula is for cell AV11:
=SUM(BI11,BP11,BW11,CD11,CK11,CR11,CY11,DF11,DM11,DT11,EA11)+10
Every cell starts off blank.
What I need is for cell AV11 to always start off blank until data is entered into one of the other cells. The problem is that since the sum always needs to be +10 only when data is entered in the other cells, I don't know how to keep 10 from showing in cell AV11 when no data is typed in the other cells.
The other formula is for cell CO39:
=(CU8)+3
I pretty much need the same thing. If no data is entered in cell CU8, then I do not want cell CO39 to show the 3.
I have 2 dictionaries; both have a "location" and the "frequency of occurence". The 2nd dictionary locations are related to the first, but are named differently, so I have a range of cells I'm using to define the equivalent locations, ie:
Dict. 2 Location----Dict. 1 Location
Wrapper------------Packaging
Robot----------------Robot Arm
Robot----------------Robot Base
etc..
(It's not a direct 1 for 1)
The program is meant to take both dictionaries, convert the 2nd dictionary keys to the equivalent dictionary 1 keys (based on the cell range data provided) and then combine the values associated with that location and store them in D_Master. D_Master is a copy of my dictionary 1, in which I am also trying to add the values from dictionary 2.
I started by writing the location definitions within the program, i.e.:
[Code]....
which works, but there are several hundred definitions and it becomes less robust, whereas a user could type in a definition within the range of cells and the code below could take care of the rest.
I have a front page with buttons linking to other spreadsheets, e.g
{ button } Stock Sheet .xls (???)
{ button } Sales Sheet .xls (???)
In the brackets I wanted to display the last updated date of the said spreadsheets, so that a user can see if it has already been updated. Is there a way of retrieving the last updated date for a spreadsheet, and dispaying it (i.e is it held against a variable or named item)