0

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?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Shaw_9
  • 3
  • 2

1 Answers1

1

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Unfortunately the error code states that function is not valid – Shaw_9 Jun 25 '19 at 09:05
  • @Shaw_9 If you are using it in a chart use named ranges for the formula as described here: https://stackoverflow.com/a/18275292/3219613 and use the named range in the chart source. Also see my edited answer. – Pᴇʜ Jun 25 '19 at 09:37
  • This looks to be exactly what I am hoping to achieve. I've created the named ranges, however I am unable to reference them in the series values. I am trying to enter ='Retrieve Data'!PulseCount with Retrieve Data being my open sheet, and PulseCount being a named range – Shaw_9 Jun 25 '19 at 11:07
  • @Shaw_9 try `=PulseCount` you probably added a global name instead of a worksheet local name. – Pᴇʜ Jun 25 '19 at 11:09
  • I've checked, the scope of the name is set to workbook, I've also tried =PulseCount to no effect – Shaw_9 Jun 25 '19 at 11:18
  • @Shaw_9 Strange it should work. Are you sure the values in `W6` and `X6` are valid. It takes the values out of the sheet `Retrieve Data` if the name is in its scope. Make sure this is correct. You can also check the address of the name by using `Debug.Print Worksheets("Retrieve Data").Range("PulseCount").Address` – Pᴇʜ Jun 25 '19 at 11:21
  • Currently the reference of the name tag is; =OFFSET(INDIRECT(“Retrieve Data!$D$”&MATC H(Retrieve Data!$W$6,Retrieve Data!$D:$D,0)),0,0,Retrieve Data!$X$6) – Shaw_9 Jun 25 '19 at 11:38
  • Either you did not copy/paste this line or it is full of typos! You use the wrong quotes `“Retrieve Data!$D$”` only the normal quotes are allowed `""` • If a sheet name contains a space it must be wraped in apostrophes`'Retrieve Data'` or it will fail. Try `=OFFSET(INDIRECT("'Retrieve Data'!$D$" & MATCH('Retrieve Data'!$W$6,'Retrieve Data'!$D:$D,0)),0,0,'Retrieve Data'!$X$6)`. Note that I just checkt the syntax I did not check if the formula itself is correct. • Actually I don't get why you did not use the formula I suggested? – Pᴇʜ Jun 25 '19 at 11:42
  • full disclaimer, my internet access and work terminal are separate networks, work terminal is on an isolated automation network, so copying and pasting isn't an option. – Shaw_9 Jun 25 '19 at 11:52
  • @Shaw_9 Then you actually need to be more precice when typing. Every little typo matters. We cannot help with *"I use approximately this code"*. • Did you try the formula I suggested? – Pᴇʜ Jun 25 '19 at 11:53
  • 1
    apologies this is all very new to me, yes that's worked. Thank you very much – Shaw_9 Jun 25 '19 at 13:20