Bringing conditional data from another worksheet

[Nikki]

Hi,

I have a table in on worksheet called 'Team Numbers' that has the following:
CC Team No Deapartment L4 L3
11 11 Finance 9 2
14 4 IS 4
15 4 IS 4
16 0 Networks
31 0 HR
211 10 Networks 10
213 7 Networks 7

I have another worksheet called 'Site Allocation'
In the sheet I want to have a formula that does something like this:
In 'Team Numbers' look at first row in column 4 (L4) if this is >0 then put
the number from column 1 (CC). If the number in column 4 is 0 or blank then
I don't want it to put anything. I then want it to do the same thing again
but for the second row.
The list that appears in the 'Site Allocation' sheet needs to only have the
CC's listed that have numbers in L4 but I don't want any gaps.
e.g
11
14
15
211
213

Any ideas?

 

[Jay]

Hi Nikki -

Two methods come immediately to mind to achieve your goal. The first would
use an advanced filter to copy the 'CC' field results that meet the criterion
to an output range on the 'Team Numbers' sheet and then use formulas on the
'Site Allocation' sheet to refer to the output range. However, this is not
my preferred choice.

My preference is to use the following visual basic procedure. Copy it to a
standard module, modify as necessary (or repost for further instructions),
and test run it.

The procedure assumes the following (adjust as needed):
The worksheet "Site Allocation" exists before the procudure is run.
The output range on the "Site Allocation" worksheet starts in cell A1.
The table begins in cell A1 of the "Team Numbers" worksheet.

Sub Nikki()
Dim ws1, ws2 As Worksheet
Dim tbl As Range

Set ws1 = Worksheets("Team Numbers")
Set ws2 = Worksheets("Site Allocation")
Set tbl = ws1.Range("A1").CurrentRegion
ws2.Range("A1") = "CC"

For Each itm In tbl.Columns(4).Cells
If IsNumeric(itm.Value) And itm.Value > 0 Then
os = os + 1
ws2.Range("A1").Offset(os, 0) = itm.Offset(0, -3)
End If
Next 'itm

End Sub

 

[Nikki]

Thank you. I have tried that with a limited sheet and it works fine. Just
one extra part that would be handy:

If I adjust the amounts in 'Team Numbers' by adding a value to a CC that
didn't previously have on then it does add that to the list is 'Site
Allocation'. BUT if I delete a value for a CC then it shortens the list in
'Site Allocation' but still leaves the last lines at the bottom. Is there a
way to get the visual basic code to delete these lines?

Thanks

Nikki

 

[Jay]

Hi Nikki -

This version clears a contiguous list of CC's in column A before making the
new list. Let me know if adjustments are needed.

Sub Nikki()
Dim ws1, ws2 As Worksheet
Dim tbl As Range

Set ws1 = Worksheets("Team Numbers")
Set ws2 = Worksheets("Site Allocation")
Set tbl = ws1.Range("A1").CurrentRegion
ws2.Range("A1") = "CC"

'Clear pre-existing data from output range
If ws2.Range("A1").Offset(1, 0) <> "" Then
ws2.Range("A1").End(xlDown).ClearContents
End If

For Each itm In tbl.Columns(4).Cells
If IsNumeric(itm.Value) And itm.Value > 0 Then
os = os + 1
ws2.Range("A1").Offset(os, 0) = itm.Offset(0, -3)
End If
Next 'itm

End Sub