I am useing Excel 2003 and OPC server client for excel, and i am monitoring 6 values, that are changeing each second. I was trying to make a function to collect this information, so i could form a statistic table, but unfortunally with no success.
I can watch how the values are changeing their numbers, but i cant sotre the values in the rows below. Does anyone know how can i do this
I have a horizontal bar chart in which some of the bars represent positive values and others represent negative values.
How to format the bars so the "positive" bars are shaded in one colour and "negative" bars in another colour? I'd prefer not to change the bar colour manually as the values change frequently.
I have a workbook that includes 4 seperate sheets that are used to record time and expenses for 4 members of staff. I want to write a macro to select the data I need from each sheet and colaberate together in a 'data' sheet so I can combine all the info to run time and expense reports per client showing combination of all time and expense incurred from all 4 staff.
I have named cell ranges in each of the 4 time-sheets. I proceed to record a macro, select the first named range, copy and paste into my data sheet, do a control home then control down arrow, then one more down arrow to get to the first blank cell and repeat the process for all four time-sheets.
This works until I add a new line and then the data will only appear for the last time-sheet (last row of data).
I have an excel file with column stating month and rows are phases ... for each Phases we add the revenue numbers by month and it does keep changes as and when scope changes. However, i am finding it difficult to capture the changes ... is there a way i can set the guidelines to ensure all historical data changes as and when any updates and it should store the historic inputs for consolidation ... i would like to be called as Baseline, Re-baseline1, Re-beseline2 like wise.
Sample attached : sample_auditing.xlsx‎
I would like a formula to capture all values >=50 to <=99 in a row.
View 14 Replies View RelatedIn my sheet, i have a list of machines in cells A2 to A5 & list of Plants in cells B2 to B5 as below
Col A..........Col B
Mach.........Plnt
A...............1
B...............2
C...............3
D...............4
What i want to capture is the last machine selected by clicking and also the value of the last plant selected by clicking
I want the machine last selected (clicked) to be in captured in cell D2 & the Plant last selected (clicked) to be in captured in cell D3.
i.e if i click Machine C in column A then go the column B and click Plant 4
Then D2 cell should show C and D3 should show 4
I need a bit of help with the below macro which I am trying to create. I recorded the below vlookup, which works perfectly. It checks a list on sheet “Map” and returns a value depending on whether the reference is one of the 6 or not. These 6 are likely to change over time so I would prefer to declare them as variables rather than build them directly into the macro
View 4 Replies View RelatedI have a spreadsheet that has two tabs that show different views of my report. In tab "Team View" I'd like to capture the information that is in "Cost Centre View" in the fields Nov09 to Oct10.
I want my formula in "Team View" tab to take the TOTAL "Run Rate Reduction" value in "Cost Centre View" ONLY and have the same "Initiative Type" in "Cost Centre View" that align to what is shown in "Team View" in column A (i.e. ISO).
I tried using a SUMPRODUCT but my formula didn't work.
Is there another formula that will give me this calculation?
I would like to know how to capture a cell value at a specific date each month in a situation where this cell can change value as it is a formula. For example, the cell returns a percentage that is derived from other data that can change regularly. It may be 45% today, but a user may alter other data in the spreadsheet and it could be 67% tomorrow (or any other percentage).
I want to graph a monthly trend of how this cell value changes by capturing the cells value on the 20th day of every month, or it would also be ok to work on a periodic snapshot of every 30 days.
I have a workbook with several sheets , the 1st 31 being named Mar.01-07, Mar.02-07,etc
I'd like to capture the averages(of cells with values) from cells B7:C24 of sheets Mar.01-07 thru Mar.31-07 and place the values in the sheet called "averages" with the average for B7 going to cell B7 in the "average " sheet, B8's average going to cell B8 in the "average" sheet and so on.
I do data entry for a webstore and one cell is used to map out the exact product category/subcategory path. I currently have to copy the appropriate path from a long list on one page and then paste it into the cell. I have to do this a hundred times a day. It would be nice if each cell within that column can have these values stored in them so I can just click on the cell and open up a drag down box and select the needed value. Is this possible to do in Excel?
View 2 Replies View RelatedIn an excel i have 3 columns they it contains around 12000 records
Group FA Title
A S1 bbbb
A M1 xxxx
A M2 eeeee
A S1 ffffff
A S1 pppp
A M3 aaaaa
A M2 ooooo
A M2 qqqq
A M1 ttttt
Here i need to get the unique FA, so i filter the column FA, my question is, After filter with FA column ,is there any way to store these unique FA(ie S1,M1,M2,M3) into an array using vba?
I have a list of names from cell A1:A10 in sheet "Input." Each of these names has its own corresponding sheet in the workbook. I want to be able to run the same exact VBA code for each sheet. In other words, I am trying to get my name variable to automatically change to the next value on sheet "Input." I'm sure this is pretty simple to do, but I can't seem to find anything that works!
View 5 Replies View RelatedI have a worksheet where some cells contain a comment. I don't know beforehand how many of those cells are present, nor their address. I want to write a macro that stores the values of only the cells that contain a comment into an array (of course the size of the array is not known beforehand). This should be done by scanning through those special cells in a given order (by rows, by columns, whatever).
View 4 Replies View RelatedMy data comes from 3 different sources
Source A = 4 columns (3rd one is important)
Source B = 8 columns (3rd one is important)
Source C = 5 columns (4th one is important)
Is there a slick way to capture values based on the number of columns in that row?
How to store a value in variable after concatenation of two values and putting it into the same cell.
Let assume, in cell A1, we have value 1 (numeric). And in code i have a variable with stored value as "%".
Now i want to concatenate 1 and % and put it back into cell A1 as 1%.
I have a written a code, but seems to be wrong one.
Sub Percentage()
Per = "%"
lr = Sheets("Process Overview").Cells(Rows.Count, 3).End(xlUp).Row
For i = 10 To Sheets("Process Overview").Cells(Rows.Count, 3).End(xlUp).Row
If Cells(i, 4).Value = "p" Then
[Code] ........
how can i store the values of an autofilter's list in a array using VBA.
View 4 Replies View RelatedI am trying to do some calculations as shown in the file. The MAcro has to show the formulas and then calculate the value, the problem is that the formulas change all the time and depends of Column A. Column A defines de Values that we will use so they will change as well. I have fould the way to copy one formula and make it usefull in the cell next to it but the problem are the cells that I named before. All my values change in each Loop and finish with the values of the last loop. I would like to save each value that corresponds to the variable (column A) and his formula, to then calculate.
View 4 Replies View RelatedThis may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
View 2 Replies View RelatedI have values in COlumn A, probably about 50K rows, The total unique values on the column A is only about 27. I need to capture all those unique values and paste them in column B. I didn't want to do "Advance filtering" since the workbook has many macros's on it that the user simply click on. Im thingking of creating a loop and compare values already pasted in Column b but then that will take too much time to complete the loop. Is there any easier or yet more efficient way to accomplish this?.
View 2 Replies View RelatedFormula to identify the top 10 most frequently occurring numbers in column A.
View 4 Replies View RelatedI have a list of numbers in an Excel range. Most of the numbers are the same but some are not. I need a function that will go through the list and return the value which occurs most frequently. (Not the number of occurences but the actual value). I need to do this in VBA.
View 3 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 am using a formula that finds the most frequently occuring text from a range of cells that have the text 'Ra' 'Ma', 'Mo' or 'Mi' in them (each cell has a drop down list with these text items only). I am currently using this formula to find which of the four text items appears most frequently in the range:
=INDEX(D6:D15,MATCH(MAX(INDEX(COUNTIF(D6:D15,D6:D15),0)),INDEX(COUNTIF(D6:D15,D6:D15),0),0))
This formula works fine in a case where there is more of one text item than any other, but when there are two items with the same number of entries, this formula comes back with the item that fulfils the criteria first - i.e. if 'Ra' appears 5 times and 'Mo' appears 5 times, then either could come back, depending on which appears first in the range.
The text items rate in importance with 'Ra' being the most important, then 'Ma', then 'Mo', then 'Mi' is the least important. What I am trying to do is, in cases where more that one text item appears the same number of times, get the formula to display the one that is the most important. So for example 'Ra' will always appear over 'Mo' if both have 5 entries, but 'Mo' will appear over 'Mi' if 'Mo' and 'Mi' both have the most entries.
I am given a database of 292 cells and i am asked to calculate the mode. How can i do it?
View 9 Replies View RelatedI know the mode function finds the most frequently occuring number but is there a way to find the most frequently occuring word/text?
View 3 Replies View RelatedI have 12,000 rows on my excel spread sheet of data and my average volume for all those days are 17,661,398.6
I noticed that the so called average did not show up not even once in all of the 12,000 rows. Thus I conclude that the average is not normal ? How to find & highlight the most frequently occurring number, the usual, and or the normal numbers?
I have a few columns referencing Crash numbers. The majority of each column is a null value, with about 10-20% of it actually containing the number of a Crash. I want to run one MODE each on a column to see what the most common Crash is.
The following
=MODE(Table_X[Crash Alpha])
Results in a #N/A Value Not Available error. What do I need to change to make it work on the column with some nulls and some numbers (no strings)?
As a secondary question, if I wanted to apply a date filter (the columns are part of a table, including a Date column) like the below:
If(Table_X[DateTime]>F9,If(Table_X[DateTime]<F9+1, etc.)
(F9 being the location where my filter Date is)
Where would I apply that to the Mode? Should I just change it to an If statement, with the Mode positioned like so?
=If(Table_X[DateTime]>F9,If(Table_X[DateTime]<F9+1, MODE(Table_X[Crash Alpha]))
I'd like to create a macro to display the top 5 most frequently occurring repairs on sheet 3 of my workbook. I'd like the macro to analyse all the sheets in my workbook except for the first 3 and then output the results on the 3rd sheet from T50 onward. I have attached a template as an example of how all the sheets in the workbook look (excluding the first 3).
All the sheets have the same layout as the "Survey Template" worksheet.The repairs are located under the Log Book Review of Historical Structural Repair in the Survey Template worksheet and all the repairs are picked through a drop down list that is based on a table of repairs listed in the Parts and Prices sheet within the workbook. I have assigned random serial numbers to the repairs (if that makes it any easier to code instead of locating exact string matches).
ExampleSurveyTemplate.xlsx‎
I need to know from the combinations below which are the 2 numbers that appeared the most. Example....
View 4 Replies View Related