OFFSET for chart that has varying X and Y ranges

[rugbyhubby]

I'm working on a chart that would display two data series (this is for tracking bloodwork results). The data is in a flat file and I want the user to enter a start/end date for the chart but they don't have to remember which date they had the blood work done. I have a formula that will find the closest date to the start and end date entered. I have another formula that will count the number of rows of data between those dates. The user then chooses which datapoints to compare via a drop down. With the dropdown I know the column for the data as well as the hi and low range for that data point. I am then displaying the data with a simple index formula. Since the data points could range from 5 to 100 I want to use OFFSET to create the chart. I'm using an OFFSET formula but I can't get the COUNTA portion of the formula to work of a referenced range. I hope I'm providing all the details and I will try to attach a clean spreadsheet as well.


Using =ADDRESS(27,6)&":"&ADDRESS((27+($E$7-1)),6) I can get the range $F$27:$F$31 using the starting point and the number of 'rows' of data that exist. However, when I create my OFFSET formula for the chart using the formula above embedded in the COUNTA argument the COUNTA argument evaluates the "formula" and not the range.


Here's my offset formula (there are three: dates, datapoint1, datapoint2):

=OFFSET(DP!$E$26,1,1,COUNTA(ADDRESS(26,6)&":"&ADDRESS((26+DP!$E$7),6)),1)

this is saved as a named range.


The data looks like this.

[pre]

Platelet Count	Hemoglobin
3/2/2012	207	14.1
4/2/2012	182	14.3
5/2/2012	193	15.2
6/2/2012	161	14.6
7/2/2012	15	150

[/pre]
If I chose a longer range then there would be more rows of data and I want the chart to automatically account for that.


Please tell me if you need anything further.


Thanks...bill

 

[Sajan]

Hi,

COUNTA accepts 1 to 255 values as its argument, and returns the count of those values.


In your example, the argument you are supplying to the COUNTA function is a single string value. If you need to reference the underlying range, you will need to use the INDIRECT function on top of the ADDRESS result.

e.g. =COUNTA(INDIRECT(ADDRESS(...)))


Cheers,

Sajan.

 

[Hui]

Rugbyhubby


Firstly, Welcome to the Chandoo.org Forums


Can you post a sample of the sheet with data you are working on:

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook


I think it can be done a lot simpler than you have tackled it, by simply using the Offset() and a few Counta() functions, but would like to see your data