Chart using data range from address function

[plokplok]

Hi All,

Nearing the end of my journey to create a Graph that updates itself based on if data exists.

Background:
I have 2 rows of finance over time. However only some of those cells have data.
I have successfully used index and address functions to work out the address of where data starts and ends.
For reference:
=CELL("address",INDEX(CT29:IQ29,MATCH(TRUE,CT29:IQ29<>0,0)))
=CELL("ADDRESS",INDEX(CT29:IQ29,MATCH(MAX(CT29:IQ29),CT29:IQ29,0)))

This practically yields me:

Start	End
$DC$29	$DG$29

<colgroup><col><col></colgroup><tbody>
</tbody>

Now I am trying to create a line graph that will plot the results of DC29:DG29.
Note that I cannot simply reference the cells directly as my results will update on a weekly basis, hence the desire to have the graph automatically update its data / timescale based on these fields.


I have tried using the indirect formula to set a range, however this does not work. Anyone have any non-VBA workarounds?

 

[plokplok]

Alternatively if someone knows a way to have excel line chart select a whole row of data, but only plot where values exist.
(I have looking into the option of Hidden and Empty Cells, however there doesnt appear to be an option to prevent 0's from appearing on the series timeline)

IE:
0,0,0,0,1000,2000,3000,5000,0,0,0,0
In the above example, only the 1000 > 5000 series would show on the line graph. All remaining zeros would not be shown, ideally creating a clean graph.

 

[plokplok]

Re: Chart using data range from address function - Solved

So it looks like this may have been a hard request, but I have managed to solve it for reference if anyone else tries to do the same thing.


Steps i used to resolve:
1: Rather then use address formula of start and end of data dates, I used a helper sheet. I referenced the financial data across time and put it into a table going down with Index function, ensuring 0s show as NA.

2: I then added an extra coloumn that validates if the row has financial data with "=OR(ISNUMBER(B4),ISNUMBER(C4))"

3: Using this test coloumn, i then sorted it only when values are true. This provides a subset of dates with aligned finances that show if they do not equal 0.

4: I then created a chart of the whole coloumn, which only plots the dates and data where a value exists. NA gets ignored.

5: i wanted this graph to automatically update when new financials come in. I had to give in here and use a very simple VBA macro that auto updates a filtered list whenever data changes.
"Private Sub Worksheet_Change(ByVal Target As Range) Sheets("Hidden_Finance_Graph").AutoFilter.ApplyFilter 'update "Sheet1" to the name of your sheet with auto-filter
End Sub"


End Result: I have a graph of financial data over time that automatically updates its start and end date axis including data.
I have seen many articles / posts suggesting this cannot be done, however it can with the above method.

However if anyone can advise on how to get filtered data to update without VBA, that would be amazing as I could make this a non VBA sheet.