I'm using MS Query to retrieve data from a SQL Database and once in Excel I
create new columns to calculate lets say "cost" by multiplying one of the
retrieved columns by a constant, since the amount of data retrieved (rows)
with the query changes (depends on criteria that I set up on the Query), the
rows on the new columns should change too, and I don't want #REF! cells, I'm
using the next formula to calculate the new values on the new column
=IF(ISBLANK(AH2),"",AH2*0.075/AL2). Now I have charts that show the values
retrieved with the Query and charts that show the values of the new columns.
Everything goes fine with the charts for the data retrieved with the Query,
when the amount of data (rows) changes, the amound of data shown in the chart
changes too, but the proble that I facing now is that the charts for the new
columns show the values ok just the first time I retrieve data with the
Query, next time I pull data using a different criteria in the Query, the
charts for the new column show just up to the last values from the previous
selection. In other words, runing the query, lets say I retrieve 20 raws,
then I calculate the new 20 rows using the formula above and these values are
shown in the charts, but if my next selection on the Query retrieves, let say
30 raws, my charts for the original dat is 30, fine; but my chart for the
calculated rows shows just 20 values.

Any help will it be appreciated
--
Cesar

Hi,

At least one approach would be to use Dynamic Range Names, here is a step by
step example of how to do that:


http://peltiertech.com/WordPress/200...t-source-data/

--
Thanks,
Shane Devenshire


"Cesar" wrote:

I'm using MS Query to retrieve data from a SQL Database and once in Excel I
create new columns to calculate lets say "cost" by multiplying one of the
retrieved columns by a constant, since the amount of data retrieved (rows)
with the query changes (depends on criteria that I set up on the Query), the
rows on the new columns should change too, and I don't want #REF! cells, I'm
using the next formula to calculate the new values on the new column
=IF(ISBLANK(AH2),"",AH2*0.075/AL2). Now I have charts that show the values
retrieved with the Query and charts that show the values of the new columns.
Everything goes fine with the charts for the data retrieved with the Query,
when the amount of data (rows) changes, the amound of data shown in the chart
changes too, but the proble that I facing now is that the charts for the new
columns show the values ok just the first time I retrieve data with the
Query, next time I pull data using a different criteria in the Query, the
charts for the new column show just up to the last values from the previous
selection. In other words, runing the query, lets say I retrieve 20 raws,
then I calculate the new 20 rows using the formula above and these values are
shown in the charts, but if my next selection on the Query retrieves, let say
30 raws, my charts for the original dat is 30, fine; but my chart for the
calculated rows shows just 20 values.

Any help will it be appreciated
--
Cesar