Hi,
I am working on Excel 2013. I am currently struggling to present my vertical column organised dataset on a bar chart. I have formulae that return different datasets depending on variables set by the user elsewhere, and this should feed into a bar chart with a varying number of categories depending on how many values the formulae return (e.g. the dataset could be 5, or 50 different items and their values to be shown in the chart). To achieve this the bar chart's data is sourced from the whole possible range in the dataset, so 500 spaces to be safe. These formulae extend all the way down ~500 or so rows, and is set to return blank values if the formula sees an error. It works perfectly and gives me exactly the datasets I require.
Other charts I have made with similar datasets automatically cull and do not show the blank rows, but whenever I try to create a chart from this dataset it squeezes everything down one end and shows the other blank rows up to 500. I have tried naming each column in the dataset in a dynamic fashion, using the OFFSET function to determine how far the chart's range should extend own the potential dataset depending on the number of values returned to limit them to just the data that the formulae and their user-input variables determine - I've verified I have set these names correctly externally to the chart by summing the data ranges referenced by the names and having them return the correct values, however the chart will not let me set these names as the reference values, either in the Select Data function or by selecting bars on the chart itself and manually altering the =SERIES function they use as their reference.
I have tried transposing the data to be organised in rows instead, tried changing the formulae, tried altering the way the chart treats Hidden and Empty Cells, to no luck whatsoever! If anyone can give me advice on how to either get the chart to ignore these rows made blank by formulae, or to set up a true dynamic range in the chart so it does not take the blank rows into account, that would be very much appreciated!
I am working on Excel 2013. I am currently struggling to present my vertical column organised dataset on a bar chart. I have formulae that return different datasets depending on variables set by the user elsewhere, and this should feed into a bar chart with a varying number of categories depending on how many values the formulae return (e.g. the dataset could be 5, or 50 different items and their values to be shown in the chart). To achieve this the bar chart's data is sourced from the whole possible range in the dataset, so 500 spaces to be safe. These formulae extend all the way down ~500 or so rows, and is set to return blank values if the formula sees an error. It works perfectly and gives me exactly the datasets I require.
Other charts I have made with similar datasets automatically cull and do not show the blank rows, but whenever I try to create a chart from this dataset it squeezes everything down one end and shows the other blank rows up to 500. I have tried naming each column in the dataset in a dynamic fashion, using the OFFSET function to determine how far the chart's range should extend own the potential dataset depending on the number of values returned to limit them to just the data that the formulae and their user-input variables determine - I've verified I have set these names correctly externally to the chart by summing the data ranges referenced by the names and having them return the correct values, however the chart will not let me set these names as the reference values, either in the Select Data function or by selecting bars on the chart itself and manually altering the =SERIES function they use as their reference.
I have tried transposing the data to be organised in rows instead, tried changing the formulae, tried altering the way the chart treats Hidden and Empty Cells, to no luck whatsoever! If anyone can give me advice on how to either get the chart to ignore these rows made blank by formulae, or to set up a true dynamic range in the chart so it does not take the blank rows into account, that would be very much appreciated!
