I want to type status update for an action - then when I press enter it should automatically as soon as I press enter:
- cut the text I typed from the cell leaving it blank
- move to the target cell (its in the same row) and "add" that text string (with a date stamp added) to the text already in the target cell.
Every day I type an update against the action in the first cell - and I press enter - it should copy that text into the target cell.
That way I have a complete history of updates against the action with date stamps.
Sounds pretty simple but I don't know where to start.
Example of a simple worksheet attached : Tracker.xls
I am using excel as point of sale book (to record customer name, service, and total price etc.) at our hair salon. We have employees that may be there to manage alone from time to time, and enter clients into excel. Our worry is straight forward, and involves them erasing what they wrote. I am confident that the actual service and price is entered at the beginning, but want to track their changes to their own entries.
The "track changes" would work if it "tracked changes" after entry. But it seems to track the last change from saving. For example....the employee enters $40.25 presses enter--after she knows she can get away with a change, she may erase it altogether or change it to say $16.75.
I am working on an Excel file which was saved and then whilst working on it my Excel programme locked up and I lost the data. I thought it would recover it but it didn't. So I lost my data and had to redo it all from the original saved file.
However, my problem is this. I have updated this file and saved it countless times but it never appears in my previous file history when I scroll down the file tab (Excel 2000) although it is stored on the file directory.
I have created a UserForm with 10 text boxes. 9 are input fields and the 10th is a large comments box. I have designed the form so that entry's can be added, searched for and updated. However, I would like all previous data to be stored as history in the 10th text box.
Our main spreadsheet in the office is a shared filed which is accessed by 3 pc's. We have been using this set-up for nearly 3 years and no problems until this last week. Now, over the past week, this message pops up when we try to save the file on the PC's not hosting the file.
"Excel was unable to save the workbook because there was a problem reading or applying part of the revision history. You can unshare the work book or save to a non shared workbook format to fix the problem"
I have shared/unshared/reshared and shared again but a week later, I still keep getting this message popping up. Any ideas?
One thing I have noticed is that we have set it in the advanced tab of the share workbook options not to keep revision history, but sometimes it also shows 'keep history for 30 days'.
I want to maintain a history of exam results held on different dates
For this purpose I created a file with three sheets named Inputs, Working & result history. Description of all sheet is as follows :
“Inputs” Used as main Input file to feed marks for each subject on certain exam date. “Working” Linked with sheet inputs and perform calculations ( total marks, % etc) “Result History” Date wise history of exam results based on sheet (working).
Required Solution Steps:
1.Score entered for different subjects in Sheet “Inputs” for a particular exam date. 2.Obtained marks are totaled and score % calculated based on scores entered in sheet inputs. 3.Date wise history of exam results recorded based on working sheet.
To transfer/copy/paste results from “Working” sheet to “Result history” sheet I recorded a macro which is linked with a button. This macro copy record from working sheet and paste it as value to result history sheet....
I want to maintain a history of exam results held on different dates
For this purpose I created a file with three sheets named Inputs, Working & result history. Description of all sheet is as follows :
“Inputs” Used as main Input file to feed marks for each subject on certain exam date. “Working” Linked with sheet inputs and perform calculations ( total marks, % etc) “Result History” Date wise history of exam results based on sheet (working).
1. Score entered for different subjects in Sheet “Inputs” for a particular exam date. 2. Obtained marks are totaled and score % calculated based on scores entered in sheet inputs. 3. Date wise history of exam results recorded based on working sheet.
To transfer/copy/paste results from “Working” sheet to “Result history” sheet I recorded a macro which is linked with a button. This macro copy record from working sheet and paste it as value to result history sheet.
Now I need modification in this macro/or any other solution so that: when I entered scores for next examination date in “inputs” sheet all calculations done as mentioned above in step 2 (for earlier exam date) & when I click the button again it post the result details to “result history” sheet in the row next to first record. In this way a history of results for different examination dates can be maintained.
I was wondering is there a way to use vlookup and then when the particular piece of data is found (using vlookup) to paste it as a value instead of the formula? I have a file that tracks the market value of certain portfolios and i have a source file that i update every month. I dont want the numbers to be overwritten each month, i want to keep a history of the market values each month. Do i need to program something in VBA or can i work something up in the formula bar?
I have a table that includes list of clients, products they order, and requested delivery date. When the client calls in to place an order, I pull up the excel table (orders worksheet) and fill in the items quantities and the required delivery date for the client who called. Once I fill the information for the client who called in, I need to update a "log" worksheet with the information that I just entered. The log worksheet will eventually contains history of all the orders which I received in the past.
I need to find a way of populating a column of forecasts based upon previous sales amount and price. For instance if I have apples on special for $2 and previously sold 200 units on multiple occasions at this price but once off sold 1000 apples at special $1, but normally they are $3 selling on average 50. I would want to get a result of Forecast: 200, not 50 or anything else to far off
I've attached the sheet I currently use for work.
Dated tab: is my working sheet MerchTrend: Previous sales history, which is imported from POS system and unfortunately cells will change based upon sales
On the Dated Tab, price column includes multi buy prices (ie 2 for $3) but the Merch Trend refers to these as individual sales (ie 2 sales for $1.50) On the Merch trend, Price Type refers to promo style. (N for Normal Price, IA, S, R, IR, P are promotional)
I am looking to record the history of multiple cells at one time instead of as soon as I hit enter after changing one cell's value. There are other threads concerning just the history of a single cell as you input another value and hit enter.
So for example, I have multiple cells that are calculated using formula's in their respective cells using input cells in the worksheet. I will be changing the input cell's values in the worksheet to update the calculated cells. I want to record the input cell's values as well as the new calculated values at one time after changing the input cells. I dont know if I can add a button to a cell or something to that accord to accomodate this. I am still new to vba coding in excel.
From there forward I will interate this as new situations arise and want to record the input and output (calculated) cells on another tab or worksheet along with the date it was recorded.
I encounter an problem when I migrate the excel application from excel 97 to excel 2000. The application basically composed of VB6 and VBA. When the program execute to line "ChangeHistoryDuration", i.e.
'Turn On the Audit ExlApp. ActiveWorkbook.KeepChangeHistory = True ExlApp.ActiveWorkbook.ChangeHistoryDuration = gi_duration ExlApp.ActiveWorkbook. SaveAs w_filename, , gs_open_pwd, gs_write_pwd, , , xlShared
Run-time Error '1004', Method 'ChangeHistoryDuration' of object '_Workbook' faild was found. However, it runs successfully in excel 97. Does anyone know the possible problem that cause such error.
I have a VBA application that uses the IE Application object many many times through a loop, and then repeats. So basically it is running continuously, and opening invisible IE window every few seconds.
I have code in there that 'kills' all IE instances between each iteration so that the number of windows doesn't pile up, but after awhile the temporary internet files, cookies, and history build up too.
Is there any VBA code that will clear those three things out?
#17. Create a macro named "AddSale" that perform the following tasks: -Switches to the Sales History worksheet, and then inserts a blank record in the third row of the Sales History worksheet, shift the rest of the records down.
i did create the Addsale to the macro i use Tools-Macro-record a new macro (is that right?) and i switch to the sales history worksheet insert a blank row. (am i right?) it state inserts a black record, i can't find record anywhere so i assume its blank row.
I have to import data from an external source(oracle database) to an Excel(2013) table.
Now the data in the staging table in the database keeps refreshing/changing, However in Excel i need the data to come into a new row everytime instead of refreshing the whole table and looking like the staging table in the database. So basically i need to build history in Excel.
I managed to implement a macro that adds a time/date stamp in Column A when any of the subsequent cells in that row are changed. With this now working, I really need the ability to restore the undo history in case a change to the data needs to be reverted.
Sub procedure:
Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range Application.EnableEvents = False For Each c In Target If c.Column > 1 And c.Column < 21 Then Cells(c.Row, 1) = Now End If Next c Application.EnableEvents = True End Sub
I am creating a spreadsheet to track customers contact information and purchase history.
A B C D 1 First Name Last Name Phone Number Etc. 2 Mike Jones 3
I wanted to know if theres a way to click on a customer (say Mike Jones) and be able to enter and view purchase history information relating to that customer in either a drop down box or different sheet?
ex. Mike Jones Date Item Spent Last Contacted 4/24 Toy $500 4/31 5/16 Movie $15 5/20
I have a series of cells that can be auto filled based on the initial response in cell B8. For example, if cell B8=ABC, then cell B22 should be auto filled with 123, and cell B24 should be auto filled with Yes and cell B56 should be auto filled with the word Hosted.
I have a list of staff on a worksheet, indexed and shown in a drop down list. I also have another worksheet with the same list of staff listed against various tasks. For example:
Sheet 1!Column A:
John Jack Jane Jo
Sheet2!Column A:
John John Jack Jack Jane Jane Jo Jo
Sheet2! Column B:
Throw Catch Throw Catch Throw Catch Throw Catch
What I need to happen is that when I update Sheet1!Column A with new members of staff, the array in Sheet2!Columns A&B automatically updates
New to the board: I searched the forum looking for a way for a spread sheet to do an auto update without hitting the F9 key. I have a sheet with external data which updates every 2 seconds that we moniter and I was tried of having to update the sheet every minute or so. I found this code within the forum.
Sub startme() Application.OnTime Now + TimeValue("00:01:00"), "this_program" 'calculate every 5 seconds change to suit End Sub Sub this_program() Application.SendKeys "{F9}" startme End Sub
Then code works great...but when this sheet is open and in autoupdate mode all sheets that are open also start to update. I tried several things from the help section of VB, like adding a +sign to try to make the old shift/F9 update which only updates the sheet you are on. Nothing I do seems to stop all the sheets from updating.
I think there is probably a simple fix to this and I hope someone might lead me in the right direction.
I have two workbooks with links between them. When I mail them to a colleague, he saves the work books in a folder, but when he opens them, there is popup message to update the links, and the data on the sheets still refer to their previous location on my pc. How can I change them to the new location without having to setup all the links from scratch again?
I have a macro that opens many excel documents. I used the application.screenupdating to turn off viewing these excel documents opening unfortunately they are still viewable. I counted the number of trues and falses in the module and there are 4. I believe the 2 pairs are unrelated. I keep both subs in the same module since they share functions.
I have 4 collumns: Location//S-1//CSM//SCO I want to enter in the location in a drop down list, which I know how to do. The thing I'm trying to do is when the Location says To S-1 for example, the date that it is changed "To S-1" the collumn under S-1 will post the date. When location changes to CSM, it will put that day in that collumn, etc.
My issue is that if I use the NOW() or TODAY() function in an IF() then it will update evertime I reopen the file. So the dates won't stay fixed. So if I change the Location to S-1 on the 12th, open it again on the 13th... it will say the 13th not the 12th.
I have a macro that I use to keep other worksheets updated with current codes and whatnot. The macro opens each workbook in the directory and updates the worksheets. It works fine except that it's only updating 1 sheet within the workbook (whatever sheet is active). I need help actually cycling through ALL the sheets in the workbook. There are 7 worksheets in each workbook. The code I need help with is in red.
'===================================================== 'CODE BELOW IS USED TO UPDATE INSTRUCTIONS SHEETS. '===================================================== Dim FromBook As String Dim ToBook As String Dim ToSheet As Worksheet Dim SPDir As String
'-Code to loop through files in directory. 'On Error Resume Next ToBook = Dir(SPDir & "*.xls") While ToBook "" If ToBook FromBook Then Application.StatusBar = ToBook Update_Data ' subroutine below End If ToBook = Dir Wend '-- close Range("A1").Select MsgBox ("Sheets Updated.") Application.StatusBar = False Application.Calculation = xlCalculationAutomatic
End Sub '===================================================== 'SUBROUTINE TO LOOP THROUGH FILES AND COPY CODES. '===================================================== Private Sub Update_Data() Workbooks.Open (SPDir & ToBook) For Each ToSheet In Workbooks(ToBook).Worksheets ActiveSheet.Unprotect "Password" Update_Column_Fields 'Subroutine to copy codes (not shown here) ActiveSheet.Protect "Password" Next Workbooks(ToBook).Close savechanges:=True End Sub
I have two worksheets, one that I update information on and the other which just basically points to that information. The second one is for viewing by a different department and has an 'Autofilter' applied so they only see certain information. This works fine as such until the data is changed in the first worksheet, at this point the 'Autofilter' doesn't automatically update, I have to take the filter off then put it back on again.
Is there anyway to ensure the autofilter updates automatically or is there an alternative way of doing this,
For a project, I need a way to automatically have updated stock values in a worksheet to work into various other formulas. I tried the HYPERLINK formula to the individual stock reports, but it wasn't specific enough to simply get the current value of the stock. Is there a way to narrow that formula down to get only that number, and still have it update every time I open the worksheet?
I am working on a resource model for my company. Essentially it is a list of contractors in the company with their daily rates and start and end dates. I have attached a sample spreadsheet. As you can see, all the information has already been populated with the cost of each contractor per month (formula used - daily rate*18.8 [days worked per month]). Now what I need to do is make the spreadsheet dynamic so if for some reason a contractor end date is changed (contract terminated earlier or contract has been extended) it needs to be reflected on the cost per month columns.
As an example, on the first row the contract period is from Dec 08 to June 09. If it is decided the contractor is now only needed until Feb 09, I need to have that automatically updated so all the figures are deleted in the March, April, May and June columns. Second example, on the first row if the contract period is from Dec 08 to June 09. If it is decided the contractor is now needed until July 09, I need to have that automatically updated with his monthly cost assuming the contractor has worked full time for that month (18.8 days).
