VBA Codes (track Changes) On One Cell

Feb 21, 2009

I created a financial forecast model from scratch. The model is self-contained and has no external links.

Backgrounder

Before firing a question at you, let me explain briefly how the model works. I have an assumptions tab, several costs tab from various departments that ultimately feed the Results tab (target price on a monthly basis) . Let's assume that my target price is on line 200. To me more specific, for Jan09, the target price is on B200, Feb09 on C200 and so on.

Management will feed the model themselves, given that they are assigned specific areas that they can input their forecast.

Issue or question to you

I would like to track historical changes to the price on line 200 on my target page. That is for every individual change and their impact on price.

To illustrate this let assume the following:

Let say User A made several changes on his forecast, I would like the macro to automatically record the individual change that has been made and the impact it had on the target price on line 200.

Assume that User A made two changes on his forecasting sheet. He increased the cost of supplies by $50,000 (on one cell) and increased the cost of labour by $10,000 (on another cell).

What I would love to have is a macro that would record the changes that User A changed; (i.e. cell, tab, user) after he entered the $50,000 and after he entered the $10,000. This would be recorded on a separate sheet.

Thus, immediately he entered the $50,000 on the cell and press it would record the change and the impact it had on price on the target page. Then when he entered the $10,000, it would do the same.

The format of the "changes sheet" that would contain the changes would look something like this:

USER..Date..Cell changed...Tab..From value..To value. Price Before Price after Impact (Price before less price after)

I am aware that there's a lot of details but if you don't understand the issue, it's very difficult for you to resolve.

I assume that it requires a VBA code of some sort to set this up. Given that I am not familiar (a true dummy) with VBA, I would need like a step by step instruction.

View 9 Replies


ADVERTISEMENT

If Statement Limit Workarounds: Convert Various Codes From One Column Of Spreadsheet Into Different Codes In Another Column

May 5, 2006

I'm trying to convert various codes from one column of an excel spreadsheet into different codes in another column. I was able to accomplish this with "If" statements, however I'm only able to string together seven of these statements in one command. Is there a better way to add formulas for more than seven conversions? Below is a copy of what I've done so far with the seven converts:

=IF(ISNUMBER(SEARCH("WARN",J2)),"Warning",IF(ISNUMBER(SEARCH("PSSNAP",N2)),"Sales",IF(ISNUMBER(SEARCH("WARN",L2)),"Warning",IF(ISNUMBER(SEARCH("2699",L2)),"Warning",IF(ISNUMBER(SEARCH("4004",L2)),"Warning",IF(ISNUMBER(SEARCH("2036",L2)),"Warning",""))))))

I want to add about 15 more codes to convert within this formula but it's maxed out in the format I'm using.

View 3 Replies View Related

How To Track Which Tab Uses Current Cell

Mar 14, 2014

I tried to use Formulas -> Trace Dependents to find which cells is linked to the current cell.

Excel only shows it is linked to the other tab, but don't tell me which tab exactly. How to find the tab which is linked to current cell?

View 3 Replies View Related

Track Changes Detail In A Cell

Jul 29, 2006

Is there any way to capture the comment that comes when we use "Track Changes", the date & time when the cell content changes should be retrieved in the adjacent automatically. Is there any way to do it using formulas/ VB code.

View 7 Replies View Related

Cell Codes For Less Typing

Aug 11, 2007

Is there a way I can set up the worksheet so that in one cell I can type out for example product codes/numbers and by doing the description for the product will come up in another cell. Rather then copy and pasting very the many different product descriptions?

View 2 Replies View Related

Excel 2003 :: Reverse Track If Value In A Cell Is Used By Formula Elsewhere

Sep 25, 2012

I am using Excel 2003. Looking for a way to find out whether some data I want to delete in a sheet is used by a formula somewhere else within the file. Is there a way to check that? In other words, I have the number 500 in A1, and I want to delete it, but I don't know whether it will impact a calculation on another sheet that involves that value in A1.

View 4 Replies View Related

Cell Colour Pallete Codes For Commands

Oct 18, 2008

where I can find the colour pallette codes for commands like, as my suck it see method of guessing the right colour is pretty time cosuming.
selection.Interior.ColorIndex = 34

and secondly, can rgb be used to fill cells colour i.e. something like
range.colour or background whatever the command is. rgb(255,255,255)

View 9 Replies View Related

Post Codes To Autofill Suburb Cell And Matching More

Apr 7, 2008

I am working on a large volume spreadsheet comprising of the usual mailing list fields such as first name, surname, company, address1, address 2 , suburb, state, postcode etc etc

Is there a way for Excel to auto fill a line of details (or display a list the matching entries)?

this way, i can start to enter a semi-unique field such as surname, company or address and a list of matching entries pop up for me to select the best fit.

I then would fill in the remaining data.

View 9 Replies View Related

Data Validation To Automate Budget Codes In Cell Range?

May 6, 2014

Data Validation setup: "Allow" is: List "Source" is: =$E$12:$E$15

D___E____F
12__CA__CASH
13__OT__ONLINE TRANSACTION
14__DD__DIRECT DEPOSITE
15__AW__AUTOMATIC WITHDRAWAL

The codes in E column are what is entered in a worksheet range (user cannot see the list). Needed is to show both the two-character code in column E and the text definitions in column F in the dropdown. As data validation now works, users only see the two-character code and are not remembering what the code stands for - they need to see the definition.

Is there a way to show both the two-character code from column E along with the text in column F? When user selects, only the code would be entered in the target cell. (Is data validation the correct technique?)

I have set this problem up in closely located cells to construct the issue. In reality, the validation list is well off-screen from where the user is actually inserting the codes and there are many more codes/definitions.

View 3 Replies View Related

Forumla To Give A Starting Number And Take One Away Each Time A Code Or Codes Are Dislayed In A Cell Range

Mar 22, 2009

In 1 cell i need a forumla to give me a starting number and take one away each time a code or codes are dislayed in a cell range. Something like this

A1 = 23 days or any number days i needed

Cell range B1:C52

every time a selected 1 or 2 letter code appears in the cell range i want A1 to subtract 1 day from the starting number, i would need it to subtract half a day if one code appears ie HD, the codes may be P, OT, HD

View 4 Replies View Related

Track Changes

Oct 21, 2009

So I got this code from http://www.ozgrid.com/VBA/track-changes.htm i followed the directions and pasted it in the workbook module but does not seem to be working. I am thinkning the copy and paste feature is messing something up anyone know what?

I have added a worksheet named Log

View 5 Replies View Related

Track Changes On Another Worksheet

Aug 6, 2007

I have a list of items that I keep track of when they were last used.

For example:
Item# Last Used
Item#1 8/27/06
Item#2 5/2/07
Item#3 6/30/07

What I would like to do is when I enter a date, it automatically tracks the changes so that I can not only see the most current date but all previous dates if I need to.

The ideal would be to transfer it to another worksheet so that is looks like the original except that is shows multiple dates after it instead of just the most recent.

For example:
Item#1 5/3/05 7/23/06 8/27/06

View 9 Replies View Related

Track Users

Dec 15, 2008

We have an excel file that I've developed that people in our department are supposed to be using - not that they want to - but it is an edict from our bosses. Is there a way to track who is actually using that file so I can verify that they are using it rather than just relying on them saying that they are?

View 9 Replies View Related

Track Changes & Macro's

Apr 23, 2009

I have a work sheet that has 113 tabs and I use two macros (both written with help from this forum - thanks), one to protect all of the worksheets and the other one breaks the workbook into 113 separate files. I want to use the tracking feature because I will be sending out these budget templates and want to identify the changes when I receive them back.

Problem is once I enable the tracking I can no longer run my macro’s; I get a VB dialog box with a red X and the number 400. Anyone know how to work around this problem?

View 9 Replies View Related

Track Changes From All Worksheets

Aug 7, 2007

I have found another user's code that will track changes for a single sheet, if that code is in the sheet. This code uses the Worksheet_Change function. However, I have just stumbled acroos the Workbook_Change function. The workbook function is exactly what I need, since I have multiple tabs that need to be tracked. However, the code does not seem to work. I can switch between the two functions, using the same code afterwards, but with the new workbooks phrase it does not work. I tried placing the Workbooks_Change in the "ThisWorkbook" object in VBA, but it does not seem to work there or anywhere else.

I have attached the file. The code is in the "thisworkbook" function.

For quick reference:

Private Sub Workbook_Change(ByVal Target As Range)
Dim r As Long, OutSht As Worksheet

Set OutSht = Sheets("Log")
r = OutSht.Cells(Rows.Count, 1).End(xlUp).Row + 1
OutSht.Cells(r, 1) = Target.Address
OutSht.Cells(r, 2) = Now
OutSht.Cells(r, 3) = Environ("UserName")
OutSht.Cells(r, 4) = Target.Value
OutSht.Columns("A:D").Columns.AutoFit
End Sub

View 7 Replies View Related

Using IF AND To Track Quantity Totals

Jun 10, 2014

I am currently trying to find a better way to track quantities of delivered material from multiple suppliers. I have a attached a sample of what I am trying to do. In the "totals" sheet Row 3 is working as planned. There are a couple of issues that I am having with my formatting as is. First off in my "raw" data sheet there is an empty row between each row of numbers (this is they way I am sent the information from the supplier). This empty row causes my totals to place a Q everywhere column A on "Raw" is not filled with "B". Is there a way to have the totals sheet only pull from the cells with values in them?

My other question is how I would be able to get the values for material, Net wt., and Charges to populate if Column A in "Raw" is Q?

Quantity Tracking.xlsx

View 5 Replies View Related

Track When Link Is Clicked?

Jun 17, 2014

I was wondering if there is a function to be able to track when the last time a hyperlink was clicked and display the date in a new cell?

View 1 Replies View Related

Keeping Track Of Payments

Apr 24, 2009

i am a builder and i want to make a spread sheet to keep track of what i have been paid and the labour costs i have paid out

so i am working on 20 houses that all have 7 stages of payment each, and 5 to 8 men working on them who receive a price for each stage, but are paid a portion of that price each week while they are working on that stage

what i am trying to do is set up a sheet to keep track of what has been paid and the balance remaining for each stage and what has been received

i have tried doing it a few different ways but im none to clever with spread sheets and what is causing me the problem is next to each payment made i need to put the date and the name of the person who was paid so it always seems to just look a cluttered mess

have any of you got any ideas of how i could simplify this and make it look neat and easy

or alternatively is there some other software that may suit this application better?

View 10 Replies View Related

Track The Time From 1-NY To 10-Lukket

Jul 2, 2009


So if we take first line the defect id is 10. The date entered is 21-08-2007 and the status is 1-NY ( means new)
NExt line the defect ID is still 10 the date is changed to 22-08-2007 and the previous status is was 1-Ny and the new status is 03- under vurdering (to be
evalueted)
Third line the defect ID is still 10. The date is changed to 04-01-2008. The previous status was 03- Under vurdering ( to be evalueted). The new status is 10-lukket.

I want to be able to track the time from 1-NY to 10-Lukket.

Now the even more tricky part. As you can see other defects go over different statuses e.g defect number 1000 here.

The sheet I have is 13000 rows and I have a total of 2300 defects

1021-08-2007 10:491-Ny
1022-08-2007 15:381-Ny03- Under vurdering
1004-01-2008 13:4703- Under vurdering10-Lukket
10029-08-2007 10:051-Ny
10003-09-2007 14:121-Ny04- Afventer tilretning
10012-09-2007 15:2204- Afventer tilretning10-Lukket
100006-02-2008 11:2901-Ny
100012-02-2008 15:0501-Ny04- Afventer tilretning
100015-08-2008 08:5304- Afventer tilretning10-Lukket
100015-08-2008 22:1310-Lukket04- Afventer tilretning
100004-02-2009 14:0404- Afventer tilretning10-Lukket

View 11 Replies View Related

Program To Keep Track Of The Hours

Jan 7, 2010

i m trying to use Excel program to keep track of the hours he is working. I know there is a way to do it but it has been many years since I used Excel and can not remember how to do it. He would like to insert the time (hours & minutes) he works in column A and have a sum in the next column.

View 7 Replies View Related

Track Inventory In / Out Using Excel

Oct 7, 2011

If I wanted to use excel to track inventory in/out, is there any way I can do the following?

I have a barcode scanner and I wanted to be able to quickly add and subtract quantities.

So, I want to create three fields at the top of list, one with the function to Add 1 to quantity, one to Subtract 1, and one simply to find (well I guess I can use excels find feature, but that would be an extra step)

When I scan a barcode into the "Add 1 to quantity" field (which only appears at the top of the page), excel will automatically add 1 to the corresponding field next to the item that has that UPC number. The same for Subtracting 1.

View 14 Replies View Related

Worksheet To Track Inventory

Apr 12, 2008

I'm trying to make a worksheet to track inventory, not for sales but for tracking how many supplies are on hand in storage rooms, closets, etc. The worksheet will be used by as many as 20 different users probably on a network.

Because it will be used by many people, I'm trying to see if there is a way that the worksheet can be updated after every save. In particular, the amount that are "on hand".

For example, say there are 10 of a particular item on hand to start. Joe takes 2 and goes to record it on the worksheet. He inputs the 2 he takes, which now show that there are 8 on hand remaining. He saves the worksheet. Then, Jerry comes and takes 2 and goes to record it on the worksheet.

I can't figure out how, or if it's possible, to have the worksheet show Jerry that there are 8 on hand to start, not 10. So that when he takes his 2, it should show that there are now 6 on hand remaining. I've attached an example worksheet.

View 6 Replies View Related

CountIf - Track Last Item

Aug 31, 2009

How to track the value of last item in CountIf and work backwards? I am trying to do inventory valuation at the end of the month. For example I have 7 purchases in a month for 1000 units in each purchase order, my on hand inventory is 1500 units. from the purchase details I did a countif to tell me there are 7 PO's in a month for the item, but if i want to find how much each units costed starting from last PO in the CountIf is there a way to pull that number? I have total purchase amount by PO & quantity by PO also in separate columns, so I know cost per unit in each PO but can't figure out how to go backwards (starting from last in)

View 14 Replies View Related

How To Track Installation Of Equipment

Feb 7, 2012

I am trying to track installation of equipment in excel, I know what I want done, but not how to do it. I have 8 columns, and I want a yes in each column to equal 12.5% and a column to show progress at the end, I also want to show total progress for installation of all equipment at the bottom of the page

View 4 Replies View Related

Track Changes Based On Login Name

Dec 21, 2006

I've been looking around for a way for Track Changes to reference the loggedin username of the workstation rather than the name in Excel registration.

I found this
Function UserNameWindows() As String
UserNameWindows = Environ("USERNAME")
End Function

: on this board and another blog.

But I don't know how to enter it to make this work. I had read you make it a new module and assign a formula, but the most I could get through was opening the VBA editor for the workbook, creating a new module, and pasting the code. Changes would still reference the Excel registration name, not the Windows' logged in username.

View 9 Replies View Related

Track Changing Date

Jun 11, 2007

How can I track date of every last change made in certain cells so my boss can monitor if I am keep working on a project?

View 9 Replies View Related

VBA Can Keep Track Of Total Row Height?

Jul 25, 2007

I was wondering if VBA or Excel has the capability to add and total Row Height or Pixels? For example I have my Rows set to a height of 14.25 (19 pixels). My Range that I am working with is A2:T41 for a TOTAL ACCUMULATED ROW HEIGHT of 570 (40 Rows X 14.25) and 760 Pixels (40 X 19).

What I would like to accomplish is that once the TOTAL ACCUMULATED ROW HEIGHT of my Range reaches 570 then Row 1 appears. If the TOTAL is less then 570 then Row 1 remains Hidden.

Does anyone know IF and HOW this can be done? I need this due to the fact that the Rows will Autosize and I need to have my header Row appear. I can't just Freeze Row 1 because my actual Headers are on Row 16.

Hope this makes sense and hope that someone has a solution.

Bye 4 Now,
Mar

View 9 Replies View Related

Track Comments On The Spreedsheet

Oct 1, 2007

I have created a spreadsheet for my students on a shared drive. They will be able to go there and make comments..My question is this: I know they can have their name set in Excel...How long will the name stay in it. I have to change the speedsheet daily. Does that mean the students will have to enter their name daily. Also how can I track each student comments on the spreedsheet?

View 9 Replies View Related

Put Together A Basic Spreadsheet To Track

Sep 15, 2008

I'm trying to put together a basic spreadsheet to track offensive plays for high school football. I'd like a formula that will change the LOS (Line of Scrimmage) to reflect the yards gained on the previous play. The problem is that you'd probably need negative numbers up to 49, and then back down to 0.

View 10 Replies View Related

Race Track Chart

Jul 12, 2006

I am running a contest for my office and the theme is 'nascar'. Anyway - we'd like to chart the contestants using a racetrack. Is this possible in Excel or any other MS Office tool?

Basically I'd like to show the placement of ten contestants on a circular racetrack. It would be updated weekly.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved