Dynamic Chart

[StevenAlberta]

Does anyone know how to create a chart series that would be dynamic based on a cell value. For example, I want to chart the values in column A from row 10 to row x. Row x would be based on a cell value in B1 and would need to be added to row 10. So if the value of B1 is 25 then the chart series should display from A10 to A35.

I have tried using the offset function within the series input but keep getting errors. I am using Excel 2010.

Thanks, Steven

[rvasquez]

Could you post a sample of you workbook? Thanks!

[StevenAlberta]

I have not posted a workbook before. I have tried but does not work. Can someone please help.

[rvasquez]

Click go advanced below the comment section. Then select the paperclick, not the drop down but just click on the image. This should being up the manage attachments window. If you have pop up blockers you'll need to select the pop up blocker at the top of your explorer and select temporarily allow pop up then reselect the paperclip. (For me the manage attachments window opens in another windows explorer window and automatically minimizes so you'll have to select it.)

From the manage attachements window select Add Files on the top right.
Then select Select Files and select the workbook. Then click done.

Type a little information in the comments sections and then select Submit REply.

[StevenAlberta]

Here is a screen shot.

ScreenHunter_13 Jul. 23 11.44.jpg

[StevenAlberta]

Hopefully this will work.

Book2.xlsx

[rvasquez]

Sorry,
Just saw your attachement. I updated it to include the below code. Just change the value in B1 to watch it work.

Thanks!


**********************************
Post before I saw your attachement:

Alright let's try this,

Select the worksheet where the graph is (I believe it's sheet1 by your picture). Right click on the worksheet's tab and then select view code.

In the white space provided copy and paste the below code:

Please Login or Register  to view this content.

Exit out of the Visual Basic Window ( the window that opened)

and then change the value in cell B1 to watch it work.

Let me know if this works for you!

Thanks!

[StevenAlberta]

Sorry... but I want to do this without using a custom function but by only using the input field in the chart dialog box. I am sure it a matter of using the offset function correctly. Any ideas.

Thanks, Steven

[rvasquez]

Hey there,

No problem. Attached is our worksheet without VBA. I first named a range by going to formulas, Name Manager, and click New. In the name field I typed MyRange and in the Refers to I typed the following:

Please Login or Register  to view this content.

Which defines a range that starts at cell A10 and go to the row whose value is 10 plus the value in B1

Then I selected okay.

Then I select the chart, went to the Design tab and select Select Data, Deleted all series and then click Add to add new one. In the Chart data range I typed

Please Login or Register  to view this content.

Then selected okay and exited.

Let me know if you have any questions!

[StevenAlberta]

Thanks for the formula. There is still one problem that I can not figure out. When defining the formula you define it as follows:

=INDEX(Sheet1!$A$10:$A$200,Sheet1!$A$10):INDEX(Sheet1!$A$10:$A$200,Sheet1!$A$10+Sheet1!$B$1

Which makes perfect sense because the chart range would start at A10 and continue for the value of B1.

But in the sample you sent me the chart starts at A11 instead of A10. I can not figure why this is the case as everything looks fine. Can you please take a quick look at it and let me know why it is doing this and how to fix it.

Thanks, Steven

[rvasquez]

Change it to this, don't ask me why but this seems to work

Please Login or Register  to view this content.

If this is good, please don't forget to mark this thread solved and also if I helped give a little star tap.

Thanks!

[StevenAlberta]

I am not to sure why your example does not work either. I have now applied your logic to my real spreadsheet and works as it should and the chart series starts at the proper cell. The only modification I had to do was subtract 1 from the cell value that tells the formula how long to make the series otherwise it over shoots by 1 cell.

Thanks, Steven

[jpr73]

Hi,
Good job rvasquez
I tried this and it works

Please Login or Register  to view this content.

Regards

[StevenAlberta]

Spoke to soon....

This approach or formula does not work.

It seems to work when the input cell is manually inputted with a value. But when the input cell itself is a formula, in my case its "=COUNTIF(BO31:BO530,">0")" then when the spreadsheet is recalculated and it would produce a new value you get an error.

Does anyone have a suggestion...