• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

dynamic data formatting question.

M

MechEng

New Member
I have an excel spread sheet from which I must create graphs (XY scatter plots in this case)

I wish to create a macro to create the graphs as the data changes weekly and graphs of each set of data must be created.

Creating a macro to do this rather than having to manualy graph the data each week is not a problem.

My issue is that the amount of data in each column changes weekly, Column A is always the same parameter as is column B etc etc, however the length of the columns vary, this week all columns contain 22 entrys, this could be 50 or any other number next week.

Eg currently the macro will plot A1 to A22 against B1 to B22, if next weeks data sheet contains 50 values per column, the macro will plot A1 to A22 regardless and A23 to A50 will not appear on the graph.

I have attempted to use dynamic ranges do do this.

I've outlined my current method below, if you could spot where I'm going wrong or suggest an alternative method that would be a huge help.

Open worksheet of raw data, 1st 4 rows contain text, rows 5 to 26 contain numerical data.

Create 2 new sheets, name one graphs and one formatted data.

create macro / start recording.

insert scatter plot in "graphs" sheet, select data, plot A5:A26 on x axis against B5:B26 on y axis, format graph, axis lables etc

Paste raw data column by column into "formatted data" sheet under user friendly headings as headings in raw data sheet are obtained from a machine and its unclear what they represent.

stop recording and save.

test macro by adding data to each column so they span from A5:A50 instead of A5:A26 as before, this is scenario I need macro to deal with when completed, varying amounts of data processed in the same manor.

When I run the macro again with the additional data, the graph still only plots A5:A26 as before and the formatted data page contains only 1st 21 entries also.

I have tried seting the dynamic ranges both before and within the macro, neither case made a difference.

It is worth pointing out the data runs for 286 columns and is used to create 20 seperate graphs, I have only described A and B above for simplicity but I require the macro to work for the full volume of data.


Also the dates in the raw data shet are in the format 04-04-12-13_41 is there a way to convert all these entries to a better format, 04/April/12 13:41 or similar, and the data is recorded at 5 minute intervals and must be graphed as such, I have been writing a column of increments of 5 by using A1=0, A2=A1+5 and dragging this down to whatever length is needed and using this as the x axis for all the plots, this suffers the same problem however of not increasing when the amount of data increases.


Any help on any of these issues would be hugely appreciated!

Thanks for taking the time to read this!
 
Hi ,


This would be much easier if you could upload your workbook.


http://chandoo.org/forums/topic/posting-a-sample-workbook


You mentioned that you tried using dynamic named ranges ; what was the result ?


As far as changing the text values to dates is concerned , a formula such as this will do the job :


=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-"," ",3),"-","/"),"_",":"))


if A1 contains the text value.


Narayan
 
Hi MechEng,


Welcome to the forums!! Is the only problem you are facing is dynamic ranges? Well the lower limits for the graphs can be set with formula and i assume you will be plotting data on separate sheets for each week?


Regarding the format 04-04-12-13_41: Assuming that 04-04-12-13_41 is present in A1 enter following formula in B1

[pre]
Code: 
=MID(A1,1,FIND("_",$A$1)-4)&" "&TEXT(TIME(MID($A$1,FIND("_",$A$1)-2,2),MID($A$1,FIND("_",$A$1)+1,2),),"hh:mm AM/PM")
[/pre]
Regards,

Faseeh
 
Without seeing your data it is hard to visualise


I will suggest that the use of Named Formulas (Dynamic Formulas) will solve your problem


I recently answered a similar query for OleMiss2010 so have a look here: http://chandoo.org/forums/topic/two-questions-about-charts-within-vbas
 
https://rapidshare.com/files/3105214021/sample.xlsm

Thats the worksheet I'm dealing with, I start with the raw data on the 1st sheet and I need to create a macro to format the data into the form seen on the 3rd sheet "formated data" and then to create graphs from this data as shown on the nd sheet "graphs".

Creating a macro to automate this process is fine, mt issue is, this worksheet contains 1hr 45mins of data recorded at 5 minute intervals, the next file I get will likely contain 1 weeks worth of data recorded at 5 minute intervals, i.e. 2016 rows of data. I need to create a worksheet were I can copy and paste in the the data, whatever amount of it there may be and the worksheet will format it as shown.

I have attempted to use dynamic ranges for the graph but I either got errors or they had no effect so I'm clearly doing something wrong, If someone cold please help me with this it would be hugely appreciated!
 
Hi ,


Your file is protected , and I cannot help out ; if you can unprotect your workbook or post the password , I can try.


However , I am not clear on your requirements ; probably a clear description of what is to be done , with specific reference to cells / columns / rows / ... will help , since once the workbook is openly available , it'll be easier to follow.


Narayan
 
Sorry about that, sorry if I was unclear, I'm trying to do is convert the raw data seen on sheet one into the graphs and formatted data seen in sheets 2 and 3. I need to automate this process via a macro, vba code or similar so that I can open the worksheet once a week paste in the raw data and have the graphs and formatted data sheets represent the new data. The amount of data varies weekly anywhere form 20 to 2000 rows of data is possible, it will be in the same format only the amount will change.

I'm not sure why you need to unprotect the worksheet as you should be able to see what I'm trying to do by looking at the sheets and if necessary you can still copy the data and paste into an unprotected worksheet to play around with it.

I greatly appreciate you helping me with this and will facilitate you however I can, if you still need the password for the sheets I would be happy to provide it.

Thank you so much for your help on this.
 
I'd add a Data Import Sheet where you will clear and paste new data in each week

Then use Named Formulas to automatically setup the Ranges for the charts
 
Thanks Hui,

The 1st sheet called "Networking_Log_04-04-12_13_41" in the workbook I uploaded here I think, this sheet is the data input sheet, the new data will be pasted into this sheet every week and the idea is that the graphs and sheet of formatted data will adjust accordingly.

This is obviously where the dynamic ranges come in. I have attempted to use these several times now but cannot get them to work.

Here is my method;

Select cells D5, right click, select Define Name,

Enter name as "Accept1"

In Refers to box, enter =OFFSET($D$5,0,0,MATCH(1E+306,$D:$D,1),1)

Go to sheet 3 "formatted data"

Repeat process for cell E5 as column E in sheet 3 is the new position of the raw data in column D in sheet 1.

This time in refers to box enter =OFFSET($E$5,0,0,MATCH(1E+306,$E:$E,1),1)

Go to sheet2 "graphs" click on relevant existing xy scatter plot

Select data, edit

in Series Y values box enter =OFFSET($E$5,0,0,MATCH(1E+306,$E:$E,1),1)

Get error message saying that function is not valid

Proceed to bang head on desk! :p


I have tried this same method using the formula =OFFSET($D$5,0,0,COUNT($D:$D),1)

This had the same result as above.


I also attempted to use the formula =INDEX(Accept1,ROW()-3) to try and link the dynamic range across the 2 sheets, i.e. that the column E in sheet 3 would represent the dynamic range in column D in sheet1.

This didn't work either! It just returns the #REF! error.


If you could help me with what I'm doing wrong I would greatly appreciate it.


Thanks


Also I am going to the library tonight to try and find a book on excel to help me with this as I need all the help I can get, is there any titles you would recommend?
 
@Montrey

Hi!

I'm still wondering if we'd be able to read your solution... sometime... in the near future...

:)

Regards!
 
=OFFSET('Formated Data'!$E$5,,,COUNT('Formated Data'!$E:$E))


Put that as a named range. Then add the named range to the graph. It will work!
 
@Montrey

Hi!

I wouldn't have guessed that in a hundred years...

XD

Regards!

PS: ... or was it in a couple of minutes?
 
@Montrey

Hi!

Maybe I didn't express myself correctly. I didn't mean that I wouldn't had guessed your solution in a hundred years, if it was read like that I apologize. I was talking about your testing process that added a "little" :) delay to the main answer, you silly young man!

Regards!
 
Yea i read his post then I got all excited like a little school girl because I thought I had an answer. I love mini-challenges too.


Now I have to wait to see if that's what he wanted.


@sirjb7

no offence taken, :p
 
Hi Montrey!


Thanks so much for helping me out with this, I still not getting it tho, I'm a total excel noob so pleae forgive my stupidity but where do I put the name of the named range? I selected column E in the formated data sheet, clicked define name, in refers to box copied in =OFFSET('Formated Data'!$E$5,,,COUNT('Formated Data'!$E:$E)) and named the range "acceptstream" went onto the graphs sheet and clicked select data, add series, I tried putting in "acceptstream" into the series name box but it said you must enter a value, do I put "acceptstream" into the Y series box? this didn't work either?

Please help ! :)
 
Hi ,


The thing to remember about using range names in charts is that you have to prefix the name with the name of the workbook ; in your case , if the name of the workbook is sample.xlsm , then the usage would have to be sample.xlsm!acceptstream


The easiest thing is to define the chart series using cell addresses ; click on the data series on the chart , and in the formula bar , replace the required address , say :


Networking_Log_04-04-12_13_41!$C$3:$C$12


by


sample.xlsm!acceptstream


and press ENTER.


Narayan
 
Thanks Narayan!


Still failing epicly at this unfortunately! :(

on "formatted data" sheet, select column E, click define name,

Name: sample.xlsm!acceptstream

Scope: Workbook

Refers to: =OFFSET('Formated Data'!$E$5,,,COUNT('Formated Data'!$E:$E))

click ok

see error message saying "The name that you entered is not valid"


Any ideas what I'm doing wrong?

Again, apologies for my stupidity with excel! -__-
 
http://i1101.photobucket.com/albums/g434/deco_r/screenshot.jpg


screenshot of what I'm talking about if it helps!
 
You must log in or register to reply here.
Back
Top