Needs a chart

[Ratan Bhushan]

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

 

[Somendra Misra]

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,

 

[Ratan Bhushan]

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,

hI sIR


I am uploading again

Please check

 

[Somendra Misra]

@Ratan Bhushan

See the file, select the drop down in orange cell.

Regards,

 

[Ratan Bhushan]

@Ratan Bhushan

See the file, select the drop down in orange cell.

Regards,

Sir

When I am opening the file its saying that the file contains one of the formula which has invalid reference and file not working. No chart showing when I am opening here

 

[Somendra Misra]

See the file now.

Regards,

 

[Ratan Bhushan]

See the file now.

Regards,

 

[Ratan Bhushan]

This error I am getting

 

[Ratan Bhushan]

@Ratan Bhushan

See the file, select the drop down in orange cell.

Regards,

sir

It worked

You are superlike.

Thanks.

 

[Somendra Misra]

Glad it worked for you & thanks for the feedback.

Welcome back!!!

Regards,

 

[Walter Simms]

Different types of chart are there such as pie chart, bar chart, line chart etc. Select one of the chart as per your requirement and mentioned all the data.

 

[Vignesh Veerasamy]

Dear Misra,

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

 

[Somendra Misra]

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]

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,

Dear Misra,

Really sorry I am not able to intercept the formula....unable to crack it....

 

[NARAYANK991]

Hi Vignesh ,

See if this is easier to understand.

Narayan

 

[Somendra Misra]

@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,

 

[Vignesh Veerasamy]

Dear @NARAYANK991 @Somendra Misra Sir's

Thanks for your great efforts..cracked the logic....