Chart that resizes based on result of VLOOKUP

I have a clustered coulmns chart that i need to resize based on a vlookup of data. The issue is that the data is not the same nymber of rows. They range from 4 to 15. When the value with 4 is selected the charts has 11 blank values. Is there a way to do this so that the chart resizes itself to only what is populated on the lookup table? I know a pivot chart will do this but it does not have a "clean" enough look for the project.

Hello
I' m not sure what you mean by using Vlookup but take a look at the attached chart example. Is this what you are looking for? You need to create a couple of dymanic named ranges for the Y & X axis and link the chart to them and the row selection in cell H2.

Hope this helps.

Sorry the example you attached did not work for me. It says the a formula in worksheet contains and invalid reference. Attached below is the data i am working with. When the user selects the job they want the chart needs to be able to update based on the lookup lookup value (Analyst1). Chart 1.xlsx

Hi
Not sure why the attachment didn't work but I've downloaded you Chart. Could you explain what data you want displayed on your chart when a Job number is selected and an Analyst chosen with the Vlookup?

Hi
I've took a guess at what you might want. See attachment. Hope it loads without reference errors. Not sure what version of Excel you're using but this is in 2007. If you're using 2003 I can do a version of that too if you require.

No errors on this file and the layout of the chart is correct. However when i update the job the chart does not change.

Hi
Can you explain what you mean by update the job? Do you mean add data to the 'Data' Sheet?

DBY

When i change the value for Job Title on the Chart tab the chart does not update. I see that the from and to update but the chart itself does not update. I am using 2007.

Hi
Just to clarify these are the formulas you should be seeing:

Name Manager:

JobNo' Formula
=INDEX(data!$A$1:$A$101,Chart!$G$2,1):INDEX(data!$A$1:$A$101,Chart!$H$2,1)

Value' Formula
=INDEX(data!$E$1:$E$101,Chart!$G$2,1):INDEX(data!$E$1:$E$101,Chart!$H$2,1)

Chart Data:

Value Series Data Formula
='Chart 1.xlsx'!Value

JobNo Series Data Formula
='Chart 1.xlsx'!JobNo

I was just typing up that the Chart data looked off. They had values of [0]!Values and [0]!JobNo. It works perfectly now. Thanks for help much appreciated.