Dynamic Range linked to a Cell Return error message

[comp764]

HI.

I'm using a dynamic chart with name ranges, and I also have a formulas link to a cell in another worksheet where the chart is. So this cell that's linked to the formula show me the amount a data points that I input into that linked cell. For example, if I input the number 10 it show the last ten entries that where put in. It works fine until I input a number that exceeds the number of data on the spreadsheet.


So if I only have twenty entries and input twenty-one into the link cell a dialog box pop up and give me the message below. After that that chart stops show any info. So I have to go into the define name dialog box and click on the formula to get it to link back to chart.


"A formula in this worksheet contains on or more invalid references. Verity that for formula contains a valid path"


Here is the formula that I using:

=OFFSET(SHEET1!$F$2,COUNT(SHEET1!$F$F)-'CHART'!$D1,0,'CHART'!$D$1,1)


Can something be added to this formula to stop it from giving me this message when I input a number that exceeds the number of records in the spreadsheet? I'm using excel 2007.


Thanks, Dan

 

[Hui]

Dan


I think you need this

=OFFSET(SHEET1!$F$2,Max(0,COUNT(SHEET1!$F$F)-'CHART'!$D1 - 1),0,'CHART'!$D$1,1)

Note I've added a Max and an extra -1 to the Row Offset

 

[comp764]

Hi Hui,

Wow!! That's amazing how you guys know what formulas to use. I've used excel for about a year but never really got into charting. Just a few simple formulas, until I volunteered to help out a fellow employee. Wow never thought it would take so much time.


I making this workbook which has 19 worksheets and each worksheet will have 4 charts which will equal to 76 chart all together. Would you have any suggestions or ideas on what would be the best way to set this up. I have all the work done just wondering if there's a better way of setting up these charts.

Any suggestions or ideas would be greatly appreciated.


And again thank you so much for your help you guys are amazing. formula you gave works great!!


Thanks, Dan

 

[Hui]

Dan


I assume that all the charts are based of data on each sheet


I generally try and keep all my data on one sheet

Then have reports and charts on others


I would even go as far as having all the Charts on the same page


I would be using Named Formulas for defining the Charts as that way they can all be made to update as new data is added or reporting ranges/dates move


Hui...

 

[comp764]

Hui,

The thing is that each worksheet represent a different product that will have the Temperature, Ph, Viscosity, and Solids monitored. So I have a worksheet for each one. I see you mentioned that you would have all the chart on one page, is that possible? I have read about having several charts run through a drop down list.


Dan

 

[NARAYANK991]

Hi ,


You can have just one chart displaying data for the 4 variables viz. Temperature , Ph , Viscosity and Solids ; each product can be selected using a drop-down list.


http://chandoo.org/wp/tag/dynamic-charts/


has enough information on this topic.


Narayan

 

[comp764]

Hi,

First of all, I just want to thank you guys for all the help that you've given me I really appreciate it. I had another question about the formula you gave below. Like I mentioned on first post, I have the formula linked to input cell that shows last entries that were entered based on the number I input into that cell. Normally it will be showing the last ten to fifteen entries.


Is there a way that I can use this same formula to give me a min and max based on selected cells. For example: if I enter 20 in the input cell it would give me the max and min of the 20 entries or 25 etc. What I'm trying to do is add moving range of the Min and Max to my charts. I have some extra columns set up for that, but it gives me the Min & Max of the entire column.


=OFFSET(SHEET1!$F$2,Max(0,COUNT(SHEET1!$F$F)-'CHART'!$D1 - 1),0,'CHART'!$D$1,1)


Thanks