Referencing data from another sheet

[Janet_B]

Hi,

I have data on a sheet like this:

Book2.xls
	K	L	M	N
47		0
48		0
49	10	1
50	6	1
51	8	1
52	1	1
53	3	1
54	17	1
55	13	1
56	12	1
57	18	1
58		0
59		0
60		0
For O-C Eq

I use a date range (which can vary) in column L:L to indicate if to use the data or not in column K:K. The data is always together ie: no blank cells between the data in K:K.

On another sheet I would like the values in K:K to start from A1 down. For example in the above, A1 would be 10, A2 would be 6 etc etc

Thanks Janet.

 

[HalfAce]

Hello Janet,
I've got a couple of questions regarding your post.
(1) Are you looking for a VBA solution or a formula/sheet function?
(2) Are all the 1 values in column L going to be consecutive, or will there be zeros and 1s within the same date?
(3) What are your sheet names?

 

[barry houdini]

You say "date range" in column L. Is that a typo or do you actually have dates in column L not 1s as shown in your example?

Presumably, in your example, A1 would be 10, A2 6 etc. then presumably that would go down to A9 with 18 then should A10 downward be blank?

 

[Janet_B]

Hi and thanks HalfAce,

(1) Are you looking for a VBA solution or a formula/sheet function?

I'd prefer a formula/sheet function but VBA would be fine too!


(2) Are all the 1 values in column L going to be consecutive, or will there be zeros and 1s within the same date?

Consecutive only.

(3) What are your sheet names?

Sheet 1 and sheet 2

Thanks again.

Cheers Janet

 

[Janet_B]

You say "date range" in column L. Is that a typo or do you actually have dates in column L not 1s as shown in your example?

Sorry yes it was a bit vague!

The 1's in column L reference 2 popup calendars (date range). The 1's indicate which values in column K are in the date range.

Presumably, in your example, A1 would be 10, A2 6 etc. then presumably that would go down to A9 with 18 then should A10 downward be blank?

Yes.

Cheers Janet[/quote]

 

[Janet_B]

Done loads of searching on this and I'm not really sure the best way to go with this. I found some code that removes blank cells but it removed everything!

Cheers Janet

 

[HalfAce]

Good morning Janet,
You should be able to use the AutoFilter function, perhaps like this. (Will run from any sheet.)

Sub Demo()
Application.ScreenUpdating = False
With Sheets("Sheet1")
    .Columns("K:L").AutoFilter Field:=2, Criteria1:="1"
    .Columns("K").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").[A1]
End With
Sheets("Sheet1").AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

Hope it helps.
Dan