Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bringing conditional data from another worksheet
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bringing conditional data from another worksheet
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 -- Jay "Nikki" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bringing conditional data from another worksheet
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" wrote: 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 -- Jay "Nikki" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bringing conditional data from another worksheet
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 -- Jay "Nikki" wrote: 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" wrote: 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 -- Jay "Nikki" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
bringing data together from two worksheet for comparison | Excel Worksheet Functions | |||
Bringing conditional data from another Worksheet | Excel Worksheet Functions | |||
bringing data in from another worksheet | Excel Discussion (Misc queries) | |||
bringing data in from one worksheet to another | Excel Worksheet Functions | |||
Bringing in information from one worksheet to another dependent on the 2nd | Excel Programming |