×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Drawing lines between unsequential data points

Drawing lines between unsequential data points

Drawing lines between unsequential data points

(OP)
I have  a series of data points with XY co-ordinates listed in 2 columns. These data points are connected by lines to form a network, but unsequential, ie. data point 1 is connected to point 3, point 2 is connected to 5 etc. The network connections are listed in seperate columns so that the full data set might look like:
    Points           Network
 Pt   X     Y       Pt1   Pt2
  1   0     1        1     3
  2   1     3        2     5 .... etc.

If the data points alone were plotted then lines would show connecting point 1 to 2, 2 to 3 etc. However, I would like to plot this network with a series of lines between the points as defined in the Network connections columns.

How can I plot the points and connecting lines automatically so that each connection is shown on the XY graph as a seperate series of 2 data points? I've tried using a VBMacro and using a VB Userform to define my own graph but can find no way of defining a line without a picture added to the form, and no picture facility appears to exist in Excel Visual Basic.  

corus

RE: Drawing lines between unsequential data points

Amazingly easy using Vlookup, and the fact that XY graphs plot line sgements only if you put in a blank row


so

node_num_1 x1 y1
node_num_2 x2 y2
empty row
node_num_3 x3 y3
node_num_4 x4 y4
empty row

will produce a series of vectors on your x y plot when you select the second and third columns.

Cheers

Greg Locock

SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Drawing lines between unsequential data points

(OP)
Many thanks to you both.

The lookup function in Onda's sheet worked, whereas vlookup gave #value in the cell, for some reason. Leaving blank rows to give a separate series for each line would have been an ideal solution, but also didn't seem to work. I thought this was because the complete row isn't blank, only the cells below each set of data, but transferring the data to a blank sheet didn't produce separate series of lines for each set of data either.

It'd be interesting to know if lines could be drawn on a blank userform in a VBmacro, if it's at all possible.  

corus

RE: Drawing lines between unsequential data points

(OP)
Got it now. A blank row/cell simply separates the data so that the lines are disjointed for each pair of points, but as a single series.
Drawing lines in a macro appears to be only possible using the autoshapes, drawn on the worksheet, and not on a separate form. Oh well.
Thanks again.  

corus

RE: Drawing lines between unsequential data points

Corus - I'm still not clear exactly what it is you are trying to do, and why Greg's approach won't work.

There is some information about drawing on the spreadsheet using autoshapes here:
http://newtonexcelbach.wordpress.com/2008/11/11/drawing-in-excel-7-creating-drawings-from-coordinates/

I'm pretty sure I've seen examples of people drawing on forms with shapes, but I've never tried it or looked at the details.

Finally, if you do have another go at Greg's approach I'd suggest using =Index() to retrieve the data rather than =Vlookup, because it's much quicker (not that it makes much difference these days, unless you have a huge amount of data).

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Drawing lines between unsequential data points

(OP)
Thanks IDS, an excellent link but as they show, drawing seems restricted to the worksheet using autoshapes/lines rather than on a separate form. I have seen excel sheets that call up a .exe file to use the data within the spreadsheet and then produce drawings/contours etc. on a separate form, but outside of Excel.

I'm also not sure why VLOOKUP didn't work as opposed to LOOKUP used in ONDA's sheet. Probably some subtle little error in the input I missed.  

corus

RE: Drawing lines between unsequential data points

I have added a VLookup and an Index version of the Lookup table given in the data_point_connection file.

They all give the same result, but Index is much faster, because it goes straight to the specified cell, rather than searching through a column until it finds the right value.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources