Create Charts From Same Data But Each Based Ob Data Criteria
Aug 31, 2006
Hi, have a question regarding dynamic charts, specifically I am trying to create several dymanic charts based on data in a single column (So i can only say my first set of data will be starting a a set location in the chart, the rest has to shift down based chart data above it). This data should organized in charts depending on the data in several other columns. For instance, need to pull out values from column based some ID and some PartNumber. A sample excel or VBA would be much appreciated. I know VBA but do not know Excel VBA much.
View 9 Replies
ADVERTISEMENT
Jul 12, 2012
I have a quite big database as a result of a study in Excel 2010. There is a special block of data for every participant in the study, including 16 data series in 16 lines. I have to make a unique chart for every line.
One block is bulid up in a way, that every row is the input for a chart, with the exception of one "spacer" row, which is empty.
Name
1 2 3 4 ...
A
B
C
D
E
F
For example in the sample above, there should be 6 line-charts (A, B, C, D, E, F) next to the block.
Can a macro be written, so I can automatically create the chart-set by selecting the block's upper left cell and running the macro? Or is there any easier method?
View 5 Replies
View Related
Jun 26, 2008
I need to make a macro that creates a specified number of graphs depending on the file's number of data sets. I know the number of sets that are in the data, and I know the number of data points that were taken. Here is what I have:
Sub Graphs()
Dim Startpoint As Integer
Dim Endpoint As Integer
Dim count As Integer
Dim xStart As String
Dim xEnd As String
Dim NumberSets As Integer
Dim yStart As String
Dim yEnd As String
Dim DataSet As Integer
Dim Data
Startpoint = 11 'The first set always starts in row 11
Endpoint = Range("L4").Value + 10 'Thefirst set always ends after the value of L4+10
NumberSets = Range("L7").Value 'number of times I need the loop to work
count = 1..........................
View 2 Replies
View Related
Jun 10, 2008
i am having a problem of updating my graphs in the application i devloped, my application is like this, go to sheet 1 click showuserform button and suer form contains combo and list box select the sheetname in the combo and select two values in the list and click plot.it plots the graphs acording to the selected values but my problem is it makes a newchart every time when we select a new week range it is getting stacked...it is not getting updated with the most recently plotted graph ??? i am facing this problem when i am opening my application in other computer...u can see my problem when u open my application. i am attaching my workbook and this is the code i used for making the chart
Sub MakeChart()
Dim rng1 As Range, rng2 As Range
Dim gs As String
gs = ComboBox1.Value
Application. ScreenUpdating = True
'First selected
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
Set rng1 = Columns(1). Find(ListBox1.List(i))
Exit For
End If
Next
'Last selected
For i = ListBox1.ListCount - 1 To 1 Step -1..............................
View 4 Replies
View Related
Jul 8, 2014
I'm creating a macro that will involve some form of if-else/case-switch, as well as a loop (probably), but I'm not too sure how to go about it.
Basically there are records in columns. Lets say Column A has numbers in it, for example
Cell A1 has "Three"
A2 has "Five"
A3 has "Two" all without quotes
I basically want a macro that will loop through the whole column, so if A1 contains "Three" (not case sensitive), then set B1 to "3" without quotes. If A2 contains "Two", then the macro will set B2 to "2" without quotes.
Of course this is sample data but you get the jist. It needs to loop through one column, so as to fill in the other column. You can assume that column A will always be filled, so the macro is being created to automatically fill in column B based on what is in column A.
View 1 Replies
View Related
Feb 8, 2014
I have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...
First I was using a msgbox function to get the info:
HTML Code:Â
For Each b In myrange
If Application.IsNA(b.Value) Then
Employee = b.Offset(0, -2).Value
SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found")
b.Value = SSID
End If
Next b
But it can be up to 30 different new employees... and that is time consuming.
I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....
HTML Code:Â
Private Sub CloseButton_Click()
Unload UserForm1
End Sub
Private Sub ComboBox1_Change()
[Code] ......
View 2 Replies
View Related
Dec 23, 2013
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 Related
Oct 27, 2009
I have attached the relevant spreadsheet for which I need to alter the color of the columns based on Site number ( Sheet 1). % Mortality will be represented in the Y-Axis, and the Site numbers would be on the X-Axis. All columns (% Mortality) except one will be of the same color, and the one of a different color will indicate a specific site. As an example, site 86 is colored differently. The way I require the chart to look is shown on Sheet 1.
After reading through some great posts on Ozgrid, I managed to do this using conditional formatting (Sheet 2), but that sort of falls short because I am required to add a data table to the chart, and the parameter that is indicated by the column bars happens to appear twice in the data table.
I was wondering if this can be automated maybe using VBA, but with the possibility of simply matching the color of columns with the font color of respective entry in the data series.
View 9 Replies
View Related
Jan 23, 2008
I was going through a thread that is open now about a similar subject: Create Workbooks & Worksheets For Each Group In Table. I tried getting it to work myself but was unsuccessful. I want a code that will create a new workbook for every new Vendor. I tried doing it myself but was getting compile errors... If this is not what I should be using let me know, it works the way I want with just worksheets, i thought it would be an easy conversion to workbooks
Sub PagesByDescription()
Dim rRange As Range, rCell As Range
Dim wSheet As Worksheet
Dim wSheetStart As Worksheet
Dim strText As String
Set wSheetStart = ActiveSheet
wSheetStart.AutoFilterMode = False
'Set a range variable to the correct item column
Set rRange = Range("A1", Range("A65536").End(xlUp))
'Delete any sheet called "UniqueList"
'Turn off run time errors & delete alert
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("UniqueList").Delete
'Add a sheet called "UniqueList"
Worksheets.Add().Name = "UniqueList".....................
View 3 Replies
View Related
Jul 31, 2013
I have a worksheet with the following columns:
Brand
UserName
FirstName
LastName
Email
[Code]....
This is a relatively long list - 1000's. What I'd like to do is create a macro that sorts by brand, and at every change in Brand, copy the user details (Username, firstname, lastname, email) to another workbook with those labels at the top of the table. Upon completion, it saves the workbook with the name of the appropriate brand.
E.g. I'd have 3 workbooks:
- BrandA.xlsx - with the 2 user listed
- BrandB.xlsx - with the 1 user listed
- BrandC.xlsx - with the 1 user listed
View 3 Replies
View Related
Sep 15, 2009
I’ve created a spreadsheet to record all transactions ( Sheet 1) at a train auction for members only (Sheet 2). Currently, the workbook I created will ‘manually’ create receipts for any buyers/sellers by filling in Member's name in Sheet 1 and then selecting ‘View ‘Member’ Receipt’ button.
Ideally, I would like a macro that will automatically generate a worksheet for any member that has sold or bought an item at the auction and insert worksheets between ‘Start’ & ‘End’ tabs (with option to refresh data) in location order (A-D). Because members come from near and far we would like to generate the members that need to travel the farthest first. Is it possible to create a receipt for only the members that have transactions?
View 4 Replies
View Related
Dec 19, 2006
I am trying to seperate singlr column information into seperate column based on the name in the Data 1 column.
Ex.
Data1 Data2 Result 1 Result 2 Result 3
one100one100two200three1
one350one350two450three2
one500one500two600three4
two200
two450
two600
three165
three236
three450
View 3 Replies
View Related
Apr 10, 2008
I have a workbook that lists system analysts and information on the systems they're responsible for. I would like to have an Excel macro that will:
1. Create and name a new worksheet for each UNIQUE value in the 'Name' column (new worksheet for each analyst)
2. Copy their system info to each respective worksheet
I've attached a sample workbook.
View 2 Replies
View Related
Jun 28, 2010
I have a data sheet with 20 columns and about 300+ rows. In the results sheet there are 3 criterias (from column A, B, H in datasheet) which are in the form of dropdown in cell A1, A2, A3.
I would like to extract the data from the data sheet based on the criterias selected from dropdown list, to results sheet starting from row 6, with only 12 headers out of 20 (non-continuous, e.g. columns A-C, E, G-K etc.)
View 3 Replies
View Related
Dec 19, 2012
generating a formula that takes into account a range of values (an entire row) and from this row, I would like the formula to select, for example anything greater than 80%. After the formula selects anything greater than 8, I would like for it to select cells that are above or below the cells that have values greater than 8.
1
2 JLKNSTTP
3 85934942
4
5
For example, in the above datas, I would like a formula to select anything greater than 8 in row three and select cells above it. In this example it would be j, k, and t.
=if((3:3>8),offset(A3,-1,1,1))
View 1 Replies
View Related
Jul 16, 2014
I need to populate data based on criteria.
The criteria has been attached as follows.
Sample Test.xlsx
View 2 Replies
View Related
Feb 16, 2009
I need a data validation to prevent entries when they are > 50, but only if the value of another cell in col A is "Payment". The value of 50 is an example, I have another formula to get this number using VLookup.
The idea is that if the value of the cell in col A is "Income", I don't want the entry to be rejected. But if the value in col A is "Payment" or "Transfer" and > 50, I want the entry to be rejected (if I only have $50, then the payment cannot be > 50).
View 7 Replies
View Related
Feb 18, 2012
I have a problem and I hope that it can be solved using a macro. Basically, I have a set of data in column A and B in sheet(1):
A
B
1NameDate
2JOY12/09/11
3MAR12/12/11
4DAVID12/12/11
5
I have another set of data in Sheet(2):
A
B
C
D
E
1NameDatesalary
CHARACTERRATINGS
[code].....
basically, I would take into consideration the names and dates in sheet(1) and if it matches sheet(2) then it would then paste their salaries, characters and ratings into sheet(1).
View 9 Replies
View Related
Aug 17, 2012
I have a data which i need to total based on document types.
Document types in cells are AB=CELL A1, ZR=CELLL A2, ZP=CELL A3,
SK= CELL A4.And amounts in rows 555=CELL A1, 625 = CELL A2,
725= CELL A3, 925=CELL A4 and so on.
My challenge is to get all the total of ZR,SK,AB type of documents using macro.Initially i used this formula, however now document types are more than one.
=SUMIF([Book1]Sheet1!$C$2:$D$4,"zr",[Book1]Sheet1!$D$2:$D$4)
View 6 Replies
View Related
Nov 14, 2012
i have a sheet that i have been creating to pull information based on two things. The criteria is an emplyee # and the second is a date.
The data needs to be organized onto Sheet4. The Employee # will already be on Sheet4, so i have been trying to use that with Vlookup / hlookup with no success as i can't seem to get it to find the correct Date and place the data correctly.
Sheet4 layout:
Completed (with respective data from Sheet2)
A
B
C
D
E
1
[code]....
the data is located in Sheet2 (Emplyoee # is always in Column A, and the Date is always in Column B).
the data needs to be pulled into Sheet4. Essentially making the horizontal data of Sheet2, Vertical on Sheet4 under the respective date column.
Sheet2 Layout:
A
B
C
D
E
F
G
H
I
J
[code].....
View 3 Replies
View Related
Aug 29, 2013
I have data in my worksheet as follows
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
[Code]....
I want to create a sum of all values in column B (Data Size) that correspond to the same Dept-div code in column A
Ex: 0667 the total should equal 43268.8 for the two cells.
I need to count all cells in these columns versus a specific range?
View 2 Replies
View Related
Nov 30, 2006
I have a raw database that contains various data all in one column and I only want to extract certain data to a new column. All I want is to extract the time, like 14:00, 14:15, 14:30, etc...... from column "A" and populate column "D" if it meets this criteria.
I have tried everything I can think of, very difficult yet it seems to be so simple.
The "date" is really a date format but the "time" is really text.
Here is what the data looks like:
Column A has....
Name1:
Name2:
Name3:
10/16/2006
14:00
14:15
14:30
14:45
15:00
10/27/2006
16:30
16:45
17:00
View 9 Replies
View Related
Aug 11, 2008
I have a spreadsheet that has Leads in column H for eg Advertisements and Presentation dates in column K
I need to set up a formula that will count the number of dates (Items) in column K that is applicable to the item in column H for eg Advertisements, Referrals etc . There can also be blank items in column K which can be ignored
View 9 Replies
View Related
Aug 27, 2006
I've looked at the various examples in a search of topics simillar to my topic but I still can not get thsi to work.
I have a work book that contains 2 sheets
Sheet1 is called RawData of Multiple columns
Sheet2 is a quick report basded on Sheet1 data.
I need to find a way to do counts where 2 criteria are met.
Example.
Count the number of Times the phrase Major Victory appears in column M
( Range M1 to M50 ) of Sheet1 IF the value in Column L ( Range L1 to L50 ) of Sheet1 = USA
I tried this:
=COUNTIF(RawData!M1:M50,B1( AND(RawData!L1:L50=$A1)))
I am using B1 and A1 ( from Sheet2 ) as search criteria
but it gave me 0 ( and I know this is wrong ).
Based on the reading I did on simillar topics it appears that I cannot use CountIF in this case.
View 9 Replies
View Related
Dec 12, 2006
I have a sales workbook, with three sheets. Ongoing, Won, Lost, all potential sales are entered onto the Ongoing sheet and Ongoing is chosen in a column that has 3 options - Won, Lost, Ongoing, until brought to closure by winning the sale or losing it at which point Ongoing is changed to Won or Lost.
The products for sale are Audio, Video and System and at the top of the Ongoing sheet it totals those three categories as Won, Lost or Ongoing.
What I want the workbook to do is when the status of the sale is changed from Ongoing to either Won or Lost, the entire row is moved off the Ongoing sheet and placed on the appropriate Won or Lost sheet, and the totals at the top of the Ongoing sheet calculated accordingly.
One of the columns on the Ongoing sheet is 'Progress' in which a short abbreviated explanation is input of next steps of the sale. When the sale is closed, and hopefully automaticall moved to it's appropriate sheet, I'd like this text to be changed to either Won or Lost.
View 4 Replies
View Related
Apr 8, 2012
copy the excel sheet data from one sheet to another sheet?
-> I have one excel sheet (name: Test.xls, sheet name: SHEET1)
Sn Code Type next calib
5BPR CORR7-Apr-12
4BPR CORR7-Apr-12
73BPR CORR7-Apr-12
9BRG CORR8-Apr-12
10BRG CORR8-Apr-12
11BRG CORR8-Apr-12
17BRG CORR9-Apr-12
311DP CORR9-Apr-12
227DP CORR9-Apr-12
227DP CORI R9-Apr-12
Want to create a new work sheet and copy the today's date(next calib - filed name) records to new sheet.
Example: Today's Date is 8-Apr-12
So, I want to copy following record to new work sheet (when I click the button / run the macro).
Sn Code Type next calib
9BRG CORR8-Apr-12
10BRG CORR8-Apr-12
11BRG CORR8-Apr-12
View 3 Replies
View Related
Jul 26, 2013
I am trying to create a sheet which will automatically calculate data based on laid down criteria.The criteria are as follows:
Cab Type in cell () Indica
If the value of cell () is less than or equal to 40Km then cell (B3) will be 400.
For every extra km above 40 the cell value in cell (B2) will be extra km done multiplied by 8 in cell (D2)
If the value of cell () is greater than 40 but less than or equal to 80 then cell (B3) will be 800
For every extra km above 80 the cell value in cell () will be extra km done multiplied by 12 in cell (D2)
Cab Type in cell () Indego
If the value of cell () is less than or equal to 40Km then cell (B3) will be 600.
For every extra km above 40 the cell value in cell (B2) will be extra km done multiplied by 14 in cell (D2)
If the value of cell () is greater than 40 but less than or equal to 80 then cell (B2) will be 1200
For every extra km above 80 the cell value in cell (B2) will be extra km done multiplied by 16 in cell (D2)
Ther are about 5 different types of cabs in the sheet.
Cab Type Provided
Total KM run
Basic Amount
Extra km
What I want to achieve is as under:
1. If value of cell A1 = Indica and value of cell B2 is entered as 40
Then the values of cells (C2:e2) should populate automatically as 400, 0, 400
2. If value of cell A3 = Indica and value of cell B3 is entered as 40
Then the values of cells (C3:e3) should populate automatically as 400, 40, 440
View 8 Replies
View Related
Oct 22, 2013
Attached is my .xls with dummy data Fetch data By Director.xlsx.
My intention is that once the Director and Month in tab 'By Director & Month' is chosen and the Fetch Data commandbutton is clicked, a new workbook should open.
This new work book should have :
all entries in tab 'Monthly Invoice Input' for that Director-Month combination,along with a sum total of column Q(Total invoice amount) in the last row.
View 6 Replies
View Related
Jan 24, 2014
I have a workbook with several different worksheets which I am copying from the individual sheets and pasting into a summary sheet, and multiplying the numeric numbers by minus 1 to reverse the signs.
This is all working fine, but I now want to copy an additional worksheet called "Plan" and paste this into the same summary worksheet, however I do not want to reverse the signs for this data.
All of the other worksheets begin with "Total" in the worksheet name, and this is how I am specifying which sheets to copy. I can re-name the "Plan" sheet to "Total Plan" if that makes the logic easier, the import thing is that the signs are not reverse for this sheet.
Column Z of the summary worksheet "RDBMergeSheet" identifies which sheet the data was originally copied from, so could I somehow use this to say if column Z = Plan, skip the multiplication by minus 1?
My current code is this:
VB:
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
[Code]......
View 3 Replies
View Related
Apr 5, 2014
show/display data based on month criteria, with selected month in cell H2 (as start date) and I2 (as end date) my expected result start from cell H7, i called "blue area"..
see my attached workbook..
View 9 Replies
View Related
