I have about 30 sheets with 500+ rows of data in each, with each row of data in the sheet needing to be plotted individually against the same y values. Is there a way to automate this with a macro? I have recorded a macro for an individual graph but don't know how to tell it to loop and go to the next row of data for the x values while keeping the y values constant.
View 3 RepliesI am attempting to have cells in Column 'U' deliver different drop-down menus based on the corresponding value in column 'D'. I have created 7 named lists:
List_117G
List_152
List_JMET
List_XBAND
List_PACWIND
List_VORTEX
List_ROVER
Those lists will be called up based on 7 values in column “D”:
“G”
“152”
“J”
“X”
“D/E”
“V”
“R”
So far I have only been able to get this to work for the first category “G”. When I change the value of column “D” from “G” to “152” I no longer get a drop-down. Here is the formula I am using in the List function of validation.
=IF(D6="G",List_117G,IF(D6="152",List_152,IF(D6="J",List_JMET,IF(D6="X",List_XBAND,
IF(D6="D/E",List_PACWIND,IF(D6="V",List_VORTEX,IF(D6="R",List_ROVER,)))))))
I am trying to replace a range of cells with certain values but I can not figure it out. I'm almost there I think but don't know how to get the varying values I'm looking for. So I have a column (AJ in this instance) that has a bunch of "xx" values at different spots within the column. I want to replace those xx values with numbers 01 through 36. What I have below gets me just about there but it replaces every xx value with 01. How do I get it to go 01 on the first one, 02 on the second one, etc?
/code
Dim cell As Range
For Each cell In Range("AJ1", Cells(Rows.Count, "AJ").End(xlUp))
If cell.Value = "xx" Then _
cell.Value = "01"
Next cell
/code
Plotting of bar and line graphs given an arrays of x and y values in VBA? I want to plot the rDateRange and rOEERange.
Code:
Dim rInitialDate As Range, rFinalDate As Range, rDateRange As Range, Calculation As Worksheet
With Worksheets("Calculation")
Set rInitialDate = .UsedRange.Find(What:=DateValue(InitialDate), After:=.Cells(1, 4), LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=xlNext, SearchFormat:=False)
[Code] .........
i have attached a sheet where I have 4 graphs on one sheet. I prefer this to an overlay. I would like to add a 5th graph but it would make all the the graphs too small. Is there a way to make the graph background longer. For instance, extend the length of this so that I can put multiple graphs on it and then just scroll from top to bottom to review them? Also, how do I copy and paste these individual graphs into a newly created graph. copy/paste doesn't work on these?
Boiler.xls
I am trying to look & match key values from 2 areas of one table with two areas of another table; in turn, it'd return one value based on the lookup table...
Attached worksheet : Test booklet.xlsx
I have attached sample sheet.
I need to populate the Rep Names looking up 3 Criteria (Client Id,Curr Cov Id,Dom Buy Grp Id) from the Table 1, either one matches the Rep Names has to populate.
The data has to be pasted on WIP_Sheet in different columns.
I am working on creating a spreadsheet that can be updated by those unfamiliar to excel. I have a master list on one sheet and three separate lists for business, individual, and general. Each list has three columns giving name, address and postal code. There are numerous repeats on these lists and I wanted to hide any duplicates while keeping my cells aligned. So far I have been able to highlight any duplicates, but was interested in some function that would save at least one duplicate while hiding any additional ones.
View 2 Replies View RelatedI want to build a database where the user can select a specific time period. I have a static file with the time periods and the related values. For instance I have in column A1:A15 the time periods from January 2011 to Dezember 2011, but I have some dates multiple times as there are more values attached to it. For instance I have in the first 4 rows January 2011...what I want is when someone puts in the value January 2011 in cell B1 and Dezember 2011 in cell B2 that the whole static file gets copied to another location (including the multiple dates) displaying the chosen time period. Similarly if someone puts in the value March 2011 to November 2011, I want only those values to be copied.
View 4 Replies View Relatedtwo formulas for one data set. The data is attached in the spreadsheet: "Product IDs". The data is a set of Master Product IDs (parent) and the Linked to them Products (children). I need to create a relationship between unique parents (Master Product IDs) and their children (Linked Products)
I need to create two formulas:
1. From the Data Set table, need to vlookup the unique value in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). So, the result will be as shown in Table 2.
2. From the Data Set table, need to vlookup the unique (de-duplicated) parent/children relationship in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). There are total 3 parent/children relationships in Table 1. So, the result will be as shown in Table 3.
In sheet1, I have the borrowers and outstanding per employee. In the Area Avg sheet, I have the average borrowers and outstanding in each area as this is the standard for comparison (I got this through the subtotal function).
Now what I have to do is in sheet1, format(change the color) each cell under borrowers which is greater than the average of the relevant area. Since, (in my actual data) the name of the area may be duplicate in different regions, the reference value (average borrowers of the area) from "Area Avg" sheet has to be traced through multiple criteria which are: Division, Region and Area. The same also has to be done for outstanding but the solution can be provided for borrowers only.
I have a bill of materials with a description column. I want to search that column for various words (ie. wheel, screw, spacer, shelf, etc) and return a value into another new column depending on that value (wheel inputs wheel, screw inputs hardware, spacer inputs hardware, shelf inputs shelf).
How Excel shows you how to search will only return one value because I can't use an else statement:
I have a spreadsheet that has about 50 sheets and a summary sheet at the front the layout of the summary sheet is column A has skill sets from A4 to A20 and across the top it has week commencing dates from F3 to about BA3 (dates go from w/c 06/05/13 to 08/12/14). On the other sheets they have the dates in the same columns but the skill sets vary as to each project, some projects may have two of the same skill set in column A because there is two people needed who can do the same thing, I need to total up the days over the sheets for each week and skill set.
I have been using the SUMIF formula as follows:
=SUMIF('Sheet2'!A8:A10,'Summary'!A4,'Sheet2'!F8:F10)+SUMIF('Sheet3'!A8:A15,'Summary'!A4,'Sheet3'!F8:F15)
And so on for each sheet and then change the formula for the next skill set and then again for the next column.
Is there a way of doing this without having to enter a formula into each cell, at the moment I am coping and pasting the formula into word to do a find and replace to change the SummaryA4 to SummaryA5 etc and then into the next cell for the corresponding date.
So to make this seem like it makes any sense I am looking for say an 'Administrator' in all the project sheets over all the dates and for it to add it up for the summary sheet. Under the corresponding week against the skill set.
So it would be F4:F20 for w/c 06/05/13, G4:G20 for w/c 13/05/13 and so on until all skill sets.
There are 11,000 rows and 4,000 are unique. The goal is to merge the data down to the 4,000 records. Each of the unique entries shows up 1 to 15 times.
In trying to solve this, I wonder if I should break this down into the different # of occurances and implement specific solutions. e.g., There are
5700 entries that show up 2x
504 that show up 3x
24 that show up 12x
I want to call a macro with a varying name that is within a module with the same name.
I have a module called Test1 and within this, a macro name called test1
I have a module called Test2 and within this, a macro name called test2
etc
On another module called Test8 (with the macro called Test8), this Test8 macro will call either Test1 or Test2 or Test3 etc depending on what I choose in an excel spreadsheet. So on sheet1, cell A1, there is a drop down with the options Test1 or Test2 or Test3 etc.
The following works to run the macro test1 from module test1 (when it does not vary i.e. i physically put in the name of the macro myself):
Sub Test3()
Test1.Test1
End Sub
The following works to run the macro test2 from module test2 (when it does not vary i.e. i physically put in the name of the macro myself):
Sub Test3()
Test2.Test2
End Sub
etc
However, if I try it so that the calling of the macro varies as below , it does not work:
Sub Test3()
MacroToCall = Sheets("Sheet1").Range("A1").Value
MacroToCall.MacroToCall
End Sub
I currently have a 70 page spreadsheet with similar data that needs representing in pie chart format. All the column headers are the same and I only need 2 columns (A and T).
There are however different numbers of rows. Is it possible to write a macro to detect the number of rows before it draws the relevant pie chart (I've managed the pie chart bit!!)
See attached example, same concept but cant post real data.
I am trying to copy a group of six graphs from one Sheet "Charts" and paste them in another Sheet "FinShots" in the same Workbook. I have grouped the graphs and named the group "FinShots1".
But I can't seem to write a code that will copy group "FinShots1" from "Charts" and paste in "FinShots".
I am currently working through data received from several experimental runs I have done for my masters studies (In chemical engineering). I need to create a chart for every experiment but coding the macro to do that has been near impossible. I have the file with the first graph drawn but I dont know how to post it.
I know it will be possible to draw every chart by hand but I am waiting on another 300+ data sets and then the amount becomes crazy.
I would like to create 2 graphs for each set of data in my worksheet. the first set of data that I would like to create a 2D line chart for is in cells AQ3:AS19 (not sure if that is the correct syntax??) and the second is AW3:AY3.
I would like to position the first chart (top right corner) at BA3 and re-size it so that it is 17 cells tall and 7 cells wide. the second chart should be positioned at BI3 and the same dimensions.
I would like to delete the chart legend for both graphs
then I need to repeat about 1000 times. Each new set of data starts 20 cells lower (ie. AQ3, AQ22, AQ41..). Or every time "Band" is found in column AP.
see attachment.
I am looking a macro that will create as many graphs as there are counties (column a) in the data range (A4:N89 for this example). There could be a maximum of ~3000 counties, which would place the data range at roughly excel's limit (A4:N65000).
In any case, the graphs would take data from column F and look like the 3 graphs i have provided in the attachment. The graphs are simply graphing share for the top 25 lenders in each county. So, for example, Texas contains 254 counties--I would like a macro that creates 254 graphs, one next to the other, starting at cell O4 through column IV4 and then repeating at O22 through column IV22 and so on, until all the graphs for each county in the data range are created.
Also, would it be possible, just as in the attachment, for the macro to change the color of the data bar associated with a particular lender, in this case JPMORGAN CHASE BANK? In the attachment, I made the three bars associated with JPMORGAN CHASE red. If there is a county in which this particular is not in the Top 25, then all of the bar colors in the graph remain blue.
Would it be possible to do the exact same (macro that graphs off the pivot table) but include the data for the Top 25 Totals, Non-Top 25 Totals, and the County Total?
In other words, the graphs would look exactly as they do now, but, for example, the first graph would contain data from F5:F32 rather than just from F5:F29. Furthermore, the graph's axis label would pull from C5:C29 AND B30:B31 AND A32.
I'm pretty much a novice at Excel so obviously my knowledge is fairly limited. Basically I have created 2 sheets called 'Week 1 - By Day' and 'Week 1 - By Campaign'. The first sheet (By Day) contains data which supplies graphs to not only that sheet but also to sheet 2 (By Campaign). Sheet 2 (By Campaign) only contains graphs sourced from data within sheet 1.
So, I need to copy both of these 52 times (for every week of the year). Thats 52 x sheet 1 and 52 x sheet 2.
I have been able to copy sheet 1 easily enough by holding down the Ctrl button and dragging the sheet. This works fine and the formula and graphs within the sheet change automatically. However, the major problem at the moment is sheet 2(sheet 2 only contains Graphs which is related to data from sheet 1). When I do the same thing with sheet 2 (hold down Ctrl and drag) the graphs within the new sheet (sheet 4) are still sourcing the data from sheet 1 instead of sourcing the data from Sheet 3 'Week 2 - By Day'.
I am trying to use VB to create 50 charts each with 3 lines using data from 3 different excel spreadsheets in the same workbook. I am able to create one chart with 3 lines using data from the three spreadsheets, this is good. However I exprience problems when i try to insert a loop to create 50 charts that correspond the the rows in each spreadsheet. I am new to VB and am very inexperienced with VB. In each worksheet my data is arranged in rows. row 1 of each worksheet has the header information and rows 2 through row 50 have my corresponding data. for example A2 lists the name and F2 through T2 lists the data. I have tried a bunch of different things but nothing seems to alllow me to be able to create multiple charts which correspond to each row of data?
Sub Chart2PPT()
Dim arow As Integer
Dim acol As Integer
Dim StartPoint As Integer
Dim EndPoint As Integer
Dim rStartPoint As String
Dim rEndPoint As String
StartPoint = 2
EndPoint = 4
For arow = 2 To 5
rStartPoint = "f" & StartPoint
rEndPoint = "t" & EndPoint
Charts.Add
ActiveChart.ChartArea.Select
ActiveChart.ChartType = xlLine 'Type of graph........................
I maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.
I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).
The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.
As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.
The log code in column "H" Is based on this criteria:
First Letter of the Unique ID in column "E" - O, M, or L
Program Type in Column "F" - U or R
1. O-U = U
2. O-R = RU
3. M-U = U2
4. M-R = R2U
5. L-R = R
You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.
This is the area where my skill at using scripting dictionaries fails.
The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words
if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")
So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)
Here is my code so far.
VB:
Option Explicit
Sub test()
Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long
Set dic = CreateObject("Scripting.Dictionary")
[Code].....
name data1 data2 total
chris a b 100
a a 100
a a 100
steve b b 90
b a 90
b a 91
jane b a 89
a a 90
a a 92
its hard to line things up in here..... anyway... each name has two more lines of data associated with it. i want to take the 4th column last line for each and sort from greatest to smallest and rearrange all the data but keeping all three lines associated with each person together. one added complication. the number of ppl in this will vary although i can do a count of every three lines and tell exactly how many there will be. so for this purpose we can just keep it at three.
in this case, all three lines associated with jane should be moved up above the three lines of steve but stay below the three lines of chris. and the three lines for each person stay in the same order they are now
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..........................
I have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?
View 1 Replies View RelatedI want my IF function to populate two cells with two values if argument for IF function is found TRUE. Is it possible?
IF(logical_test, [value_if_true], [value_if_false])
value_if_true = return multiple values in mutiple cells (for example put number 8 in cell A2 and number 10 in cell A3 if function is TRUE)
How would I do that? I tried putting IF(logical_test, (A2="8",A3="10"), [value_if_false]) but it is not possbile...
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
In my userform I have a list of check box's that can be selected. Currently if more than one is selected, they will appear in the spread sheet in the same line one after another with a space between them. How do I make it so they either appear with a comma appearing after each, so the next value appears on the next line below or most perferably the cell turning to a drop down list with the values?
Current code
VB:
If CheckBox1.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox1.Caption
If CheckBox2.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox2.Caption
If CheckBox3.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox3.Caption
If CheckBox4.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox4.Caption
If CheckBox5.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox5.Caption
If CheckBox6.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox6.Caption
Spreadsheet current cell appearence if all 6 are selected ' Chinnook EH101 Lynx Puma Sea King Fixed Wing'
Required appearence:
Chinnook
EH101
Lynx
Puma
Sea King
Fixed Wing
I am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not.
I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K.
I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.