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 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
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'm using this code to run a macro whenever data is entered/removed from a cell within a range. (two macros and two cell ranges).
[Code] .....
Now I need to do the same thing for a different cell range (E3:E52) and a different macro (FormatChart2). Do I need to make a new Private sub or can I add it into the existing code?
I have a whole spreadsheet of data which looks confusing so I need to make it look more attractive to the viewer.
I have column A which shows a number of the branch conducting the transaction and the column will contain numerous different branches which I have sorted into branch order.I think there are too many branches to use the conditional formatting condition (probably 5000 different numbers)
What I wish to do is change the colour of the cell when the branch name changes.
For example
Column A= Branch number
123
123
123
345
345
345
456
456
456
789
789
What I need is to highlight all branches 123 with a colour (any colour say red) but when the change in branch number occurs to 345 use a different colour to highlight all the branches (cells) with the number 345 (say grey).Then when the branch changes to 456 use the same colour that highlighted branch 123 (ie red) .Then again when the branch changes to 789 change the colour back to grey again so the spreadsheet shows rows highlighted when a different branch occurs
The above example would look like
Column A
all branches with 123 ( cell highlighted any colour but say red)
branches 345 cells highlighted grey
branches 456 cells highlighted red
branches 789 grey
I have had a TREMENDOUS amount of assistance from a true gentleman, Jaslake; aka John. In an attempt to quite monopolizing his time I wanted to throw this at the board. I have a number of sub sheets with exact ranges of (B4,d4:b44, D44) update an individual cell (H6:H86) in the” summary” page. My attachment explains it in detail but currently I will show you the formula that resides in (H6) of "Summary". (H6) = Sheet "1". This formula only covers (B4,D4:B10,D10) for example purposes but I need it to run all the way through (B4,d4:b44, D44) for all 80 sheets.
=('1'!B4) & "-" & ('1'!D4) & " | " & ('1'!B5) & "-" & ('1'!D5) & "|" & ('1'!B6) & " - " & ('1'!D6) & "|" & ('1'!B7) & "-" & ('1'!D7) & " | " & ('1'!B8) & "-" & ('1'!D8) & "|" & ('1'!B9) & " - " & ('1'!D9) & "|" & ('1'!B10) & " - " & ('1'!D10).
Results look like this:
March 01, 2009-Today looks good | March 02, 2009-Today looks bad|March 03, 2009 - Today we had issues in Boston|March 04, 2009-Issues in Dallas | March 05, 2009-Issues In New York|March 06, 2009 - New York is complete|March 07, 2009 - Dallas is Complete
NOW, with this said what I really would like to happen, if it is possible, is to have the "Summary" (H6) only have the last range that has been updated showing. So if currently B4,D4 is showing in (H6) once the user types into B5,D5 then it replaces B4:D4. I would like this action to repeat itself until the user has completed their event. The reason I can not stick with a formula in (H6:H86) is because I have a macro running a copy.paste, then clear.contents once a selection is made in Column 4. The information must remain on the sub-sheets but it can be replaced in “Summary “. “Summary” is only a quick view of the current status while the sub sheets are a log of all the work done. So, the following
is there a way to save column widths once a pivot table is refreshed..
if not what is the vba code (and on what event procedure) to set the width for columns M to AF set to 13.
What I have is a database of measurements taken, where X-axis is distance across a surface and Y-axis is measuring deformation to that surface. Each measurement is actually a group of data points from one sweep across the surface on a certain day, resulting in a line plot for that group. What I'm trying to do is create a "checksheet" so that you can overlay plots from multiple dates on the same grid to compare how the surface has changed with each measurement. I've got the checksheet part working properly, and I can toggle things on/off as I like as long as my data doesn't change.
The rub is that this data is gathered via query, and each measurement doesn't have the same # of data points. If I update the query to look at a different date range, the plots are now off because the old data range doesn't match the new data.
Is there any way of changing the data range when a query gets updated, or a way to tie it to a function?
Workbook contains the following sheets : PIR TrackerChartsSAMPLEFINALValidations
When a change occurs on PIR Tracker, the following occurs:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim Rng As Range
Set Rng = Intersect(Target, Range("A1:A500"))
[Code] .....
I also want the pivot tables on SAMPLE and FINAL to be updated. What do I need to do?
The following formula is in cell A3: A3=IF(A2="X",A1+365,""). Rather than A3 displaying only 1 year, I would like for it to update after that day occurs to =A1+730, and then A1+1095, and so on.
View 2 Replies View RelatedI want to isolate text on either side of dashes ( " - " ) that occur either 2 or 3 times in a single cell.
Example:
Apples - Pears - 99 - 100+ years
Bananas - 56 - 57+ years
In particular, I would like to capture the "99 - 100+ years" and "56 - 57+ years" text on its own.
I tried this " =SUBSTITUTE(A1, "*- ", "", 1) but it doesn't work
I am trying to categorize a list of words based on the first letter.
For example:
[Code] ......
I want to see if the letter 'D' in 'Data' occurs in a range from A-G, if so display "A-G" in cell B,
else if the first letter occurs from H-M, display H-N in cell B
else if the first letter occurs from N-R, display N-R in cell B
else display S-Z in cell B.
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 RelatedIs 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 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.
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 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.
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 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.
In the middle of one of my subroutines, my function sometimes hits an error (Subscript Out of Range: because it's looking for a string when it is not there).
If this error occurs, I want it to then look for a different string.
So I ONLY want this other string search to occur if I get an error at that point in the code. I've tried "On Error GoTo ....." but it's doing that GoTo everytime, even if no error occurs. I also can't put "Exit sub" before the On Error statement, because there is a lot of code after this point as well.
I have a list of numbers like
A B C D E
1. 8 1 4 7 6
2. 7 4 6 7 8
and so on. In another sheet I also have numbers such
A B C D E
4 3 8 9 0
I want to be able to compare any numbers that occur in sheet 2 that are in the rows in sheet 1 and turn the cells a colour for example turn the cell green if the number occurs.
With Range("H2:H5000")
.FormulaR1C1 = "= SUMPRODUCT(('Data Sheet'!R2C11:R5000C11=RC1)*('Data Sheet'!R2C81:R5000C81=""G - Prior policy incident handling errors""))"
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Right now its simply counting, but what I need it to do is to sum col L if col A matches col K. Col L contains numbers, and I need a sum of those numbers if the match occurs in A & K.
I am trying to create a formula which would display a date (prior to the computer's system time) when a certain amount of text occurs.
I have attached my workbook with an example and further explanation.
I want to count the occurrence of certain letters in a range of cells. In my attachment I need the sum of how many times the letters "C,M,Y,K" occur in the range A2:D2.
View 2 Replies View RelatedI have a list of data that is the day of the month(1-31) that an event has occurred. (sample data in attachment) I want to find some way to find when the "hottest" time of the month is when the events are occuring.
how to get the end result. I think for myself a chart of some sort would be good to see visually, or some kind of statistical breakdown.
My only requirement is that the list is always being added to, so the end result needs to be updating with the complete list.
Im not an expert in excel, so the more details on how to do this the better. Even better would be creating it in the attachment, and uploading your version so I can see exactly how you did it.
I am going to be taking this idea and apply it in the same way for day of week (1-7) and time of day.
I have a list of data, integer values ranging from 0 to 36. Imagine a Roulette wheel. The list is long, with over one hundred data points. I would like to view the first 14 data points, and count how many times each value occurs. How many 0's? How many 1's? How many 2's?...How many 36's?
Obviously, many values will have 0 occurrences. Most will have 1 occurrence, some 2, and maybe one or two will have 3 occurrences. I doubt we will see a value with 4 or more occurrences, but it is possible. With this accomplished, I will then note the results. So, that accomplishes the first 14 data points, call them 1-14.
Then, I want to move down the list 1 data point, and repeat the process. So, here I am looking at data points 2-15. Basically, it's the same set of data, with the first point missing, and a new point added on. I will then note the results. I want to continue doing this until the last 14 are viewed.
Column A: multiple names of people, almost all names occur multiple times Columns B: counts how many times the name occurs in total
What I want in column C: if there are e.g. 18 times the name 'Peter'. I'd like to see in columns C that the first 'Peter' found has 1, the second Peter found has 2, etc; this offcourse for all names...