Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.worksheet.functions
|
|||
|
|||
Bringing conditional data from another Worksheet
It appears that your CC numbers are in ascending order.
Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =IF(ROWS($1:1)<=COUNTIF('Team Numbers'!D$2:D$8,"0"),SMALL(IF('Team Numbers'!D$2:D$80,'Team Numbers'!A$2:A$8),ROWS($1:1)),"") Copy down until you get blanks Biff "Nikki" wrote in message ... 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.worksheet.functions
|
|||
|
|||
Bringing conditional data from another Worksheet
If I have understood correctly:
in "Team Numbers" : in B2: =if(D20,A2,"") Copy down -------------------------------------------------------------------------------------- In "Site Allocation" In a spare "helper" column ( I have used column F): in F2: =IF('Team Numbers'!$D2<"",ROW(),"") Copy down for as many entries (or more to allow for future additions) you have in column A of "Team Numbers" In A2: =IF(ROW($A1)COUNT($F:$F),"",INDEX('Team Number'!A:A,SMALL('Site Allocation'!$F:$F,ROW(A1)))) Copy across to column D and down as far as needed for future entries. You can hide column F or change to (say) column IV. HTH "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 in from another worksheet | Excel Discussion (Misc queries) | |||
bringing data in from one worksheet to another | Excel Worksheet Functions | |||
Bringing data in one sheet | Excel Discussion (Misc queries) | |||
Bringing together data | Excel Worksheet Functions | |||
Bringing Last line of worksheet to a Summary page | Excel Worksheet Functions |