I'm looking to change raw data into a "point" system. One of my value exchanges is every $1,000 equals 5 points. So if cell A1 equals $4,685 I am looking for cell A2 to give me a value of 20. (5pts times 4 increments of a $1k) The point value only changes at the $1k milestones, so the remaining $685 does not change the point value. What would the formula look like for something like this?
View 3 RepliesI have a section of my program that asks for board scores (point value is 6-10 in increments of .5) I have set up a data validation so that users can only enter those specific values. Problem is, none of the values can be more than 1 point from each other. (the part I have yet to figure out)
Example, if the first value is a 6.5 the second is 7.0 and the third is a 9.0, I need the data validation to display an error because the 9.0 is beyond the one point spread.
Summary: Display an error message if any of the points are more than 1.0 point from another.
Basically, I have a Userform and am trying to have a user enter 1 datapoint into a Textbox. Based upon that datapoint (it's 9 digits), I am trying to see if I can therefore reference a linked Access Table to fill in other textboxes with information tied to that record (so if they enter a unqiue ID, I want to automatically return Name of Person, Location, etc.) . I liken it to a vlookup in regular Excel but am just not sure how to go about it in VBA.
View 2 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 a pivot table as shown below which is pretty straight forward however I am trying to create a macro that will automatically change the point name to match the point name in column E then copy that resulting dispaly to another sheet then pick the next point name in line and do the same thing and repeat for 50 rows, so my end result will be 50 pivot tables ready for printing. I can do this manually but I am trying to make it automatic...BTW the point names in column E change everyday but the pivot table supports the name changes.
Option Explicit
Sub PointName()
Dim Ws As Worksheet
Dim Rng As Range, Cel As Range
Set Ws = ActiveSheet
Set Rng = Range(Cells(2, 7), Cells(Rows.Count, 7).End(xlUp))
For Each Cel In Rng
Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
Ws.Columns("A:B").Copy
Sheets.Add
With ActiveSheet
.Paste
.Name = Trim(Cel)
.Range("A1").Select
End With
Next
Ws.Activate
End Sub
I turned it off for a while and when I turned it back on I am getting an error
Unable to set the _Default property of the PivotItem class
Debugger is highlighting
Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
Why the following (do it in the immediate pane) gets the wrong answer in VBA.
[Code] .....
this should return 0.1 but what you get is
9.99999999999996E-02
I've got an excel sheet that lets people choose between 2 choices. I'm using scroll bars to make it easy for them to choose and visualize their preference with as little mouse clicks as possible. The scroll bar is only one sided though, meaning it starts from a min value to a max value.
I would like a scroll bar with a of 0, with the value increasing positively as the user scrolls to the left, and the value also increasing positively as it scrolls to the right.
I'm trying to create a macro that when run, scans Row 12 (only after column E), Finds the first empty cell, then inserts the cell value from Sheet4 CellE8. Then the hard bit begins. I need it to insert cell info in all the cells below it, from different locations...
For example
A
B
C
D
[Code].....
In the above sheet, I need it to go to cell D2 and insert the values from Sheet4 CellE8, Then proceed to D3 and insert data from Sheet3 D4, then to cell D5 and insert data from Sheet1 A7, etc etc
I dont mind doing each cell individually, but they will always be in the same column (and row 2 "Value" will always be the one that determines the next empty column).
Pivot Chart. I would like to set up something to where a user can click on an individual value on a pivot chart (currently a line chart set up with 4 data series) and somehow display some underlying data. I have a lot of information stored in a data worksheet that I can't display all at once, but if a user sees a questionable data point, he/she can click and learn more about it from source data, or even a new query of the data worksheet.
I am using Excel 2010
I want to use a Pivot table to filter data to show just the studies that contain patients from the 'South' area?
As per example below I want to be able to see all the patients in all areas but only for studies that have patients from the south. I put together an array formula that works well for small tables but is too much with one one my sheets that contains 200,000 rows.
Before filtering:
Study ID
Study Short Title
Study Patient ID
Area
1346
LLP
90126
Northwest
[code]....
In the attached document is a timeline made from a scatter chart. Error bars using custom values are used to show the length of each task, however I can't get the chart to include error bars for the last 2 data points (tasks).
View 1 Replies View Relatedis there any way to use the right function to return any data to the right of the decimal point?
eg 117.22 would be .22
108.1225 would be .1225
102.358 would be .358 etc
If I check the box labeled catagory name on the intial creation or within chart options on the right click menu each data point gets labeled with the name of the catagory.
I would like to label a significant event at one of these data points does anyone know how to do this? If this isn't a built in feature does anyone have a work around that would look good?
I have 100 rows and 100 columns, where row 1 is dates, and column A is times, and within are numbers. On a second sheet I have a column (A) with random dates and another (B) with times. What I need is a third column (C)on the second sheet that looks at the date and time, finds the intersection on the first sheet, and returns the number found there.
View 14 Replies View RelatedI now have a XY scatter graph with 109 individual points (representing schools) and a macro that tells you the plot details when you hover over. It's ok if you want to know which school is down the bottom but if you don't know where the desired school is in the first place, it's a lot of trial and error before you find it.
What could I look to add that gives the user the option to have a single data point highlighted (on selection?) so they don't have to manually look for it?
I want to collect data from multiple files that have multiple sheets and data in them. Basically what I want to do is to copy every 600th data point for 6 times (1 hour) and then move to the next sheet. Once done with the sheets, I want to move to the next file. I had done a similar code before that worked, and now that I tweaked it, it doesn't work. Plus I'm getting a compiler message telling that "For control variable already in use". I have attached the code I'm using below.
[Code] ......
Trying to capture data from specific fields (which are populated with live data collected elsewhere) into new fields based on date. Ex:
A1 - A4 have "totals" derived from an external source, and the fluctuate daily.
I want to take today's totals and drop them into D1 - D4. Tomorrow, I want totals dumped into E1 - E4, the next day they go into F 1- F4, and so on... in other words, I'm tracking daily totals over time.
I've started with a macro that I would attach to BeforeSave as follows--
Showed a line chart using 2 vertical axis where the first was a single line along the graph (ie. 42%) then the second data series was a single data point that was above or below the line which easily showed if the result lied above or below the threshold.
How to get a straight line across the body of the just to be used as the theshold line?
I have a range of data that has been collected by date starting in H4 across several columns. This means that my entries start at different points in each row i.e. -
Row 4 first entry Column AJ
Row 5 first entry Column K
Row 6 first entry Column AB
Using vba is it possible to find the first entry in Row 4 and copy it and all the figures following it in that row to K4 on Sheet2, and then row 5 to K5 on Sheet2 and so on?
My goal, albeit a simple one, is to return and store a single value within a Series. It is the first in the series collection.
Dim MyPoint As Integer
MyPoint = Worksheets("My Worksheet").ChartObjects("My Chart").Chart.SeriesCollection(1).Points.Item(1)
However, after i type the period after "Points", no autocomplete listing appears, and when the expression is evaluated, I get the error 'Runtime Error 438 - Object doesn't support this property or method'. How to accomplish the task of retrieve a single data point in a series and 2) Why the Points and Item methods don't seem to work as described in the help documentation
I have been looking for a printable overview of Excel VBA that covers the fundamentals like program structure and syntax, but have been unable to find anything other than pure examples (like many 3rd party sites) or unprintable references (such as Microsoft's help file). If there is a site that covers the fundamentals of using Excel VBA in some depth
I am building stacked XY Scatter charts and having difficulty formatting the y-axis values (which are data series point labels)
Rob Bovey's XY Chart Labeler manages this fine, but I work in a strict corporate environment which does not allow downloads. Also the file will be posted on our web as a tool for all company users to extract and analyze data from corporate databases.
I am stuck on the number formatting of the labels. My file is too big to attach; I have included the code below.
I'm sure there is something very basic that I am missing. Needless to say VBA is a challenge for me.
Option Explicit
Sub Atest()
Dim intR As Integer, intP As Long
Dim sngVal As Single, sngY As Single
Dim dtX As Date
Dim chtTst As ChartObject
Set chtTst = ThisWorkbook.Worksheets("GRAPHS").ChartObjects("Chart 18")
chtTst.Select
ActiveChart.SeriesCollection("Y Axis Values").DataLabels.Delete
The first data point of my chart appears cutoff along the y axis
I formatted the x-axis by clearing the check box "Value (Y) axis crosses at maximum category"
If I add the tick mark back in then my first data point does not begin at the origin.
Screen Shot attached of both cases
Ok, so here's the trick:
I know how to create combo or mixed graphs with line/bars on single axis, and secondary axis. I even know how to do mixed stacked and unstacked columnar charts...
But how do you depict in single view graph
a single bar (1 datapoint) with a single line (upper control limit e.g. target).?
And I don't want to to use shapes to draw the target line.
I want the target line to be automatically plotted by Excel...
At the moment I've got a chart with 3 series lines on it and each line has 12 points. I need to select the 12th point on each series in the chart and create a label for it. The 3 lines are Budget, Forecast and Actual and often the Forecast line hits the same points as the Budget line in the chart and every time I try to find a way to select the last point on the Forecast line, I end up selecting the budget one because it's the one in the foreground.
I can select the forecast line with the keyboard arrows, but not the last point.
I also try to zoom in to get it but one of my charts has exactly the same forecast line as budget line and I still need the forecast line because the forecast will change in future.
I can delete the budget line then add it back, but this is difficult and one I'd have to repeat many times because I'm always making graphs and labelling the final point in a series is something I'm going to have to do more often.
How do I select the last point on the forecast line if it's directly behind the budget line?
I have a set of data, for example:
Code Manager Level
abc 123 ab1
def 456 cd2
What I need is to create output that looks like this:
A B C
abc Manager 123
abc Level ab1
def Manager 456
def Level cd2
So essentially for each code, I need two separate rows that say what heading and the value is.
I have instances where my Y variables sometimes contain a zero in the data and i need a formula to skip over that data point in the LINEST calculation.
Y variables are in Row 1, Columns A:E
X variables are in Row 2, Columns A:E
The following formula is returning a #VALUE! error: .....
I am looking to create a dynamic bubble chart. To do this I am using offset to create the series formula which without listing all the data names turns out to be something like this (where 297 will adjust to the number of rows with data): =SERIES(Project!$A$5:$A$297, Project!$J$5:$J$297,Project!$P$5:$P$297,2,Project!$Q$5:$Q$297)
My problem is that by creating the chart in this fassion I am unable to get a unique name for each data point. For example, if row A looks like this,
A5: 1
A6: 2
A7: 3
each data point will be named 1 2 3. How do I either create a new series for each row dynamically or get the specific name from column A to associate with the correct data point?
i'm using this source to add labels to data points in charts:
Sub AttachLabelsToPoints()
'Dimension variables.
Dim Counter As Integer, ChartName As String, xVals As String
' Disable screen updating while the subroutine is run.
Application. ScreenUpdating = False
'Store the formula for the first series in "xVals".
xVals = ActiveChart.SeriesCollection(1).Formula
the code is from ms and works. (for some reason they also declare variable 'chartname' although it's never used, anyway). the full thing here: [url]
i actually have my source data filterable. so depending on the filters chosen, the chart updates itself. so it removes data points in the chart as more filters are used.
the problem is that the labels ignore the filters. the code above just goes down the column to grab the labels grabbing values in order, even if they have been filtered out.
a cumbersome workaround would be to copy the filtered data to another range and use that for the labels. this is neither optimal because i have lots of data or elegant.
Is there any way to set the list from a Combo Box to automatically start in the middle?
Whenever I select a Combo Box, the drop down list always automatically starts at the top of my selected range, ideally I would like it to start in the middle.
For example my Range that I am filling the Combo Box with lists dates: Today()-7 to Today()+7.
At the moment the first item in the combo box list is Today()-7, I would like it to be Today().
how to get a line in a graph to become a curved line between 2 data points in 10 point chart. I the pictuer attached I am trying to get the line to curve at the second data point 5(ms) and the third data point at 80(ms). The aftr 80 the line become staright again.[IMG][/IMG]
View 3 Replies View Related