# Concatenated Range Names In Graphing Vba

Jun 21, 2007
I have a lot of seperate data that I'm trying to graph via a macro. I have one macro with a Do Until/Do While loops that runs before I import the data that creates and seperates the data into dynamic named ranges. My goal was to create a macro that graphs the different ranges using another Do Until/Do While loop. You'll see below that I have the ranges concatenated inside the loop so it does just that, graphs each set of data in each worksheet by running the same macro. Below is the code for the specific graphing macro and at the same spot, where the series values are set using the concatenated range name, the error message, "Unable to set the Values property of the series class." I have no idea what is wrong, but my guess is it's probably something simple.

Sub Graph()

Dim Counter As Integer

Dim CounterEQ As Integer

Dim Title As String

Counter = 200

Do Until Worksheets("IndustryEmployment").Cells(1, Head) <> ""

Counter = Counter - 1

Loop

CounterEQ = Counter

Do While Worksheets("IndustryEmployment").Cells(1, Explode) <> ""

GoingTo = Worksheets("IndustryEmployment").Cells(1, Explode)

Charts.Add

ActiveChart.ChartType = xlLine

ActiveChart.SetSourceData Source:=Sheets("IndustryEmployment").Range( _

"U15:U16"), PlotBy:=xlColumns.......................

View 3 Replies
ADVERTISEMENT
Apr 2, 2009

I want to create a series of VLOOKUP commands with different lookup tables whose names are concatenated from two different pieces. Here's what I'm trying to do:

VLOOKUP(x,LUTWK01,y,false)

VLOOKUP(x,LUTWK02,y,false)

VLOOKUP(x,LUTWK03,y,false)

LUTWK01, etc., are named ranges. I have the text "WK01", "WK02", "WK03" stored in other places, and would like to be able to concatenate "LUT" and the particular week (e.g., "WK01"). The formula that I've created, which isn't working is:

VLOOKUP(x,CONCATENATE("LUT",A1),y, false) where cell A1=WK01.

When I pull out just the CONCATENATE portion, it resolves to LUTWK01, but apparently this is not recognized as a range name. Any idea as to how I make the VLOOKUP formula recognize a concatenated range name? Or is there a better way to do this?

View 2 Replies
View Related
May 2, 2012

How can I countif on a row/range

I tried this but this wont work. All i want to countif there are duplicate rows. This formula will be copied down

=countif($A$1:$A$11&$B$1:$B$11&$C$1:$C$11,A1&B1&C1)

View 3 Replies
View Related
Jul 2, 2012

I have a range of cells each containing a name. Based on a number that has to be entered manually I want excel to return the names concatenated in one cell. So for example:

Number of variable entered: 5

q9001

q9002

q9003

q9004

q9005

q9006

etc.

Should give me: "q9001 q9002 q9003 q9004 q9005"

I have been trying to work with formulas using IF and CONCAT functions. But so far I haven't figured out how to have excel return me the correct amount of variables for each separate number that can be entered seeing the number of variables entered can vary from 1 up to 50.

(Using Excel 2010)

View 7 Replies
View Related
Feb 11, 2014

I am creating a new sheet that is gathering the data points from the master sheet. These data points are in the same row, but are in every SECOND (=every other) column.

I am struggling with the =INDEX function, which I presume is the best way to gather these data points. I'll then graph this gathered data.

View 3 Replies
View Related
Jul 7, 2007

I have inherited an Excel workbook in which the formlas all contain cell names (and there are thousands of names in this book). I need to find a way to change from using cell names in a formula back to a standard absolute cell reference but have no idea how to do this?

View 9 Replies
View Related
Jun 20, 2014

I am trying to graph dimension measurements that are automatically added as a new worksheet upon completion of a CMM program. Do I need to move all of my data to one work sheet to be able to graph it or is it possible to call the same cell on multiple sheets? Also is there a way to set the graph up so that it updates the information every time a new sheet is added? I have a sample workbook attached that only list one dimension the actual workbook will have many dimensions listed. Sheet 1 is blank and sheets 2-4 contain the information from the CMM.

View 1 Replies
View Related
Jan 28, 2013

I have a spread sheet that we input data on and produce a simple graph. When I started we plotted these graphs by hand on chart paper. When we started using spread sheets I learned how to graph the problem is these guys we have working in the field don't know how or care to learn how to make a simple xy graph.

Six months ago I started automating a workbook so our field guys could just plug in numbers and have the needed graph, I put check boxes to select the points to calculate the slope and intercept. I hard coded to plot to say line 21 then at the end of the test we always have a zero point that we don't plot so I put that on line 22 then have a macro to hide everything before I print it.

I think I would like to dynamically select the data for graphing but I'm not sure about the best way to do this. My first thought is to key on the x axis column with a loop and graph data from zero until the x looses value.

View 9 Replies
View Related
Dec 1, 2006

I have a graph / time problem

I have a program that is spitting out raw data to me that I would like to be able to put into a graph form in a efficient manner, but I’m not sure the best way to accomplish this.

Here is what I’m working with:

I am getting a list of time date stamps that appear as follows.

11/30/2006 6:02:25 AM

11/30/2006 6:02:36 AM

11/30/2006 6:02:40 AM and so forth.

Each stamp represents a hit to a link.

What I would like to be able to do is graph the number of hits per hour verse the time of day hit. Showing the time on the x & the number of hits per that hour on the y axis.

I can convert the time to decimal, but I still need to find a way to get excel to graph the sum of hits per hour.

View 9 Replies
View Related
Mar 19, 2009

I was wondering what the best way to create a standard deviation graph was? I have a single row of numbers and need to make a stadard deviation graph.

I read somewhere that the analysis toolpak would be useful for this but having installed it i'm not really sure what to do next.

View 7 Replies
View Related
Dec 12, 2009

If I graph a straight line and on several points on this line I want to graph rectangles, how would I calculate the corners of these rectangles.

The rectangles will not always stand 90 degrees on the line. This difference from 90 degrees is in cell L6 The direction of the line is known and the sides of each rectangle is known as well as the center of the rectangle (in meters N or S and E or W). Currently I have the direction of the line in cell L5, the long side of the rectangle in cell L3 and the short side of the rectangle in cell L4. The centers of the rectangles are in rows 1 and 2 of columns M, N, O etc. For instance cell

M1 = 450 (N) and cell M2 = 400 (E). The corners are to be calculated in reference to the values in these cells.

I have been calculating it long hand with a calculator but that takes me a long time and they tend to change things quite regularly. I have to incorporate this with macros that draw the line sition but I have that. Going clockwise from the right top around they would be called corner A, B, C and D.

View 9 Replies
View Related
Mar 26, 2008

I have a workbook with Sheet1 and Sheet2. I’ve programmed information from to be calculated and results inserted into sheet2. A new result every Row. I plan to do a graph taking the X-axis and Y-axis values from Columns A and B respectively in Sheet2. What is the VBA code for dynamic updating of graph? Meaning, I want the graph (a curve) that will have points added to the curve, extending it, whenever a new row is added into sheet2 – Columns A (X-axis) and B (Y-axis). The rows are not defined. It can have 100 to 200 rows or more..

View 6 Replies
View Related
Apr 15, 2009

I was hoping that someone might be able to help me with a current problem that I am facing in my work center. Periodically we have personnel check-in on given day and then later check-out on a specified day. Is it possible to graph/predict/forecast our personnel losses in our department using a chart in excel? Or at least be able to know how many people we will be losing on any week...?

I have attached a sample worksheet of data with columns labeled Name, Gains, Losses.

Columns explained:

1) Name: Name of Incoming Personnel

2) Gains: Date that Personnel Arrived/Checked In

3) Losses: Date that Personnel will Check out

View 7 Replies
View Related
Jul 2, 2008

I have researched and read multiple threads related to graphing rolling averages per month. I've setup my own but I cannot get it to graph correctly. The x axis will be the days of the month ie. 1-31. The y axis is obviously the sales volume. The legend should be a line graph for each individual month sales. I want the graph to reflect the current month and previous 12 full months.

I used the file user rockycj started for another user in a different thread labeled, "Excel Line Chart - 12 month chart moving with dates ". It was very helpful and I'm trying to get this completed before my deadline. If anybody could take a look and let me know, I would greatly appreciate the help. I have attached the file for reference.

View 9 Replies
View Related
Jul 23, 2012

I'm having some problems with graphing 'blank' cells for my thesis work.

I am filling in data still as I go, and I'm trying to set up my graphs as well so I can easily go in later and modify, fill in more data, or whatever. But all the cells I've formatted as coming back blank are labelled as zeros on the Excel graph. I tried putting in to return #N/A but then Excel is still graphing the cells as zeros. Frustrating. I really do not want to have to recopy over 1000 data points, find the zeros, and cut them out later, I'll have enough to do writing the dang thing. How to modify the formula to have it correctly skip the points when graphing (it's a line graph)?

This is the current formula I'm using. =IFERROR(AVERAGE(C41:Y41), "")

And I put in =IFERROR(AVERAGE(C41:Y41), "#N/A"), which shows the cell as #N/A (which, I'd much rather it be blank, as then I can read the data easier), but it is still graphing as 0.

View 4 Replies
View Related
Nov 20, 2008

I have got a table of results with wards along the columns and in the rows reading left to right weekly dates e.g. 5/10/08 12/10/08 19/10/08 etc.

How can I get a graph to change the data it displays from a combo box eg. A1 is selected in the combo box and it shows a line graph of the Data for A1 over the weeks? There are 50 wards

View 9 Replies
View Related
Feb 13, 2010

I have sheet that is populated with data daily. I wish to create graphs from information contained within a table in this sheet where i can select the start date and end date of the graph possibly from a drop down menu. I will be creating multiple graphs and wish for them all to change when i select the time period.

View 9 Replies
View Related
Jul 31, 2014

I have a column who's values I would like to graph against many other columns. I was wondering if there was something that would let me do that in one go.

View 1 Replies
View Related
Mar 7, 2014

I am attempting to graph data based on work down in specific work centers to track efficiency. I can download all items that were finished for a certain time frame (yesterday, last month, last year, etc.), but the data only shows up as the item, quantity, and the date of closure. I take that info and copy and paste it to the close out tab of the other work book, where it references another sheet to pull in the time required to complete the task, and the work center that did the work. My problem is when an item has multiple work centers on the same item and then taking that info into a pivot table to graph. This can be done manually, but I was hoping I could create a way to only have to copy and paste the data and have it return the work done.

I have included a shortened version of what I am trying to do, eventually there will be 25000+ items on the data tab, but if I can get this to work, then i assume I can extrapolate.

View 1 Replies
View Related
Feb 10, 2009

I need to graph a function of SIN(X), I did it by writing my x-values in one column with small steps between each value and then calculating the sin(x) value in the column next to it and then graphing the two columns. But this raquires a lot of space on the spreadsheat and it would be convenient to be able to write it on the form y=sin(x). having x defined as a continuous variable. and then plotting the graph

View 2 Replies
View Related
Dec 5, 2011

I am trying to graph data from an Array of sheets. I keep getting many errors... I tried using Cases too but this failed, it graphed the first Sheet but nothing else.....

Before Array Works! (Uses Cases)

Code:

Sub GraphTest4()

Dim LastRowGraph1 As Long

Dim ws As Worksheet

For Each ws In Worksheets

Select Case ws.Name

[Code] .........

View 8 Replies
View Related
Dec 10, 2012

How do I graph this data? I am plotting the test results (Column A is the employee number, B is the Employee name, then each of the questions are Columns D through DX with DY being the total correct by each employee) (Rows are employees and then the Ratio that the question was answered correctly) The goal is to find questions that are routinely answered in correct or that have a problem with the question.

I was hoping to graph the correct and incorrect questions so those that are either high frequency correct, incorrect or not answerd identified I can improve the test.

Example of the spreadsheet. I am using =COUNTIF(D3:D15,"1")/12 for the ratio answered correct - this should also see the ones not answered as incorrect. 1 = correct answer 0 = incorrcet null=not answered

NO

Employee Name

Question

1

2

3

[Code]...

How can I graph these results to answer which questions have a high frequency of incorrect answers? I am thinking some kind of scatter plot.

View 5 Replies
View Related
Jan 31, 2013

struggling with the syntax of this one. Lets say we have a variable called MYWEEK with a value of WEEK 9 and cells A1 and A2 have values of 9 and 10 respectively. I need to be able to compare the variable against cells A1 or A2 but with a prefix of WEEK .

I've tried the following:

if MYWEEK = "WEEK " & A1 then ...

if MYWEEK = "WEEK " + A1 then ...

if MYWEEK = ("WEEK " & A1) then ...

I know the answer is staring me in the face but I cant see it. Any use of the ampersand presumably invalidates the IF statement by introducing multiple conditions rather than a concatenated value. How do you compare a value against a concatenated set of criteria in VBA?

View 2 Replies
View Related
Oct 30, 2007

I have a TextBox that I want to show the concatenated value of two cells. Is that possible? Or do I need to concatenate it on the sheet and then have the TextBox = that cell?

View 9 Replies
View Related
Aug 14, 2014

I'm simply trying to graph my data on a line graph in Excel. see the attached.

The issue is that the spacing on the x-axis is all equivalent. I want the space between the initial and 1st month to be very small while the space between 6 month and 12 month be larger.

So at the end of the day, the line will be kind of crunched down from the beginning and goes up at the 6 month data point.

Is Excel capable of handling such simple request?

View 3 Replies
View Related
Oct 4, 2008

How can I graph merged cells, without graphing gaps or spaces of the skipped cells?

View 12 Replies
View Related
Mar 6, 2009

I have the theoretical set:

year $ %

2003 100 10

2004 120 20

2005 145 30

2006 205 40

2007 300 50

Could I make the year the X-axis and make 2 y-axes on the same graph in excel that each datasets would be correlated to (eg. for money it'd be from 0 to 300 while for percent it'd be from 0 to 50)

View 3 Replies
View Related
Jan 14, 2014

I'm recording a macro, so a line in my graph is a specific green. The marker fill won't retain the green, it always ends up blue. the lines and marker line is green. no matter what color I try the marker fill will always be blue. How to correct this?

View 6 Replies
View Related
Apr 18, 2006

For an economics assignment I would like to graph price elasticity with price on the 'y' axis and units on 'x'. I have not found a way to do this in Excel, can someone assist?

View 6 Replies
View Related
May 13, 2009

Effectively I need to concatenate two columns in Sheet1, and then vlookup each concatenated value from Sheet1 in another concatenated column in Sheet2 and return the value from the next column

Now that is quite straight forward, but I am new to coding, and I was wondering if there was a way of doing it that doesn't involve having the concatenated values written to a column as a formula. Ideally what I would like it to do is put the concatenated value of Sheet1 together as a variable (I think thats what you call them) and then look up that variable in Sheet2 without Sheet2 creating a concatenated column too.

I'm of the understanding that this might be slower than writing the columns, but there is a reason for my madness.

Let me know what you think, or if i'm barking. Any suggested reading would also be handy, I don't like to bug you guys for the full code straight out.

View 10 Replies
View Related