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

Dynamic Range in Excel Chart .

Shweta Nair

New Member
Hi ,

I request assistance in my below query.

I have a data of years (2015,2016) and months for joiner and resignee in a company.

My aim is to plot the data on a chart basis user selection. I have given a list box where the user can select year and whether he/she wants joiner, resignee or both(joiner and resignee).

1. User will select required year.

2. Then user will select if :

a) He wants joinee for that year Or

b) He wants Resignees for that year Or

c) He wants both joinee and resignee for that year​

I have achieved plotting chart for requirement 2a and 2b.

For requirement 2c , I had written a separate two Offset formula.( one for joinee range column selection in chart) and other for resignee range in chart.

I then tried to combine all the formula using If criteria.

I have done several trials and variations of the IF formula to determine what the user has selected . But none of the achieve the desired result. I also get a number of named reference errors while trying to achieve requirement 2c.

I have attached the excel file.

Kindly assist.

Regards

Shweta Nair
 

Attachments

  • Summary of Query.png
    Summary of Query.png
    55.7 KB · Views: 5
  • Joinee_Resignee.xlsx
    17.9 KB · Views: 4
Personally, I'd restructure your source table (i.e. make it into flat table, using type column to track joinee/resignee) and use Pivot Table to summarize.

At any rate, try following. Combine Joinee and Resignee column in chart source to single column.

Use following formula in H6 & copy down.
=SUMIFS($C$2:$C$25,$A$2:$A$25,$H$2,$B$2:$B$25,G6)*OR($H$3={"Joinee","both"})+SUMIFS($D$2:$D$25,$A$2:$A$25,$H$2,$B$2:$B$25,G6)*OR($H$3={"Resignee","both"})

See attached.
 

Attachments

  • Joinee_Resignee.xlsx
    22.5 KB · Views: 4
Hi,

Thanks for your assistance.

In the attached solution sheet , when I select both , the data is represented in one column in the chart which is the sum of the joinees and resignees for that month.

I intend the data to be represented in two separate columns in the chart.

Attached Image.

Regards
Shweta
 

Attachments

  • Query 1.png
    Query 1.png
    40.2 KB · Views: 2
Then I'd recommend that you go with my original suggestion. Use PivotTable.

See sheet2 for how it's set up.
 

Attachments

  • Joinee_Resignee (1).xlsx
    39.8 KB · Views: 7
Back
Top