DDE Change Tracking
Aaron Blood has created an excellent DDE Change Tracking.
The file is in the download area of [url]
The macro, in the xls file, archive the data from top (a1) to the End of the worksheet.
I want invert the way to file/archive the data… copying all the cells already filled, a cell down and filing the top ( A1...) with the last DDE value (In the TracK sheets).
How can I do that?
The primary code is:
Sub Change_Tracker(Watch As String, TrackOn As String)
Dim NextCell As Range, r As Double, c As Double
r = .Rows.Count
Set NextCell = .Cells(r, 1).End(xlUp).Offset(1, 0)
View Complete Thread with Replies
Related Forum Messages:
Change Tracking Automatically
I have a spreadsheet i would like to have changes tracked to automatically. I know it's only a matter of clicking a few buttons to get this to happen, but i was wondering if there is a way to have excel do this by default when i open the file. I would also like to have the changes highlighted automatically.
VBA - DDE Link
I have created a DDE link which is connected to Excel and refreshes on every change but the problem is it's only 1 row in height, so as new data comes in I lose the previous. Instead I want the old data to move down a row and I want this to continue as new data comes in.
Accept DDE Input Only
if it was possible to limit data input to DDE (I am using winwedge to capture numeric data). Dave Hawley replied that all I had to do was lock the cells and protect the workbook. I have now done this but the data is not accepted unless the cells are not protected, anyone any ideas on what I am missing?
Allow Dde Data Entry Only
I have a programme that will be accepting numeric data through a DDE link (Winwedge). My question is; is it possible through VBA to ensure data can ONLY be entered into a specified range from the DDE link?
DDE Links Showing #N/A! After Refresh
I have a large spreadsheet with several links to external data via DDE. With help of kind Ozgrid volunteers, I've managed to update the data link each second. However, every time the data is updated, the linked data displays #N/A for a split second. This is enough to cause hundreds of cells and formulas to display #N/A for a split second. Since this occurs once every second, the screen has become almost unreadable.
I have tried
Application. ScreenUpdating = False
without any success. I also tried disabling Excel Error Checking via Tools > Options > Error Checking.
DDE Macros And Data Formatting
I'm working with a data logging system to collect data on a circuit. The logging software allows you to export the data collected to Excel in real time using DDE, and having talked to the logger company I managed to obtain a macro to get the data into excel (I'll put it at the end of the post). This works fine, and I can now press a button and Excel updates itself with the new data every second. What I want to do is quite complicated and I have no idea where to start:
1. The data obtained via the macro only displays in static cells, that is when the new data comes in it overwrites the old one. I want Excel to take a copy of each new bit of data as it comes in, and copy and paste it into a column on the same sheet so that it can be stored. I then want it to move down a row and take the next reading and so on, so that each time a new reading comes in, Excel takes the value and puts it in the next row down in a set column
2. Having a time elapsed measure would also be very useful, so that it records the time after starting the other macro in a column next to the ones collecting the data as described in 1. In this case, each new row would be 1 second further on. The actual time is not massively important, just the 'relative' time since the data collection started.
3. Once the data is copied into the columns to be stored (1.) I then want to calculate two sets of moving averages and place those in columns next to the data. My current plan is one average of 30 samples and another of 5.
4. Once I have the moving averages I can then get Excel to plot some graphs to show the progression of the two values
5. Since my project is revolving around detecting a short circuit in a coil of wire, it is looking for drops in resistance. I want Excel to be able to monitor the two calculated moving averages and if the difference between them exceeds a set threshold for more than 5 samples then some alarm is triggered (for now just a cell changing colour to red or a pop-up box or something).
Using Data From DDE Link In A Macro
I wonder if it is possible to bring data from DDE-links to variables in a macro without first bringing it to cells in Excel. The reason why I want to do this is because I only need the data when the macro is run (that is when a certain button is pushed). It would be a waste of system resources to continously link the data to cells in Excel. I asked the same question a few days ago at another forum but didn't get any reply.
Copying Data If Conditions Met (DDE)
I'm working with a DDE feed of stock quotes (much like this thread: http://www.mrexcel.com/forum/showthread.php?t=322427)
Ideally, I'd like a row of information to be cut & pasted as the values into another worksheet IF an objective condition is hit.
A very simple example: Let's assume I've got the following data:
I'd like to be able to copy the entire line to a new worksheet IF the bid volume is above 400, every time it updates with new data.
Unfortunately, my VBA knowledge ends very shortly after recording a macro & making small tweaks, so I have no idea how I'd set something like this up.
Dde Data Copied From Sheet1 To Sheet2
I am enlcosing a file that I partially acquired (the macro portion) and I am trying to add to that.
The workbook has a dde link in sheet 1, the macro will write the data to sheet2 at set times. I am creating a report of the data in sheet 2 in sheet3. at this point it is very rudimentary. I need some assistance to clean up the macro and the post of the dat in sheet2. I have created a detailed (probably over detailed) explanantion in sheet 1 in a text box.
Lookup Cell Fed Via A DDE Link
I am trying to build a stock watchlist in excel 2007 with a dynamic link to a DDE server (paid for).There is no add-in or plug-in, I just CTL ALT & drag each code from a watchlist in the program I am using and place it in a cell, however can only choose one data field at a time. There are 14 data fields and over 150 codes in my list which makes 2100 cells. (My guess is about 3 days work)
I would like to just enter the stock code in say column A (A3) and with each DDE data field (e.g. lastprice, open, cose, high etc) entered in subsequent columns have it lookup the stock code in cell A3 and return the correct value based on the code in column A, rather than entering each cell individually.
Is it possible to write a macro or vba code to create the cell formula so I can just fill down and save myself 3 days work?
The DDE server I am using is formatted like this:
I thought I might be able to do something like the following, but it doesn't seem to work.
Note: The DDE server can only be accessed while the other program is running and is password protected
Hide Or Remove Zero Values From DDE Links
I'm using a dde link to import stock data into a workbook. The service that I use didn't create time templates when they wrote the excel add in for intraday data.
Because of that I get zeros in data fields when the stock isn't being traded. For example, when I update in the morning the stock hasn't been traded in three hours, so when requesting hourly data I have three rows where the Open, High, Low, and Close columns are field with zeros.
Is there anything that I can add into the dde array that would have excel only import data with price value greater than 0?
I've been trying to weigh my options but my excel experience is limited.
Vba Code Recording Dde Linked Data
In A2 is the Stock price, and B2 the time of the update.Now every time it updates A2 and B2 get overwritten.I need some code that will save/ record the the stock price on the hour, every hour. I then need the highest and the lowest price within that hour, and lastly the price at the very end of that hour. (example)
I received what I think may work, but I dont know how to write this in VBA. If the time of the new value received > than B2 + 1 second, create new row where Open=High=Low=Close else if (last column=new value,if new value>high column then high column=new value,if new value<low coulmn then low column=new value.)
Compare DDE Linked Cell For Percentage Tolerance Of Last Value
I have a DDE linked cell that is constantly updating (cell A1), I want then to have a cell (cell B1) that changes only when cell A1 differs from value currently in B1 by a certain percentage, then check B1 for the same tolerance level against A1, and repeat. I can do this in a number of ways in VB, however when VB code is running causes the DDE link not to update and since the update is constant I cannot use selectionChange and Change VB Worksheet function result in the same error.
Edit Part Text In DDE Arrays Linked To External Program
I have a datafeed program that offers multiple DDE link types to retrieve different types of data. After pasting the DDE links into Excel, all works well. However... To modify the links means manually editing upto 12 different array formulas located on different pages then pressing Ctrl + Shift + Enter after each to change the data source being analysed.
I found that I can manually copy and paste a few characters into each linked array formula, Ctrl+Shift+Enter but it needs to be by Macro referring to a couple cells (Say A1, A2) containing the variables that alter the data source being linked to.
I need to make a VBA Macro that refers to these two cells containing text, and modify the 12-odd linked array formulas.
In this example Linked Array formula, the bolded PG and 9 would be variables pulled from cell A1 and A2.
All other components of the array formulas stay unmodified, it is only the "pg,9" text that needs to change in each linked array.
I have a worksheet that has multiple units listed on it. Each unit has a materials used list with quantities. I want to reference this column to another sheet and get a total materials needed list and also reference it to my inventory and subtract that number from my materials on hand. how can I do this?
Inventory Tracking ...
So far I was able to find solutions to all of my small problems with excel just by searching here but with this one I need help and ideas how can it be done differently.
We need to track inventory from when its taken, when it was installed and we need to know every month when inventory is counted how much do we have on hand in our truck....
I manage a small motel with 6 rooms and I'm trying to write a simple program to track my customer reservations including their details. All the commercial software is too elaborate and way out of my price range. I'm slowly learning excel but this has proved beyond my capabilities.
Tracking Changes In Workbook
I'd like to make a vba macro in excel which will be just like "Track changes" feature. I'd like to track changed cells and when and who did it. (Date, User, Cell, Last value, New value). It is important to make a report in separate worksheet and automatically fill this worksheet when the user save changes.
Tracking Who Opens Spreadsheet
I have a spreadsheet that can't be password protected but this doesn't mean just anyone should be accessing it. Is there a way to record who opens it and when? Ideally I could place some code into the spreadsheet that recorded on a sheet who has opened it and when. This will detract certain nosy people from accessing it!
Inventory Tracking Spreadsheet
A friend of mines wife has decided to get into MaryKay and he is wanting to create a spread sheet to keep track of the product she has.
He wants to be able to enter the product in and at the end of a show subtract whatever product she sold with a sheet showing what she has in stock.
Tracking Open Trades
I want column J to reflect the total amount of all trades which were open at the time the trade on the row is closed.
(the data is sorted according to closed trade times).
So while the trade on row 4 is the first trade to close, there were other trades also open prior to the closing. I need to show the total amount in column E of all trades which were open during the time window that the trade on row 4 was open.
Inventory Trend Tracking
Ive got an inventory of about 2000+ items as well as items that can be broken down into more specific groups such as colors, designs and sizes. What i want to know is how to get the correlation, thats excel function CORREL(), of each item for the time weve had it. The data I can provide is the monthly quanitity sold of the item.
For example I have item A.
and so on. Easy enough to do. My only problem is I would have to manually do that 2000+ times for my entire inventory to figure that out. What I wants is a command that can verify my item code, which is simple enough as I can convert the item code to something as simple as a string or leave it as is. Not every item has been in the system an equally long time though. If that were the case i could just copy it with blank cells to fill the space and copy and paste all the way down. So I need to find a way to get an IF statement to correlate the corresponding cells IF the item number matches the target cell. What I have right now is something to this extent.
Formulas For Baseball Tracking Spreadsheet
My spread sheet reads column A teamname, B games played, C wins, D Losses, E Ties, F winning % and G GAMES BACK
I'm looking for a formula to determine games back based on wins, losses, ties and also based on the number of games played.
example, team 1 played 9 games and is 8-0-1. Team 2 has played 9 games and is 8-1-0. Team 2 is would be a 1/2 game back. Team three is 8-0-0 also a 1/2 game behind team 1 but tied with team 2.
So in column G it would read team1 "-", Team 2 ".5" and team 3 ".5"
Tracking My Employee's History Of Changes.
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.
Tracking Status Of Open Work
I currently use a spreadsheet to track my work and I would like help automating some of the functions. I don't want to use macros if at all possible. I have some formulas but it doesn't do enough.
what i do is audit other peoples work and send out error notifications when needed. i use the spreadsheet to help me tack the status of the error notifications. when I send out an error notification the auditee has up to 3 chances to rebut the error so i need to keep track of where the follow up lies and when the error is finally accepted or removed (process completed).
I would like column U to tell me where we are in the process...
1.notification sent (by the auditor)
2.reply received (either accepted or 1st level removal request by the auditee)
3a. if error accepted- process complete
3b. if removal request-notifcation sent(by the auditor - error removed or error remains)
4.if still an error(following 3b) - reply received (either accepted or 2nd level removal request by the auditee)
5a. if error accepted- process complete
5b. notification sent (by the 2nd level reviewer - either error removed or error remains)
6.if still an error(following 5b) - reply received (either accepted or 3rd level removal request by the auditee)
7a. if error accepted- process complete
7b. notification sent (by the 3rd level reviewer - either error removed or error remains) & process complete (3rd reviewer decision is final)
I would like the V column to be the "date of last action"
since the notification bounces between people, i need to know who is currently responsible for followup & how long they've been sitting on the work. i would like the date to populate based on the manually inputed fields where I input sent & received dates for each level of action.
the spreadsheet is too wide for the post so i will put it on a website...
the columns are all titled and all manually inputed except for column U which is the one that I want to autopopulate with the current status of the error notification. the formula is a IF statement but due to the 7 formula limitation It came out too simple. it's better than nothing but I want it to be more specific.
Tracking Date And Time Overlaps
I'm trying to figure out a formula that would track a student's class schedule to see if there is a date/time conflict. I'm uploading an example of a ficticious student named Amanda for you to review.
According to the example, I should be prompted that there's a conflict between Amanda's first two classes because they both meet on Monday (M) and Wednesday (W) and the time during each of those classes overlaps.
This will need to be checked per student, based on their ID in column A.
Tracking Elapsed Time With No Date
I have an assignment to audit the time it takes convert a printing press from one job to the next. I've been using a watch to take the readings in hours, minutes, and seconds.
I recorded them in my spreadsheet, as h:mm:ss, but it showed a date also. I think I finally got that resolved, but now, when I try to subtract one time from another time, I get an incorrect answer. I think I need to convert all of these times into decimals, as in 13.23.44, as oppossed to 13:23:44.
One post on page 1 of my search said to multiple A1*24, but I don't fully understand. I tried that, but 11:30 became 11.50?
I need to extract the set up time, or job to job changover time in a decimal format if possible. There are other tasks being done with those numbers later in the spreadsheet that are more conducive to decimals, than the time format.......
Call Tracking Sheet - Electronic
I am trying to turn this call tracking sheet into an electronic one. Is is possible to have check boxes in the empty cells that will allow someone to just click them as they go along and it would create a total at the bottom to avoid countless hours of counting these sheets. Or even turn "Trans CM" into a button that you can click and it would keep track of how many times that specific button was clicked.
FIFO Inventory Method Balance Tracking
I've been trying to come up with either a formula or a macro (I'm a novice in VBA so it would probably take me forever to figure it out) to track the FIFO balance (First In-First Out) of items at any given point in time. I have attached a sample spreadsheet with the example and all the notes that might help figuring this out.
I want to stress that I'm not trying to calculate any cumulative balance or any FIFO pricing whatsoever; just separate tracking of the purchase balances at any point in time after varous sales using the FIFO balance.
350 People Tracking 24 Dates For Classes Each
I have a spreadsheet with ~350 people tracking 24 dates for classes each. On a seperate tab I have a macro that copies and pastes a pre-defined macro to display the due dates. My question is, can I have a macro run the calculation and put the result in the cell rather than have around 8,400 calculations on a sheet?
Job Hour Tracking With Pivot Tables
I have an Excel timesheet that the supervisor loads the time (in and out) for all the shop employees. These employees may work up to six different jobs in one day at various points throughout the day. I have no problem with calcs and formats on the daily time entry tab (sheet1). What I need is a way to track each job using a running total so that as the job is in progress we can see how we are doing against estimated hours. I thought perhaps I should at least make a weekly time summary pivot table thinking that it would make it easier to calculate, but it didn't help with running totals. The summary included (5) copies of the same daily entry tab (sheet1) just given a different name, in this case ..a simple date change and save the sheet with the date used. OK, here's the problem. How do I create a running total that would track each job? I forgot to mention earlier that each daily time entry sheet is saved and named by the date for which it represents. The weekly time summary pivot table works fine....but it only gives me the grand total of hours worked for each day...not a running total summary. I can view the pivot tables and go to the calcualtor and add the hours up per job, but that's defeating the purpose. Anyway, I hope this is clear enough to at least get some responses. Then, maybe by then I'll have a better understanding how to ask the right questions.
Time/date Tracking Only During Business Hours
i work dispatch at a company and we're trying to track how long it takes for a technician to arrive on-site. my worksheet currently includes the date of call-in (E1) , time of call-in (F1), Service Date (G1), Service Time (H1), among many other field. the easy solution is to use the formula (G1+H1)-(E1+F1), and use the answer as the total amount of time, but the problem is if a customer call in at 3:00 PM and we service them at 9:00 AM the following day, it looks like it took 18 hours to arrive on-site, when in reality, since we close at 5:00 PM and open at 9:00 AM, we only took 2 hours to arrive on-site. is there any formula i can write to account for non-business hours and weekends, or is this a bit above what i should expect from excel?
Tracking Changes Made To Worksheets On A Hidden Worksheet
I've developed a workbook that is designed to allow users to produce a list of aspects (one per worksheet) and edit them as the aspect changes. However, i would very much like to create a 'log' of changes. For example, everytime a cell is changed the cell reference, worksheet reference and date are added to a list on a hidden worksheet.
Creating Personal Expense Tracking Form
I'm trying to make a spreadsheet that will track my expenses. What happens is I enter in my daily expenses in a "Notes" worksheet. This includes the date, whether it's a debit/credit, and what category is it (rent, tuition, entertainment, work income etc). It looks like this
Date - - - - - - Debit - - - Credit - - - Category
4-May-06........................$30 ..........Dining out
Then I have another worksheet called "Expense outline" which pretty much sums all expenses in each category and displays a summary. So it would show how much I have spent in total on each category for each month. Looks something like this
So what I did for the Entertainment summary for the month of May was, I used a SUMIF(column of categories, "Entertainment", column of credits). This will look for the category name "Entertainment" in my "notes" worksheet, and sums the corresponding amount from the credit row. The problem is, I also want to include it so that it will automatically differentiate between the different months. Right now, when I'm choosing the column of categories for May, I select only the cells in the month of may when I'm choosing my column of categories and credits.
Tracking Access To Excel File Through VBA Rutine
I was looking for some help on a VBA solution. I would like to run an on open event for a scheduling workbook. It should open a logging workbook, write the %username% to the first blank line in A:A then save and close the logging workbook, leaving the scheduling workbook open and ready for input.
The following UNC can be used as the location for the logging workbook....
Call Log Tracking Issues With NOW() Looking For Answer ASAP
The employees have 3 chances to contact the customer before sending out written notification. The goal is to only have the employees enter an "x" in an attempt cell which will cause the date and time attempt was made to autopopulate in another cell. I've been using an IF formula to do this [=if(A1="","",NOW()], but each time a move down to another customer it adjusts the date and time for all other fields as well. How do I lock in the date and time they marked the cell without it affecting others. This is basically how the attempts are set up. The only unprotected cells would be those under A2-C2. Example would be employee places and x in cell A3 and D3 autopopulates the date and time A3 was modified. The problem I am running into is when A4 is modified, it not only autopopulated D4, but it changes D3 to the time A4 was modified as well.
A B C D E F
1 Attempt Attempt1 Attempt2 Attempt3
2 1 2 3 Date/Time Date/Time Date/Time
(A-C are over the word attempt, A2=1 B2=2 C2=3, D is over Attempt1, E is over Attempt2, F is over Attempt3)
Count Formula- Spreadsheet That Is Being Used For Tracking Work Completed Each Day In A Week
I have a spreadsheet that is being used for tracking work completed each day in a week. Each day has 5 columns and 10 rows of data to potentially be entered, some of the data is text and some is numerical. Can anyone help me come up with a formula that will count the text and the numerical entries for each day? I tried to use a nested if statement but it exceeded the number of nestings available. The range of cells for the first group is B6:F16.
As a side question, will count work for a range of data or only one column or row at a time?
Filtering For Summary: Produce A Spreadsheet For Tracking Pupil's Progress Through A Year At School
I'm trying to produce a spreadsheet for tracking pupil's progress through a year at school (Targets/Predictions etc) but am having some problems with creating a summary of data gathered for each pupil. I have attached the file for your perusal; On the data entry sheet staff will fill in the appropriate data, and on the summary sheet I am looking to generate summaries for each pupil (I have set up how I want it to look). This may sound easy, (and probably is); the issue is that there are around 30 subjects in total, but pupils will only have be doing 5 of them, I need the summary sheet to show the information for subjects they are taking only (It should come up with the subject name under the headings subject1/2 etc and the appropriate grades to go with them, missing the blanks out. Obviously I could do this by cutting and pasting for each pupil...But there will be almost 2000 pupils in the list!!!
Tracking Daily Total Sales And Individual Tender With Data Extracted From .dbf File.
I want to track daily sales of a shop with the tenders (Cash, Master, Visa)seperated.
Everyday there will be a file ctp.dbf from a folder YYYYMMDD (previous day date) which contains sales details.
I tried to use sumif commands and everything is working fine. everytime i have to open book.xls and from it I do a files>Open to open the ctp.dbf for the calculation to be done. is there a way where by i can open 1 file and everthing i calculated properly?
Also this book.xls can only do for 1 day how can i go about having the daily sales detail of the month (look something like sales summary.xls) or even year in 1 excel file?
attached is book.xls and sales summary.xls for reference.