CODEWITHSUNDEEP
excelvbaexcel-formula
Shaw_9
Shaw_9

Reputation: 3

Data source start and end range variables

Hopefully the title is sort of correct...

To try to explain; I'm pulling a ton of data from a PLC into a spreadsheet. I've got a graph to display two series against time. Because of the sheer amount of data I'm limiting the series values with a range ='Retrieve Data'!$D$400:$D$2000 for example.

Both legend entries and the horizontal axis should be across the same range, currently to show a different window of time on the graph I need to edit these all individually to the same series range within their respective columns. Is there a way I can use two cells, one as start and one as end point in the formula, something like:

='Retrieve Data'!$D$("W6"):$D$("X6") where W6 is start of range and X6 is end of range?

Else would I need to look into use VBA?

Upvotes: 0

Views: 83

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You could use the INDIRECT function:

=INDIRECT("'Retrieve Data'!$D$" & W6 & ":$D$" & X6)

Note that INDIRECT is a volatile function and you should avoid heavy use because it can slow down a lot.

If you plan to use this in a chart data range it will not work because chart data ranges have to have a fixed address.

There is a way around this, and that's using named ranges

In name manager, define a name MyChartRange using the formula above. Then use =MyChartRange as address in your cart data.

Upvotes: 1

Related Questions