Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 31st 07, 05:14 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 184
Default 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   Report Post  
Old March 31st 07, 06:34 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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$8,"0"),SMALL(IF('Team
Numbers'!D$2$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   Report Post  
Old March 31st 07, 06:44 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,339
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
bringing data in from another worksheet stacy05 Excel Discussion (Misc queries) 1 February 23rd 07 07:39 PM
bringing data in from one worksheet to another stacy05 Excel Worksheet Functions 0 February 23rd 07 01:51 PM
Bringing data in one sheet Nad Excel Discussion (Misc queries) 0 June 26th 06 01:19 PM
Bringing together data Emma Hope Excel Worksheet Functions 4 May 25th 06 02:58 PM
Bringing Last line of worksheet to a Summary page Debbie Excel Worksheet Functions 8 January 26th 06 11:36 PM


All times are GMT +1. The time now is 07:15 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017