I have an excel sheet in which I have the data for a few stocks and I need to create the Line Charts for them. But the data first needs to go through 2-3 stages, in order to arrange it properly for plotting the line charts.
First the data comes into Raw Data Sheet from external source. Then it gets converted into Pivot Table Format. And then it is moved to third sheet, where data for every single symbol gets arranged properly. And then finally in the forth step, the line charts gets created.
I have confusion in the 3rd sheet. How do I properly move the data from the pivot table to this third sheet in such a manner that all the data gets arranged properly under each symbol name, on a daily basis ?
I have explained the requirement in very clear and detailed manner in the attached sample file.
Please suggest various methods by which this could be done.
If you have any doubts, then please ask them, and I would explain in more details.
I am using the following software versions -
Microsoft SQL Server Management Studio version- 12.0.2000.8,
Microsoft Office 2016 x64
and Windows 7 x64
I am trying to put the question in very simple terms -
All I am trying to do is to find a method by which data for specific column could be picked up from the Pivot Table and then it can be copied to FIXED CELL LOCATIONS in the adjoining sheet.
Something similar to doing the Vlookup type of thing. But this time we are picking up the data directly from pivot table, so I am not sure what method to be used.
If anyone want more explanation, then kindly ask, I will explain in as much detail as possible. Please give it a shot, have a look at the sample file. It is not as complicated as it looks :)
@Neeraj bhai, thanks again for providing great help. You have understood the requirement 100 % correctly.
We just need to make a slight modification in the solution that you have provided. I have uploaded your file with slight more explanation, please take a look. Excel-Chart-with-Pivot-Table-Data--.xlsx
Just as you have referred to cell number G3, in the formulas that you have written for Column H, we just need to make sure that we make use of the information given in the Column B and C instead. Everything else is perfect.
Your description shows that you are trying to get the data from the pivot table on another sheet exactly in the same format as of pivot table itself and then inserting a line chart for all the stocks based on that data. Right?
If this is your intention, why not just insert a line chart based on the Pivot Table itself or rather Pivot Chart and have a slicer based on the stock symbols so that you can filter the chart if required.
I am really not sure why do you want to replicate the data from the Pivot Table on another sheet for all the stocks in order to insert a chart.
happy 1001
ASKER
Your description shows that you are trying to get the data from the pivot table on another sheet exactly in the same format as of pivot table itself and then inserting a line chart for all the stocks based on that data. Right?
Yes, I am replicating the data from the Pivot Table. BUT in a way, where I am able to Choose and Decide the Exact Location, where exactly the data for a specific symbol goes. I can define that this stock xxx should go into column H, and the data for this stock yyy should go into column K and so on.
If this is your intention, why not just insert a line chart based on the Pivot Table itself or rather Pivot Chart and have a slicer based on the stock symbols so that you can filter the chart if required.
Because once I have arranged the data according to my requirement, as explained above, then I intend to create various different kind of charts for ALL OF THEM in one sheet. I do not need just one chart at a time which can be then changed by using slicers etc. Instead I want to have ALL the Charts for ALL the symbols in Permanent Manner.
That is the reason I need to have a proper method to have the data for specific symbols in specific locations, so that then I can start the work of creating charts for all of them, one by one.
If you have any more confusion regarding anything, then please ask.
I am very grateful for your exceptional help. Thanks a lot
All I am trying to do is to find a method by which data for specific column could be picked up from the Pivot Table and then it can be copied to FIXED CELL LOCATIONS in the adjoining sheet.
Something similar to doing the Vlookup type of thing. But this time we are picking up the data directly from pivot table, so I am not sure what method to be used.
If anyone want more explanation, then kindly ask, I will explain in as much detail as possible. Please give it a shot, have a look at the sample file. It is not as complicated as it looks :)
Thanks a lot