Dynamic Chart - 12 months

Hi kevinjay1,

You shouldn't have any problems with the version of Excel, can you advise where you encounter a problem?

Can you also outline the steps you take to create the chart?

Cheers

Hello noboffinme,

I followed the steps exactly as shown at http://peltiertech.com/Excel/Charts/DynamicLast12.html, even started a new sheet with the exact same values he had on his page, I created the ranges (?) exactly as he did, but when I go to add the chart, I have a blank chart, when I go to add the series and ranges, I get error messages. If you ctl-F3, you will see the inputs I have added. They are now showing REF errors because I deleted the tab the values were in

Thanks for looking, Kevin

Hi,

I noticed that your Defined Ranges (Ctrl + F3) had errors.

I've posted your file with the Peltier exercise in 'Sheet1' & it works OK.

Can you have a look at the defined ranges & check against yours, you may have made an error copying the Formulas.

Let me know how you go.

Cheers

OK, I've created the same exercise with the data in the 'By_Lab' worksheet.

Note that I added an underscore in your worksheet name, better practice if you are going to use a worksheet name in a Formula.

All this exercise & the Functions are doing is selecting the number of Cells (Changeable in Cell 'N2') in Column 'A' from the bottom populated Row

It then reads across to the Column on the same Row that you want to chart.

So you make the value in Cell 'N2' to 6 - it reads the last 6 months in Column 'A'

Based on that, it charts the values (chtValA) 7 Columns to the right - same with chtValB but 10 Columns across.

-----------------------------------------------
A quick summary of the very useful OFFSET Function.

All it does is refer to a Cell OR a Range of Cells based on what Cell is named in the Formula.

It has four arguments (or parameters) 1.Rows, 2.Columns, & (Optional) 3. Height, (Optional) 4.Width

So if you write the below in a blank Cell;

Please Login or Register  to view this content.

The result will show the value in the Cell 2 Rows below & 1 Column across from 'E5'
------------------------------------------------------

Also, you need to adjust the Formulas to suit your data, the chtValA Named Range should be

Please Login or Register  to view this content.

because the value you want charted is 7 Columns from the Month Column - Air Target is 10 Columns from the Month Column.

Hope this clarifies.

Thank you very much for your explanations, it makes sense now, and thank you for your patience. I need one more exercise in patience if you don't mind.

I Hate to say this publicly, but here goes

I cannot create a graph because I cannot add the ranges. I go to add series , type in =ChtValA (for example) and get an error message stating that my formula contains an error. I then try ='ChtValA', same message. I then try ='(worksheet name)'!ChatValA and get the same message. I can see where I need to go but am too dense to create a chart. I can see your formulas, I can see what data they are selecting, I can see why they are selecting that data, but a simple graph is now defeating me. I have looked at several different examples and actual sheets that are using this and have multiple defined ranges, but the graph is stopping me.

I will now go and hang my head in shame......

Edit: It must be this file, I have another one with dynamic ranges and am flying through it now, not sure why I can't get the naming right

LOL, hang in there, we'll see what else we can try.

Try these 2 options;

1. Create a chart manually, that is select the range of Cells & Create a chart, now try to add a new Series based on the exercise instructions.

2. Make sure you enter the Formulas EXACTLY as they are written in mine, If you have spaces in the workbook name, you need the apostrophes (' '), otherwise if you use By_Lab, apostrophes should not be used.

Can you post your latest file, errors included, so I can check it out.

Cheers

Hi, I noticed that your data in 'By_Lab' has the Month Column populated below where the figures finish.

Remember the Formula reads the populated values from the Bottom of Column 'A' & then reads the figures across from those Rows.

If there's nothing there to read, nothing will appear in your Chart.

I'm working on getting your file working in the meantime.

Cheers

I've done the 'Charts_by_By_Lab' & added a Spin button to change the number of months to display in all 6 charts simultaneously.

Have a look at the Named ranges & see if you follow how it works.

Sometimes a report or dashboard looks intimidating but if you break it down into it's component parts, you'll see that you're capable of doing the same yourself.

Let me know if you have any other questions.

Cheers

Glad I could help.

Hi diver4fun,

I'll try to help you but you need to start a new thread.