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

Date Day Week CNC number Control number Parts Cut Target to cut Delta cut Actual WIP Ideal WIP Delta WIP Daily WIP level 27/02/2017 Mon 9 4 0 25 -25 161 40 121 28/02/2017 Tue 9 3 0 25 -25 199 40 159 38 28/02/2017 Tue 9 4 5 25 -20 203 40 163 42 <tbody> </tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>