Offset function to selct specific data from another work sheet

[Krays23]

HI

Im trying to follow The Excel Kids video in you tube using the offset function. if you could take a moment to view the video at 3 minutes 5 seconds it will be a lot clearer than trying to explain it in text.

https://www.youtube.com/watch?v=PbbSF0oSres&index=4&list=PLWNJy3iJaYzBPNbionzdfoyM1Bt5NEiHl

I can get it to work fine how he suggests however there is one difference that I cant get to work.

In the work sheet that contains my main data. I have a colum named CNC machine this is machines numbered 1 to 4 what I want is my offset to only show values from a specified CNC machine for example only show CNC 4 with the current formula from excel kid it it shows all machines.

The offset im using is this and this is used in a worksheet specific to CNC 04 , Each machine will have its own work sheet showing only data for that machine.

Basicallly im using this offset to enable a scrolling graph based on data from data in CNC data input the issue is it shows all data for all machines and not just 4.

=OFFSET('CNC data input'!A7,'CNC 04'!$Z$7,,)

How can I do that ?

Sorry this is not so clear but a lot easier if you see the video

Hoping for your help as always.

Dan

 

[gallen]

Hello,

I can't view the video but just to explain, Offset simply takes a range object and references a range OFFSET by a number of rows and columns. Offset(1,1) would get the cell 1 row down and 1 column to the right.

So for example

s = Range("A1").Offset(1,1).Address
MsgBox s

Would display a message box with "$B$2"

Unless OFFSET is user written procedure that is explained in the video? If so Ignore this message!

 

[Krays23]

Hi I get that and I want that however I need it to filter colum "CNC Number" in my "Maindata" table to only offset and show values "4" when I click the scroll bar which is using the offset.


The below are headings in my work sheet CNC data input Maindata table

=OFFSET('CNC data input'!A7,'CNC 04'!$Z$7,,)

A7 refers to the cell 27/02/2017 below and returns that cell value in my other worksheet where the offset function is stored and linked to a scroll bar. however when I click the scroll bar it returns the A8 colum with 3 in the CNC Number colum I actually want it to show me just the dates with 4 in the CNC number colum as I keep clicking the scroll bar
[TABLE="width: 1006"]
<tbody>[TR]
[TD][TABLE="width: 1006"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Day[/TD]
[TD]Week[/TD]
[TD]CNC number[/TD]
[TD]Control number[/TD]
[TD]Parts Cut[/TD]
[TD]Target to cut[/TD]
[TD]Delta cut[/TD]
[TD]Actual WIP[/TD]
[TD]Ideal WIP [/TD]
[TD]Delta WIP[/TD]
[TD]Daily WIP level[/TD]
[/TR]
[TR]
[TD]27/02/2017[/TD]
[TD]Mon[/TD]
[TD]9[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]25[/TD]
[TD]-25[/TD]
[TD]161[/TD]
[TD]40[/TD]
[TD]121[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]28/02/2017[/TD]
[TD]Tue[/TD]
[TD]9[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]25[/TD]
[TD]-25[/TD]
[TD]199[/TD]
[TD]40[/TD]
[TD]159[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]28/02/2017[/TD]
[TD]Tue[/TD]
[TD]9[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD]5[/TD]
[TD]25[/TD]
[TD]-20[/TD]
[TD]203[/TD]
[TD]40[/TD]
[TD]163[/TD]
[TD]42[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]