Graph Axis X Not Showing Value but Data In Graph

[shelby55]

Hi

I am using Excel 2003.

I have a graph that has values based on named ranges.

Value = offset(Graph_Data!$X$9,Graph_Data!$X$5,0,Graph_Data!$X$6)
Category X axis labels:=offset(Graph_Data!$B$9,Graph_Data!$B$5,0,Graph_Data!$B$6)

On this workbook there is a named ranged data validation for the user to select a certain date and the graph will show 20 weeks of data with the last data point being the date/week chosen.

I say date/week because the raw data sheet has columns for week number, date, and week ending date. If 7 days are in the period selected then week ending date is used, otherwise the date is used.

On the graph_data worksheet the date to show has the code of (for week ending date or date):

=IF(C9=7,VLOOKUP(A9,'Raw Data'!$DM$3:$DO$1094,3),IF(C9<7,VLOOKUP(A9,'Raw
Data'!$DM$3:$DO$1094,2),""))

The graph works fine, showing all x axis labels when the date selected is not the last one on the raw data page. But if I choose the last date of raw data then the data for that date is displayed but the x axis label only for that date is blank. However, on the graph_data worksheet there is a date displayed.

I've even selected source data for the graph and selecting each of the axis values including x axis shows that the correct columns/values are what is being defined by the named range so I'm not sure if a value is there why it's not being displayed.

Can anyone please advise what the issue might be? Thanks very much.

 

[SkipVought]

what values are in these reference when you pick...
[tt]
Date Date
other than Last Last
Graph_Data!$X$9, |
Graph_Data!$X$5, |
Graph_Data!$X$6 |
|
Graph_Data!$B$9, |
Graph_Data!$B$5, |
Graph_Data!$B$6 |
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

The "date other than last" should only affect the last week of data in the raw data sheet. Week ending is Sunday and the database I'm using only has to July 17 right now so July 12 to 17 should show their dates and all others will show weekending but they are still showing some date so not sure why it's not showing in the graph..especially when the data for that date is in the graph.

Having said that, July 12 to 17 all show July 17 on the graph_data worksheet (instead of their respective dates) from the code of:

=IF(C9=7,VLOOKUP(A9,'Raw Data'!$DM$3:$DO$1094,3),IF(C9<7,VLOOKUP(A9,'Raw
Data'!$DM$3:$DO$1094,2),""))

[tt]
Value | Actual Date | Display Date
Graph_Data!$X$9, 72 | July 14 | July 17
Graph_Data!$X$5, 57 | |
Graph_Data!$X$6 62 | |
| |
Graph_Data!$B$9, Feb 28| |
Graph_Data!$B$5, 1 | |
Graph_Data!$B$6 20 (is actually a formula: if(count(B9:B29)>20,20,Count(B9:B29)). This is so it can change with whatever date the user selects and fill in the last 20 weeks.
[/tt]

Note that the values for x,y, and z are on graph_data workheet which only has the date OR the week ending date. So I changed above to show actual date, date on graph (as above they should all be end of week date except July 12 to 17).

 

[SkipVought]

The way that OFFSET works...
[tt]
arg1: Cell anchor ref
arg2: # rows offset from anchor ref for return range to start
arg3: # columns offset from anchor ref for return range to start
arg4: # rows in the return range (default 1)
arg5: # columns in the return range (Default 1)
[/tt]
your arg4 corresponding values are 62 & 20. They need to be IDENTICAL VALUES (range rows) for the chart to work.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

The 20 in B6 is to define the number of rows to use in the graph via the named ranged. So

offset(Graph_Data!$B$9,Graph_Data!$B$5,0,Graph_Data!$B$6)

is starting at B9,number of rows to apply comes from B5 so 1,number of columns is 0, number of returned cells is B6 which equals 20.

So I know that the data is returning correctly from the named ranges because selecting from the graph shows me the range and it's picking the right values. Plus the issue isn't the data in the graph, it's the labels i.e. the graph shows 20 points of data and only 19 x axis labels.

I thought this only happens when the date selected in the summary is the last item in the list BUT I did a test by removing July 12 to 17 leaving only dates that compose a full week. When I do that, the data displays correctly even for July 11 which is the last in the raw data range and the axis label showing is July 11.

So it doesn't like my formula for the axis...though I don't understand why a date shows up in graph_data worksheet for that item but doesn't display in the graph.

 

[shelby55]

Hi Skip

I figured it out....I'm stupid! The raw data is one line per day but the rest of the graph is weekly. So even though it's in the middle of the week, it will still have a "end week date" assigned which is what I need to be reporting on. Once I changed that all is good.

Sorry Skip!