Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-29-2019, 06:33 PM
DOMASHNIA DOMASHNIA is offline Trying to select a row name by column date if a value is present Windows 10 Trying to select a row name by column date if a value is present Office 2013
Novice
Trying to select a row name by column date if a value is present
 
Join Date: Nov 2019
Posts: 1
DOMASHNIA is on a distinguished road
Default Trying to select a row name by column date if a value is present

So, as the title says....



Im making a staffing sheet for work. I would like EXCEL to generate a daily staffing sheet based on the people working on a given day.

the schedule is 6 weeks and the columns auto populate the dates when the new 6 week starts. The employee names are static. The employees work Am or PM shifts which are designated by A, P.

Im terrible with excel and ive finally gotten to a point where youtube can no longer help me (or i simply dont know what to search for)


So, if I input a date, I want just the people (on that date) with an "A" to populate to a group

and the same for those with a P.

ideas?



There would be 9 sheets (each for different units of this hospital) and then...given the day entered on the final "HOUSEWIDE" sheet....it would automatically fill in the spots.

i uploaded the excel file with the test names and "schedule" and how it should look on the housewide sheet
Attached Images
File Type: png Untitled.png (27.2 KB, 14 views)
Attached Files
File Type: xlsx Donald Project.xlsx (78.1 KB, 7 views)
Reply With Quote
  #2  
Old 12-01-2019, 07:44 AM
p45cal's Avatar
p45cal p45cal is offline Trying to select a row name by column date if a value is present Windows 10 Trying to select a row name by column date if a value is present Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

In the attached, on the Housewide sheet there are some formulae A8:B15 and A108:B115.
These formulae are committed to the sheet using Ctrl+Shift+Enter, not just Enter.
I leave you to enter similar formulae elsewhere in the sheet.
The formula is along the lines of:
Code:
=IFERROR(INDEX('2nd'!$B$5:$B$20,SMALL(IF(INDEX('2nd'!$C$5:$AR$20,0,MATCH($A$3,'2nd'!$C$4:$AR$4,0))="a",ROW('2nd'!$B$5:$B$20)-4,FALSE),ROW()-7)),"")
'2nd'!$B$5:$B$20 The list of names. Appears twice in the formula.
'2nd'!$C$5:$AR$20 The body of the table containing as, ps and blank cells.
$A$3 The date
'2nd'!$C$4:$AR$4 The dates at the top of each table.
-4 The 4 is one less than the top row of '2nd'!$B$5:$B$20
-7 The 7 is one less than the row that the topmost formula is in.

In each section of your Housewide sheet you can compose the formula in the first cell, then either:
array-enter it, and drag/copy down as far as you need,
or:
select that cell and the handful of cells beneath it, and array-enter the formula into all the selected cells at once. This will be lighter on resources. I've done this with B108:B115 (light blue shading).

It may not be the most elegant formula, it's just the one that came out right first.

I've highlighted in light green other adjusted cells; TODAY() has been replaced with a hard date so we can see some results.
I've also populated the sheets with fictitious names and a bunch of ps and as in the tables.

Tenet Health will be happy.
Attached Files
File Type: xlsx msofficeforums43955Donald Project.xlsx (92.6 KB, 5 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change font color if date in column F is prior to today's date. kevinbradley57 Excel Programming 1 12-05-2018 07:35 PM
Trying to select a row name by column date if a value is present Select all cells in a column , even when blank... Chayes Excel Programming 4 05-30-2018 10:35 AM
How to select multiple criteria from same column? LearnerExcel Excel 1 03-10-2018 07:41 AM
Trying to select a row name by column date if a value is present Count # of times "text" is in 1 column is specific date is in the other column ann.acornacchio Excel 4 12-31-2016 05:40 PM
Trying to select a row name by column date if a value is present Automatically enter date into a column and make that column read only Mr Davo Excel 1 10-29-2012 01:07 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:12 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft