Dynamic named range reading my cell formula - can I avoid this? 1

[OMoore]

Hi all

I am using a dynamic named range (that I learned from GlenUK) to automatically update a graph of data points (see formula below).

The figure I am updating is a calculated percentage, and if there are no figures entered to calculate it, then the cell returns a blank. To explain - the percentage value in the field is calculated by dividing field B by field C, but will only show a result if cell B is populated (e.g. formula for cell A2 is =IF(B2="","",B2/C2).

I know this is complicated but bear with me !

Now the problem arises when, since I am using a named range, technically, even though there may be no value in the cell to plot, it still sees my formula and populates my graph with blank spaces..e.g.

Field..Call value
A2.....84%
A3.....76%
etc..
A10.....90%
A11....=IF(B11="","",B11/C11)
A12....=IF(B12="","",B12/C12).
..etc...
AX.....=IF(BX="","",BX/CX).

My named range formula is:
=OFFSET(INDIRECT("'Sheet1'!$A$2"),0,0,COUNTA('Sheet1'!$A:$A)-1,1)

Does anyone know how to make the named range avoid reading the formulas, or have some other option for getting around this issue!!!!

Thanks,
Owen

 

[DaleWatson123321]

Owen,

I see no one has yet come to your rescue, so perhaps I can help out.

The method I often use in creating data ranges for driving charts, is to:

a) Extract to a separate sheet only the data that meets the criteria for the chart. While the criteria can be as elaborate as necessary, in your case it likely only requires that blank cells NOT be included. For this extraction, I use VBA code associated with Excel's "Advanced Filter".

b) Based on the data extracted, my code then re-assigns the coordinates of the range name used by the chart to reference the data. This practice has always worked well for me, but some have suggested I should consider using a "dynamic" range. So perhaps you could use the dynamic range you've already set up, if you decide it works better.

I developed a file in June that I've been using to track and graph the conversion rate between the U.S. and Canadian dollar. This file demonstrates the above, as well as a couple of other "enhancements" made to the chart with VBA.

If you or anyone else would like the file, email me and I'll send it via return email.

I hope this helps.

Regards,

Dale Watson dalwatson@gov.mb.ca

 

[GlennUK]

Hi Owen,

all you need to do is change your named range formula so that the part inside the COUNTA function counts column B, which will give the right number of cells to use for plotting. You don't have to have the OFFSET and the COUNTA pointing at the same places.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[OMoore]

Glen,

Can you elaborate? The data I need is in column A so why would I reference column B?

Dale - thanks for the reply!! I see the advantages of doing it your way, but this requires you to copy the information manually doesn't it?

Thanks
Owen

 

[GlennUK]

Let me explain ...

your OFFSET function is being used to create a reference to a range, in column A, but you only want to include those cells that have a corresponding entry in column B. Assuming that the empty cells in column that are blank are actually unpopulated then using COUNTA on column B will give the number of cells of column A that you require.




Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[OMoore]

Glen - you're a genius!! It works perfectly. I've referenced a different column for the CountA function and now my graphs are coming out perfectely.

Thank!

Owen