• 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.

DATA REFLECTING ONLY AVAILABLE PERSONNEL

Status
Not open for further replies.
A

arslan_janjua2001

New Member
i have a data of personnel on one sheet, lets say SHEET 1, with information like available / leave / sick, etc. Now i want that SHEET 2 works in such a way that only the personnel who are available must be shown on SHEET 2. Any help on this?
 

Attachments

  • PER SHEET.xlsx
    10.3 KB · Views: 10
Make your range an Excel Table and use this table as data source for a Pivot Table which you will filter on " available".
After adding data, just click " Refresh" or add a small macro to do the job
 

Attachments

  • Copy of PER SHEET.xlsx
    14.6 KB · Views: 0
Hi Arslan,

See the attached file, you can change the status in cell A1 to see results.

Following Formula needs to be adjusted as per your actual file range:
=IFERROR(INDEX('SHEET 1'!$A$3:$C$99,AGGREGATE(15,6,1/('SHEET 1'!$D$3:$D$99=$A$1)*ROW('SHEET 1'!$D$3:$D$99)-2,ROW(A1)),COLUMN(A1)),"")

Regards,
 

Attachments

  • PER SHEET (1).xlsx
    11 KB · Views: 4
REALLY APPRECIATE IT KHALID
BASED ON YOUR SHEET, I HAVE MADE ANOTHER ONE WITH INCREASED NUMBER OF COLUMNS BUT IT ISN'T WORKING. NEED YOUR KIND HELP PLEASE.
 

Attachments

  • PER SHEET 2.xlsx
    12.9 KB · Views: 4
arslan_janjua2001
As You're a new member,
You have just read Forum Rules.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
You should reread those as soon as possible!
PLEASE DON'T SHOUT! We have big ears and will hear you just the same.
 
Just in case any reader needs to do something similar with MS Excel 365.
First, turn the source data into an Excel Table and Name it (e.g. tblStaff).
That makes references dynamic and independent of the actual positioning of the Table on the Sheet.

The formula is then
= FILTER( tblStaff, tblStaff[STATUS]="AVAILABLE" )
The formula is only placed in a single cell 'availableStaff' but it spills to provide the required records.

If one then needed to know the departments with staff availability one could reference the new range 'availableStaff#'
= UNIQUE( XLOOKUP( "DEPARTMENT", tblStaff[#Headers], availableStaff# ) )

I am so thankful that I need not develop traditional spreadsheets anymore! ;)
 
THANKS A LOT.
Peter Bartholomew said:
Just in case any reader needs to do something similar with MS Excel 365.
First, turn the source data into an Excel Table and Name it (e.g. tblStaff).
That makes references dynamic and independent of the actual positioning of the Table on the Sheet.

The formula is then
= FILTER( tblStaff, tblStaff[STATUS]="AVAILABLE" )
The formula is only placed in a single cell 'availableStaff' but it spills to provide the required records.

If one then needed to know the departments with staff availability one could reference the new range 'availableStaff#'
= UNIQUE( XLOOKUP( "DEPARTMENT", tblStaff[#Headers], availableStaff# ) )

I am so thankful that I need not develop traditional spreadsheets anymore! ;)
Click to expand...

THANKS A LOT
 
Status
Not open for further replies.
Back
Top