Updating Graphs using OFFSET (conversion to 2007)

[The_Ant]

A few years ago I made a worksheet for with sales data. Every day I upload the daily sales per product group into a sheet. At the moment I have a sheet full of sales data per day. In the old excel (post 2007) I had a great graph that showed me the sales over time, per product group. In a cell I would enter the product group name, and only that product group would appear in the graph. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The created the following formula can named it IndexProduct (therefore the formula was not in a cell but a name):<o></o>
=OFFSET(indirect(Product Name),,,MATCH(End Date,DATES,0))<o></o>
However all the data overtime would appear in the graph. I now want to be able to select the start date and end date.<o></o>
I changed the formula to:<o></o>
=OFFSET(indirect(Product Name),MATCH(Start Date,DATES,0),,MATCH(End Date,DATES,0)- MATCH(Start Date,DATES,0))<o></o>
The problem starts that Excel 2007 give me an error when changing the dates in the second formula. The worksheet was created in the old excel. I converted it to 2007, and it work fine. However, now I want to change the dates, I get a error. The worksheet is saved as .xlsm<o></o>
Furthermore, if possible I want the date on the horizontal axis to adjust to the correct dates.<o></o>
Does anybody have a solution for this?<o></o>
I don’t know if the size of the data has anything to do with it. To give an indication, I have a bout 30 product groups and +-600 daily entries per group.<o></o>

 

[Andrew Poulsom]

Please post the actual original formula, by copying it and pasting it into a reply. You can't have done that already because range names can't contain spaces and indirect isn't capitalised.

 

[The_Ant]

This is my formula (The Sheet name have to be in the formula, otherwise it doesn't work):

=OFFSET(INDIRECT(VLOOKUP(Stockview!$C$2,ADMIN!$A$2:$C$29,3,0)),MATCH(Stockview!$D$2,INDEX!$A$5:$A$1000,0),,MATCH(Stockview!$D$3,INDEX!$A$5:$A$1000,0)-MATCH(Stockview!$D$2,INDEX!$A$5:$A$1000,0))

The Vlookup formula, translates the product to a range that indirect can read. All product have a named range.

Let me know if more information is needed.

 

[Andrew Poulsom]

Are you saying that formula works in Excel 2003 but not in Excel 2007?

 

[The_Ant]

Not totally. The formula works in 2007. But i want to change the start date, i get the error. It says that the formula is incorrect, where nothing has changed in the formula.

 

[Andrew Poulsom]

Are you saying that you can change the start date in Excel 2003 without getting an error? In what way are you changing the formula you posted?

 

[The_Ant]

In 2003 I had no problems with the formula. I can't see how it reacts in 2003, as i only have the 2007 version availible.
In 2007, i seem to get an error when changing the start date. I've tried a smaller range and it seems to work fine. However, when increasing the range i get an error that the formula is not correct. However all i've done is increased the range.
The I don't want to change the formula, i want to change the cell "Start date" in "Stockview!$D$2". I can't seem to see the error in the formula. I don't know if the formula is too long for excel?

 

[Andrew Poulsom]

What's in these ranges?

Stockview!$C$2
ADMIN!$A$2:$C$29

 

[The_Ant]

Stockview!$C$2 = Product name
ADMIN!$A$2:$C$29 = Table with product name to range name

e.g.

<TABLE style="WIDTH: 139pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=184><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 71pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=94>Product name</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=90>RANGE Name</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Alpha</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">IndexAlpha</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Beta</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">IndexBeta</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Charlie</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">IndexCharlie</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Delta</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">IndexDelta</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Echo</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">IndexEcho</TD></TR></TBODY></TABLE>

The Index name coinside with range names for each product.