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

Needs a chart

Hi team

I have explained my requirement of chart in the excel file.
I am looking for a chart as per user selection

Please help
 

Attachments

  • Chart.xlsx
    17.7 KB · Views: 6
Hi Ratan,

Can you paste values only and not links to data in your sample file as it is all turning error when I try to open it. May be we can help if we can see the data.

Regards,
 
Dear Misra,

Superb...if possible can know the logic how the selection data exactly picks the first four lines & vice versa..
Thanks in advance.
 
Hi Vignesh,

The logic lies in named formula for the four series, go through them. If you have any problem in understanding them write back.

Regards,
 
@Vignesh Veerasamy

First take X-Axis, This has to be dynamic as when the data grows it should consider further dates also.

So the formula for Axis is :
=OFFSET(Sheet1!$B$4,0,0,1,5)
So OFFSET function is generating a range from B4 to 5 cells (RED 5) since there are 5 dates I hard coded it to 5 otherwise in general you can use something like COUNTA($B$4:$Z$4) which will give the width of range.

Now come to series, so as per data layout there are some symmetry:

1. Both the option of dropdown has four series.
2. All the series are together in a group of 4 in consecutive rows.

So we had devised helper cells in the range A17:B24. Where in column A has two option with a gap of 3 rows and column B has number of rows to displace from for each selection.

Now see the formula for one series.

=OFFSET(Sheet1!$B$5,OFFSET(Sheet1!$B$17,MATCH(Sheet1!$F$15,Sheet1!$A$17:$A$24,0)-1+0,,1,1),0,1,5)

This will give a range of data. The blue OFFSET will give o. of rows to displace from B5. Rest 0 column to displace from B5, height of 1 row and width of 5 columns is hard coded.

So row displace will get a number from B17:B24 based on drop down selection.

Try executing these formulas in a cell and see the intermediate result.

Regards,
 
Back
Top