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

How to link my dynamic chart data using OFFSET??

rkspeaks

Member
Hi Excel Ninjas,

I have a chart data which runs from left to right as dynamic categories. I have a drop down to select these items in A2. Categories as Horizontal... Fruits, Clothing, Auto, Gadgets etc and can increase in future. Criteria as Vertical...Sales, Profit, Advance which are 3 rows and fixed for all categories. If I select any category say Fruits or Clothing or Auto in A2, I want a chart for the vertical rows i.e. Sales, Profit and Advance. I used the below named range but failed to link it to the chart axis. I attached my sample sheet below. Please help me.

=OFFSET(Sheet1!XFA3,0,MATCH(Sheet1!C4,Sheet1!$B$1:$E$1,0),3,1)

Thank you
RK
 

Attachments

  • DynamicChart.xlsx
    8.1 KB · Views: 7
Sorry, a small typo error. The formula I tried is the below one.
=OFFSET(Sheet1!A1,0,MATCH(Sheet1!A3,Sheet1!$B$1:$E$1,0),3,1).
 
I think your formula should be:
=OFFSET(Sheet1!A1,1,MATCH(Sheet1!G2,Sheet1!$B$1:$E$1,0),3,1)
 
Hui, thanks for the reply..But how to link them to the chart's source data. I named them as ranges. Even I accessed the name going to the "Select data" option in the chart. It says the formula you typed contains an error. But the formula works good outside the chart arena.

RK
 
You will need to change the Chartt named range to:
=OFFSET(Sheet1!$A$1,1,MATCH(Sheet1!$G$2,Sheet1!$B$1:$E$1,0),3,1)

then add a chart with no data (Insert, Chart)
Right Click, Select data
Add a Series
Series Values: =Sheet1!Chartt
upload_2014-8-25_10-52-22.png

See attached
 

Attachments

  • DynamicChart-Hui.xlsx
    13.1 KB · Views: 17
Back
Top