Hi Jon,
Well, I moved away Gross Amount since I don't have any TOTALS below my
ranges. Please have a look here for better overview:
http://img398.imageshack.us/my.php?i...drangesqk9.jpg
(obviously SUMPRODUCT formula has just different conditionals for each column)
As you can see in the picture my ranges look as you told me to do so aside
from COUNTA (I have put there a number of rows rather than COUNTA) but no
avail anyway when formulas "enter " values. I suspect the culprit to be
formulas since thay show 0.00 when no data is available and the date is
"blank" because the formula is defined to do so. Maybe there is a way to
"REFRESH THE RANGES" like in a web browser by pressing F5??:) and then it
will trigger them:)
I don't what to do now. Every single time I had"created" new values in a
source worksheet and formulas showed them, ranges didn't expand.
Any furhter suggestions Jon, as I still have a hope:)?
Thanks,
Piotr
"Jon Peltier" wrote:
The formulas under the linked data are messing you up, I fear. Could you put
the totals above the range, or below the entire range, not three cells below
the last month?
If you know how many months of data you have, use that number in place of
COUNTA(table!$G$39:$G$45) in your dynamic range formula. Also, just define
it once, then define the others like so:
Name: FirstRange
Refers To:
=OFFSET(table!$G$39,0,0,COUNTA(table!$G$39:$G$45), 1)
Name: SecondRange
RefersTo:
=OFFSET(FirstRange,0,1)
Name: ThirdRange
RefersTo:
=OFFSET(FirstRange,0,2)
etc.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
"Piotr (Peter)" wrote in message
...
Hi Jon,
Ok so I have read about 6 different tutorials including yours about
dynamic
ranges, however as an intermediate user I am still confused.
I have a table G39:K50:
Invoices [GBP] Payments [GBP]
Per Invoiced Gross Amount Payment received Pay yet to receive Rec. vs.
Invoiced
1 2008 26,643.26 26,643.26 0.00 100%
2 2008 17,596.49 17,596.49 0.00 100%
3 2008 45,565.64 31,987.15 13,578.49 70%
4 2008 34,258.51 22,766.73 11,491.78 66%
5 2008 30,873.22 18,138.02 12,735.20 59%
6 2008 39,550.61 22,695.54 16,855.06 57%
7 2008 37,848.49 11,041.48 26,807.01 29%
(blank) 0.00 0.00 0.00 #DIV/0!
(blank) 0.00 0.00
0.00 #DIV/0!
This table ends in a row G50 where December will appear. All cells contain
formulas that either displays month and a year when found in a source or
sum
up specified values in a range, so there are "blanks" and "zeros' as the
formulas have been copied down in order to show dispalay new month and sum
up
what I need.
Having done little exercise when creating a Dynamic Range according
www.contextures.on.ca/xlNames01.html I could see the expanded range when I
typed new name. Fantastic!. Nonetheless, what I would like to achieve is
whenever new values appear automatically (as formulas will find them in a
table), then the range is expanded.
One of my ranges looks as follows:
Period_Invoiced=OFFSET(table!$G$39,0,0,COUNTA(tabl e!$G$39:$G$45),1) G is
obvoiusly changed for H, I, J, K. I was just to confused with this formula
when using Named Ranges so I left it as it is.
My concern though, is my source data for a chart: Payment
Received=table!$I$39:$I$45, Payment_yet_to_receive=table!$J$39:$J$45,
Received vs. Invoices=table!$K$39:$K$45 and Category X (also secondary
X)=table!$G$39:$G$45.
When new data appears in a table found by my formulas, the ranges do not
expand. I am doing something wrong here. Possibly I should have change the
ranges all the way down to a row 50 but when I did that, also no avail.I
can't add every single time data by hand as I said I have over 100 charts
and
I would like to create a tool that will save my time. Aslo all my charts
will
be "chart sheets".
I spent all my day trying to sort this out and I just can't get this
right.
I fyou could assist me again I would be obliged.
Sorry for the long post but I wanted to explain this correctly.