Referencing only certain cells in a table

[yofnik]

Hello,
I posted this question earlier in the execl.worksheet.functions group,
but did not get an answer. I apologize for the repost.

I need some help referencing certain cells from one worksheet in
another. Here is an example of what I am trying to accomplish:

Worksheet 1:
Item # Name Value
1 item1 100
2 item2 0
3 item3 0
4 item4 54
5 item5 0
6 item6 0
7 item7 70

I need to create a separate table in Worksheet2 that as only the NON
ZERO values from the table above in Worksheet 1. So the table in
Worksheet 2 should look like:

Item# Name Value
1 item1 100
4 item4 54
7 item7 70

I am looking for a function or formula to do this automatically for the
user of the spreadsheet I am creating. Doing a filter, then copying,
and pasting is not what I need. I need the data to appear in Worksheet2
automatically for the user based on the contents of the table in
Worksheet1. How do I accomplish this?

Thanks in advance for your help.

 

[dolivastro]

There are two possibilities.

1. Use this formula in the cell on sheet 2:
=IF(Sheet1!$C1<>0,Sheet1!A1,""). No copy and paste it to the other
cells. The formula will adjust as it is pasted. For example,
"Sheet1!A1" will change to "Sheet1!A2", and so on.

This will give you a matrix with blanks where 0 appeared.


2. Write a VBA module to do the trick. This is probably the better
approach, and the one I would take. But you need to know VBA to get
the job done.

Dom

 

[yofnik]

The whole point of doing this is to remove the blanks where 0 appeared.
So I guess my only option is to do this in VBA. Time to roll up my
sleaves I guess. Has anyone done anything similar that they can share
to help get me started?

 

[kounoike]

This is a macro doing almost the same as what you did manually, doing a
filter, then copy/paste etc.
copy the code below to standard module. after selecting your Worksheet 1,
run myfilter, then this will create none zero value in Worksheet 2.
assuming item# in A1, Name in B1, Value in C1 and the name of Worksheet 2 is
Sheet2.

Sub myfilter()
Dim srcsheet As Worksheet
Dim dstsheet As Worksheet
Dim n As Long
Const itemad = "a1" 'change - item# address
Const valad = "c1" 'change - value address
On Error Resume Next
Application.ScreenUpdating = False
Set srcsheet = ActiveSheet
Set dstsheet = Worksheets("Sheet2") 'change - Worksheet2
n = Range(valad).Column - Range(itemad).Column + 1
dstsheet.Cells.Clear
Range(valad).AutoFilter Field:=n, Criteria1:=">0", Operator:=xlAnd
srcsheet.AutoFilter.Range.Copy destination:=dstsheet.Cells(1, 1)
srcsheet.AutoFilterMode = False
Application.EnableEvents = True
End Sub

if you want to update table in Worksheet 2 automatically when you change
values of table in Worksheet 1, copy the code below to Worksheet 1
module(Sheet1?, not sure), not standard module.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
myfilter
Application.EnableEvents = True
End Sub

keizi

 

[yofnik]

GREAT! Thank you so much.

This is a macro doing almost the same as what you did manually, doing a
filter, then copy/paste etc.
copy the code below to standard module. after selecting your Worksheet 1,
run myfilter, then this will create none zero value in Worksheet 2.
assuming item# in A1, Name in B1, Value in C1 and the name of Worksheet 2 is
Sheet2.

Sub myfilter()
Dim srcsheet As Worksheet
Dim dstsheet As Worksheet
Dim n As Long
Const itemad = "a1" 'change - item# address
Const valad = "c1" 'change - value address
On Error Resume Next
Application.ScreenUpdating = False
Set srcsheet = ActiveSheet
Set dstsheet = Worksheets("Sheet2") 'change - Worksheet2
n = Range(valad).Column - Range(itemad).Column + 1
dstsheet.Cells.Clear
Range(valad).AutoFilter Field:=n, Criteria1:=">0", Operator:=xlAnd
srcsheet.AutoFilter.Range.Copy destination:=dstsheet.Cells(1, 1)
srcsheet.AutoFilterMode = False
Application.EnableEvents = True
End Sub

if you want to update table in Worksheet 2 automatically when you change
values of table in Worksheet 1, copy the code below to Worksheet 1
module(Sheet1?, not sure), not standard module.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
myfilter
Application.EnableEvents = True
End Sub

keizi