• 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.

Grab variable data from different Tab to Plot

R

rikt

New Member
Hello,


This is my first time posting here and I have a question about charts and data selection.


My goal is to create a Gas Ratio Pixler Plot which involves 5 columns of data, the first one will refer to a depth and the other 4 ones will plot points which together will form a line. That is each Depth and associated 4 points will plot a line, so I should be able to plot several lines to compare. This will be plotted on a Log scale. So far so good, I already did all of this part.


The thing is I need to get the data for the chart (the 5 mentioned columns) from another Tab where all the values are.


I need a way to be able to select ranges of depths, with the associated 4 points to plot, and plot them by doing so. That is I would like to be able to insert the depth I need to plot in a form of range. Something like “From depth: 1000 ft To depth: 1050 ft” where I only need to type in the actual depths (1000, 1050, etc) and the graph will grab the associated 4 points to plot.


This way I should be able to "summon" the data I need into either the chart itself directly or a group of cells from where the chart will collect/plot the data.


I’m not sure I was able to be clear on what I need, but please let me know so I can try to explain better.


Thanks!
 
Hi ,


I do not know about others , but for me , my understanding will be total if your workbook is before me ; if you can upload your file , your explanations may not be need to be lengthy ; in fact they may not be needed at all.


In case you wish to upload your file :


This forum does not have a provision for either uploading files or attaching files to posts.


What you can do is to use your preferred file-sharing website ( RapidShare , Hotfile , DropBox , SkyDrive , GoogleDocs , SpeedyShare ... ) to upload your file , give others permission to access and download the file , and then post that access link here , in this same topic.


Narayan
 
Thanks for that info. Here is a small version of the file.


The original data and calculation is in the “01.GasData” tab, from where I copy. The plot on “02.Plot” is getting the data from that small table next to it where I have pasted the data from “01.GasData”.


I want to be able to add the values I need to the table that feeds the plot without having to go around copy/pasting.


The idea would be to have no table on the “02.Plot” at all. I want to be able to have a sort of query where I can insert “From: 10 To: 30” and have the data plot just like it is right now which is plotted from depth 10 to depth 30, just by typing 10 and 30 in the right place/cell. I want to be able to choose, by depths, the values of the C1/C2, C1/C3, C1/C4 and C1/C5 columns.


Please find the link to access the folder below.


http://rapidshare.com/files/3677504900/Gas%20Ratios.xlsx


Thanks!
 
Hi ,


Are the 3 straight connectors ( Straight Connector 3 , Straight Connector 8 and Straight Connector 12 ) relevant , or will you put them in place manually ?


Narayan
 
I actually know very little about excel, but I assume that by "Straight Connector" you mean the blue separator lines that I have drawn. They are irrelevant, I will place them by hand. They are simply visual markers and not important.


Thanks for the help! Really appreciate it!
 
Hi ,


Is it possible you can wait till tomorrow ? It's late at night here , and I can work on your chart only tomorrow.


Narayan
 
Hi ,


Can you dwnload your file from here , and see how it works ?


https://www.dropbox.com/s/o4iakqa3p874r9x/Gas%20Ratios.xlsx


To make any changes to the layers and see how the chart changes , do the following :


1. Open the workbook and go to the '02.Plot' tab , if it is not already the active tab.


2. Let us assume the data is as follows :



Start From Layer : 30


End At Layer : 50



3. Suppose you wish to change this to 10:40 ; make the required changes in cells AO3 and AO5 , so that AO3 is 10 and AO5 is 40.


4. Right-click on the chart , and click on Select Data ; the Chart data range input box will be highlighted.


5. Press F2 and use the right cursor key to place the cursor at the end of the displayed data range.


6. Press the Backspace key to clear the range $I$5:$L$7 from the input.


7. Enter the text From_To


8. Press OK.


If these instructions seem too complex , let me know ; a macro can be written so that all of the above happens automatically when ever you change data in AO3 or AO5.


Narayan
 
Hi and thanks for the help!


I apologise I didn't reply earlier. This has absolutelly no urgency whatsoever, I just needed it eventually. I hope I didn't make you lose any sleep, I'm so sorry.


The macro option would be the best solution and was actually what I was looking for since the beginning, but due to my lack of knowledge in excel I probably couldn't explain it and didn't make that clear.


Thank you once again!
 
UAU This is A-M-A-Z-I-N-G!!!


It's exatly what I wanted!!!


I will now try to do the same into the original file which is much bigger and holds confidential data. That's only reason I coundn't send it over.


I see on the first tab there is a cell with a code regarding some index wich I guess is related to the button you created. I need to keep it, right?


I hope I'm not pushing your patience, but I was wondering if you caould by any chance explain step by step how you did it so I can recreate it on the other file. I'm not in a hurry at all so if by any ahance you can do it, please do so with time. No rush.


Thanks once again! This is great stuff!
 
Hi ,


Sorry about that ; I was checking something out ; you can delete that.


There are only two things you need to go through :


1. The named ranges which are :


Data_Range
Code: 
='01.GasData'!$I$2:$L$11

[b]From_To [code]=INDEX(Data_Range,MATCH(From_Val,'01.GasData'!$H$2:$H$11,0),0):INDEX(Data_Range,MATCH(To_Val,'01.GasData'!$H$2:$H$11,0),0)

[b]From_Val[/b] [code]='02.Plot'!$AO$3

To_Val ='02.Plot'!$AO$5[/code]

X_axis[/b] ='01.GasData'!$I$1:$L$1[/code]


2. The macro itself , which has some values in it which you will need to change if you add more series to the chart. In case you have any doubts on the macro , please let me know.


Narayan
 
Hi ,


I don't think there is much to explain ; all of the work is done by the named range From_To.


Suppose your starting layer is 20 ; this value is in the named range From_Val ; in the definition of the named range From_To , the first part is :

[pre]
Code: 
=INDEX(Data_Range,MATCH(From_Val,'01.GasData'!$H$2:$H$11,0),0)
which is using the INDEX function with the column parameter having a value of 0 ; what this does is return the complete row which matches the value of the starting layer ; since your data range is I2:L11 , where H2 = 0 , H3 = 10 and H4 = 20 , the fourth row i.e. I4:L4 is returned.


If your ending layer is 50 , this value is in the named range To_Val[/b] ; in the definition of the named range From_To[/b] , the second part is :

[pre][code]INDEX(Data_Range,MATCH(To_Val,'01.GasData'!$H$2:$H$11,0),0)
[/pre]
which again uses the INDEX function with the column parameter having a value of 0 ; what this does is return the complete row which matches the value of the ending layer ; since your data range is I2:L11 , where H7 = 50 , the seventh row i.e. I7:L7 is returned.


Thus , what is passed on to the chart as the data range is I4:L7 , where the series are plotted in rows ; this is specified in the macro statement :

With ActiveSheet.ChartObjects("Chart 2").Chart
.SetSourceData [From_To], xlRows[/code][/pre]
Narayan
 
Ok thanks!


I will try to remake it on the other one.


the macro itself I have no idea how to make it but I will search first.


thanks!
 
You must log in or register to reply here.
Back
Top