Retain Formula Result History
Mar 5, 2008
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?
View 3 Replies
ADVERTISEMENT
Feb 4, 2009
Is it possible to retain the formula in particular cells within an excel spreadsheet whilst clearing the rest of the sheet, and if so how?
View 3 Replies
View Related
Oct 30, 2009
I need a solution so that each day I can get a value from a single cell but it appears in a new column each day.
eg, A1 contains a numerical value (based on a formula)
Each day there is a new column, say, Monday = B1, Tuesday = C1 etc.
On Monday B1 takes the value of A1 and 'freezes' the value in B1
On Tuesday C1 takes the value of A1 and 'freezes' the value in C1, but the value in B1 remains as it was when it was originally frozen.
View 9 Replies
View Related
Jun 10, 2014
When I drag my VLOOKUP formula down a column in Excel 2010, the return value copies the formula result from the original VLOOKUP formula result. For example, if the first VLOOKUP returns a value of 0.5, I expect to see 0.5 or 1 in the cell below that one. However, I get 0.5 which is not the expected result for the cell below.
When, I click the fx on the cells below, the expected return values appear in the formula result. After I click OK, the expected formula results updates and now appears in the cell.
I'm not sure what is causing this issue. My computer was updated recently from an old machine to a new one. I have never experienced this issue before.
View 3 Replies
View Related
Apr 24, 2008
I have on a sheet, on say cell "J10" a formula "=A1"
Now suppose I to delete row 1, Then J9, which was formally J10, will now say "=#REF!" instead of "=A1"
Is there a way to maintain J9 (which was J10) to say "=A1" without giving a reference error?
View 9 Replies
View Related
Jun 11, 2008
I have the below concatenate formula and it works for what I need..well, almost. I know I don't have the format cells option when using concatenate, but I need the answers to the formula to come out as a percentage or to two decimal places. I have searched and honestly don't understand how to do this in my formula....
View 6 Replies
View Related
Feb 13, 2008
I have a template file which has a reference to a cell on a sheet in another workbook.
I need to copy this template to 250 workbooks. However, everytime I copy this template sheet into a workbook, it updates the reference to the template name!
Is there an absolute reference I can use for the sheetname?
Example: Workbook Template - Sheet1 - Cell A1 = Value
Workbook Template - Sheet2 - Cell A1 = Sheet1!$A$1
So for so good, but when I copy Sheet2 to a new workbook, I need to have the exact same reference; ie. Sheet1$A$1 and NOT = ['C:Workbook template']Sheet1!$a$1
View 3 Replies
View Related
Sep 3, 2006
I created a simple auto numbering function whereby Cell A7 contained =Row()-6, and Cell A8 contained =(A7+1). I then shift, and pasted the contents of cell A8 until cell A600. My aim is to simply copy the increments of 1 - 600 into another column. However when i copy and paste i'm also copying the initial underlying formula ie: =( A?+1), Is there a way to copy the results, not the formula?
View 2 Replies
View Related
Sep 28, 2013
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.
View 1 Replies
View Related
Feb 5, 2014
I have an actions tracker worksheet.
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
View 2 Replies
View Related
Apr 1, 2009
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.
View 9 Replies
View Related
Sep 4, 2013
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.
View 5 Replies
View Related
Aug 18, 2009
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'.
View 9 Replies
View Related
Mar 29, 2007
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....
View 6 Replies
View Related
Mar 30, 2007
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.
View 9 Replies
View Related
Aug 24, 2006
I know this is an easy one but I'm unsure why I can't easily modify a formula. The key event in this formula is "+190". Every time I change the value the formula no longer works. I've copied to another cell and the formula no longer works. My question is when you have a formula like this one if you have to modify how do you do it and keep the formula working.
View 9 Replies
View Related
Apr 20, 2013
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.
View 1 Replies
View Related
Jun 4, 2014
how to delete the track changes history whilst the excel file is shared?
View 3 Replies
View Related
Mar 29, 2013
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)
promo sort example.xls
View 1 Replies
View Related
May 28, 2014
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.
View 5 Replies
View Related
Mar 5, 2014
I'm building a report and need to transfer data to a History Tab each time the macro is run.
"Report" Tab contains data to be transferred: C6:F6 to the "Historical" tab B:E in the appropriate date row.
The Date would be found from the Oldest date (furthest into the past) on the "Calculations" sheet, Column U.
View 3 Replies
View Related
May 25, 2006
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.
View 4 Replies
View Related
May 4, 2014
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?
View 8 Replies
View Related
Oct 21, 2007
#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.
View 5 Replies
View Related
Aug 5, 2013
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.
View 3 Replies
View Related
Jun 11, 2014
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
View 2 Replies
View Related
Jul 1, 2013
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
View 3 Replies
View Related
May 2, 2012
I have a s/s which is built on IF functions and references other sheets and I have used the s/s in the past without issue. However now I want to ammend the formula a little and excel will only display the formula itself, not the result of the formula. I have tried CTRL ~ to turn on/off the show formula function but this makes no difference.
My reason for changeing the current formula is that I need to turn a 2 (numeric) to 02 (which can be either numeric or text). This is the new formula.
=IF($A4="","",IF(VLOOKUP($A4,HCGTH1_DATA,22)=2,"02",VLOOKUP($a4,HCGTH1_DATA,22)))
Is there an issue with the formula or some setting in excel?
View 6 Replies
View Related
Jun 22, 2007
I have a workbook consisting of 4 worksheets. Cell B5 in each of three of these sheets has the exact same formula (copy/pasted) referring to data in the other worksheet. In one of the three similar sheets cell B5 shows up as a blank. In the other two, this cell shows up as a zero.
Since the formula in each of these cells is identical, I figured the difference in the way the result showed up was a matter of cell formatting.
The cell format for each of the three is indicated as ‘general’. However, with one of these, the ‘sample’ shown when you view the cell format shows as a blank while the other two show the ‘sample’ as a zero (0). How can I go about making all three cells appear the same, namely a blank?
View 9 Replies
View Related
Aug 1, 2009
I have already an existing formula to get only the amount from the previous cell. From a6 to a30 it contains the word "11/05/09 press release $100", so in b6-b30 this is the formula. =IF(ISNUMBER(SEARCH("press",A6)),RIGHT(A6,LEN(A6)-FIND("$",A6)),"") until b30
the problem is in b6:b30 the 100's were not a number you have to copy and paste to other cells using paste special then values then click the smart tag and convert as a number to get all the total from b6 until b30.
View 4 Replies
View Related
