dynamic charts with missing data in chart range

Formic Nether

New Member
Joined
Jun 5, 2012
Messages
20
Hello

So, I'm working with a raw data set that I have made, and I frequently update the data whenever I collect data from sources. The first two rows of the data set are frozen, so I can have some semblance of what my data titles are. Using the data, I have made several charts. Now, I know I should have realized that I needed to do some dynamic charting in order to not make my life miserable whenever I update my data set. However, now I'm in this predicament in which I do have a data set, but there is a large chunk that is missing.

I was looking at this website for dynamic charting, but the first thing I need to do is to define the names. The only thing that I know that I have no missing data is the date column because it is predefined to be every 15 minutes starting from my first data collection. The rest of the data may or may not have missing data.

Is there still any possibility of doing any dynamic charting?

Any assistance is appreciated.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Well, apparently, I solved my own problem after doing some heavy experimentation.

The answer is that I can do dynamic charting. In order to solve the problem of missing data in the data set, I need to ensure that there is a consistent column in which I will never have any lost data. Apparently that is the date/time column. Using this as the reference value, I can then use the OFFSET formula for the following formula:

Code:
date=OFFSET('Sheet1'!$(date_column)$(starting_date_row),1,0,COUNTA('Sheet1'!$(date_column):$(date_column))-1,1)

data1=OFFSET('Sheet1'!$(data1_column)$(starting_data1_value),1,0,COUNTA('Sheet1'!$(date_column):$(date_column))-1,1)

This can be verified by going to the name manager and checking how the name references which column,row. It should refer to the same number of rows as the date column.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top