• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Please help

Y

Yitzhackm

Member
I’m currently working on a project, I have four worksheets named: Roster, active, term and other using this array formula =IF(COUNTIF(Roster!$C:$C,"Active")>=ROWS($A$1:B1),INDEX(Roster!A:A,LARGE((Roster!$C$1:$C$999="Active")*ROW(Roster!$C$1:$C$999),COUNTIF(Roster!$C:$C,"Active")-(ROWS($A$1:B1)-1))),""). The workbook needs some modifications and i was wondering if i can modify the formula so the data shows in both the "active" and "Other" tab? Thanks
 
Hi Yitzhack ,

I have not understood your question ; let me get it clear.

1. You have 4 tabs , Roster , Active , Term and Other.

2. I assume the formula you have posted is entered in the Active tab.

3. I assume that the text "Active" is used in the formula because the formula is entered in the Active tab.

If all of the above is correct , then :

If you want similar results on the Other tab , you can do two things :

a. Copy and paste the same formula in the Other tab ; the results on the two tabs , Active and Other , will be identical.

b. Copy and paste the formula , but then change all instances of "Active" by "Other" ; the results on the two tabs will be similar but not identical.

Can you clarify ?

Narayan
 
Narayan,

sorry for not clarifying, you are correct, all the information is entered into the "Roster" tab. the formula returns data on the other tabs based on the text on the formula "Active" "term"or Other" that is changed base on the tab. my problem is that now i need the formula to return data in both tabs, Active and Other" i have tried to add "&other" into the formula but it doesn't work. perhaps because i am doing it wrong. i hope this clarify. Thanks in advance.
 
Please help....does not say anything about your problem and does not let those viewing know if they could help !
Just about as bad as, "How to", "Hello", "Urgent", "Does not work" , and the classic, "Excel problem" (that's why we are all here) all have been used as post titles, all say nothing.
 
Thanks for the feedback and please acept my apology, this is my fist time posting. i tried to edit the title but couldn't find the way. Can it be edited or you would recommend to close this thread and start a new one all over again?
 
Hi Yitzhack ,

Sorry , but I am still not clear ; let me explain.

The initial formula you have posted , can you clarify in which tab it will be entered ?

Secondly , when you say you want both "Active" and "Other" , do you mean that in which ever tab the formula is entered , it should return data if there is either "Active" or there is "Other" in column C in the Roster tab ?

Narayan
 
Narayan,

The initial formula is entered into the "Active" tab, then I do the same for the other sheets, changing "Active" in the formula as needed. The information on the roster includes Name, Supervisor, "ACTIVE", schedule and some other personal information. the Active tab collects the information based on the text "Active" in column C. once the employee is termed the text in column C is changed to TERM and the data is sent from "Active" to "Term". based on the sensitivity of some of the information, Some information of the active employees needs to be in the "Active"tab and some other (same employee) in the Other tab. PLease note that the same formula is used from B1 to E99

I really thank you for taking the time.
 
Hi Yitzhack ,

Let me try and understand :

1. You say that the Roster tab can have either "Active" or "Term" in column C ; as long as it is "Active" , information for that employee will appear in the Active tab ; as and when you change the data in column C from "Active" to "Term" in a cell , the corresponding information for that employee will no longer appear in the Active tab ; instead it will now appear in the Term tab. Is this correct ?

If it is , then if you have information for one employee , some of which should appear in the Active tab , and some of which should appear in the Other tab , the following formula :

=IF(COUNTIF(Roster!$C:$C,"Active")>=ROWS($A$1:B1),INDEX(Roster!A:A,LARGE((Roster!$C$1:$C$999="Active")*ROW(Roster!$C$1:$C$999),COUNTIF(Roster!$C:$C,"Active")-(ROWS($A$1:B1)-1))),"")

needs to be changed only in the portion which is highlighted.

Instead of column A , if you put column E , then the information from column E in the Roster tab will appear.

Narayan
 
Screen Shot 2013-09-15 at 10.36.39 AM.png

The first one represents the Roster Tab. as you can see the Status (column c) dictates where is information is going to be sent.

The second is the active tab. as you can see, the roster shows only Yitzhack and Patricia as active employees.

Third is Sandy (Term) and Fourth is Joseph (Other). you can see the formula reflects the tab on which they currently are. what needs to happen I need Yitzhack in the Active tab as he is an active employee but also i need Yitzhack in other to display some other information unrelated to the active tab. hope this makes sense.
 
Hi Yitzhack ,

Can you try this ?

=IF(SUM(COUNTIF(Roster!$C:$C,{"Active","Other"}))>=ROWS($A$1:B1),INDEX(Roster!A:A,LARGE((Roster!$C$1:$C$999={"Active","Other"})*ROW(Roster!$C$1:$C$999),SUM(COUNTIF(Roster!$C:$C,{"Active","Other"}))-(ROWS($A$1:B1)-1))),"")

This is to be entered as an array formula , using CTRL SHIFT ENTER.

Narayan
 
You must log in or register to reply here.
Back
Top