Pulling one of multiple headers in single column based on information below.

[TDavis94]

In the attached file, I am trying to pull the correlating header (department name), based on the names below each department (on the master schedule), into the daily staffing sheet (Column B). Ex. if Name 14 is on the staffing sheet, I would like Department 3 to automatically populate next to it in Column B, and change to Department 2 if the name changes to Name 8.

*Only Column A and J are used on the "Master Schedule" tab, and only Column B (under Department Header) is needing a formula in this sample sheet. I have already developed a formula to pull names to the desired areas based off of the scheduled shift on the Master Schedule.

Any help would be greatly appreciated!

[Glenn Kennedy]

I have a bad feeling that we are only getting half the story here.

I created a Named Range, imaginatively called Range (CTRL-F3 to view/edit):
='Master Schedule'!$A3:INDEX('Master Schedule'!$A:$A,MATCH('Daily Sheet'!A303,'Master Schedule'!$A:$A,0))

and then used this formula:
=IFERROR(INDEX('Master Schedule'!$A:$A,AGGREGATE(14,6,ROW(Range)/(ISNUMBER(SEARCH("Department",Range))),1)),"")

see file.

[TDavis94]

Thanks for replying Glenn! I'm not sure why you feel that only half the story is being given. I took a look at the file, and one of the cells went blank as I added and took away shifts from various people. I also realized that the search for department would not allow to change the department name to what is needed. A little more background is that I work at a hospital, and we have a residency program for new RNs. All of the residents report to the same person, but they would like to be able to easily see which RNs are in which department on a given day. I changed the department names in the file I attached to this message to more accurately reflect what is more likely to be used by this leader.

[Glenn Kennedy]

That is EXACTLY what I meant. I expected that you would tell me that the Department names were not going to be Department 1, etc.

However, the file you uploaded is the wrong one. It still refers to Department 1, etc. If the Dept. names are simply too generic... without any useful handle to differnetiate them from person's names... this is likely to get messy. The Depts are in the same column as the individuals. Can we use SOMETHING to differentiate them from names... a dash in front of the Department name, on the Master schedule, or something like that??

[Glenn Kennedy]

Also, post my file showing EXACTLY what you mean by;


"and one of the cells went blank as I added and took away shifts from various people."

[TDavis94]

My apologies. I wasn't sure that it would make a difference if I changed the names at first, that is until I saw how you were trying to solve this. I left the first two departments as "Department 1," and "Department 2" to show how one of the cells went blank on the daily sheet. The rest of the departments changed to the ones actually being used, and I added a dash to both sides as recommended. Doing it this way would allow us to add departments later if some were not getting residents this year. Your continued help is much appreciated!

[Glenn Kennedy]

I'm out for a walk. Back in an hour or so. Are you sure that your name formula is working? From memory it is referring to A3... a blank cell. I feel that the yellow shaded cells may be important, too. What are they for? Explsin the abbreviations.

[Glenn Kennedy]

I think I understand what your formulae were doing in the name columns now. However they were phrased a bit strangely. I have modified them. In particular, they look as though you had manually changed all the row numbers in the counter manually, all the way down the row. Also they were inconsistent in their starting point. If you want to dump all of the daily sheets and just keep one, that is now possible, as the formulae now identifies the shifts worked from the whole range on the Master sheet. So you can keep the master sheet populated on every day, if you so wish, select teh date in D300, and everything updates. I added a few shifts for the 27th. Overtype D300 with 27th and you'll see what I mean.

Basically, you can cut the daily sheet down to a single day and control what you display from a dropdown that could be put in D300. If you want to look forward, or backwards, the data are all there. If you DO cut it back, to a single searchable daily record,it will be so much easier for you to maintain.

Google "Excel relative and absolute cell references". If a formula is set up right, it can simply be dragged all the way down a column in a fraction of a second.

I changed my mind about using hypens before Dept names... for two reasons... they are a bit unsightly and hyphens may also be present in someone's name. So, instead I used a "Carriage return" character CHAR(10) which is invisible. So, the Dept names look correct, but if you look at Master shet, they are result of a formula. I renamed Depts 1 and 2 for testing. You can change them to whatever...

I did make a mistake in the Named Range, now fixed. I think the formula for the Department name is working correctly. So, try and break it. if it topples over, SHOW me where it's going wonky.

If you decide to go for the streamlined version, I'd be more than happy to set it up. it's only about 5 mins work... let me know.

[TDavis94]

The naming formula works by pulling the name for the 7a shift if the highlighted column has "A" in it, and for the 7p shift if it has a "P" in the column. There are 42 columns to the right of the name column, and the highlighted one is where the daily sheet currently being worked on identifies which name corresponds to "A" or "P".

I originally had 42 of the staffing sheets. I hid 41 of the daily sheets because this spreadsheet was modified from a previous unit based staffing tool, which is why the current one starts at A298. I wanted to make sure that I could get one sheet working correctly before updating the other 41.

You'll notice that the top of the highlighted column is "25" and "M", which stands for the 25th of the month and the day of the week (Monday).

=IFERROR(INDEX('Master Schedule'!$A$4:$A$92,AGGREGATE(15,3,(('Master Schedule'!$J$4:$J$92=$A$3)/('Master Schedule'!$J$4:$J$92=$A$3)*ROW('Master Schedule'!$J$4:$J$92))-ROW('Master Schedule'!$A$3), ROWS(A304:A305))),"")

Cell A3 (bolded in formula) on the daily sheet tab has "A", and B3 has "P" (currently hidden on the first individual daily sheet). This lets the formula know to index a name if the letter in column J on the master schedule matches the letter in A3 on the daily sheet.

The underlined portion of the formula, "'Master Schedule'!$A$3", tells it to start indexing below that cell (from what I was able to conclude). I found a formula that allowed me to index from a column without repeating the previously indexed cells, so that no name is repeated. That last part with "ROW('Master Schedule'!$A$3)" was the only part of the formula that I did not completely understand, but found that it worked how I needed it to.

I am unhiding the entire sheet, so that you can see all 42 of the daily sheets. The daily sheet that you are helping me with will also be highlighted to easily visualize the one needing work.

[Glenn Kennedy]

I know they're there. I unhid them myself to find what was in A3. My q is do you actually need any of the hidden sheets? Bar one, of course. Play with the sheet i posted.

[Glenn Kennedy]

Even if you decide to keep all 41...the sheet now selects only the date column selected in row 300. One fewer mamual change needed.

[TDavis94]

Strong work! I saw that you posted that last reply as I was replying myself. If I read your message correctly, It sounded like it would update by changing the date highlighted in yellow. When I tried to manually change it, everything went blank. I'll send you the sheet showing this. I think that the streamlined version would work for the person I am making this for. The individual departments print the entire schedules daily staffing sheets to put in a binder on the unit (for patient assignments, noting if nurses floated to other units, etc.).

Will adding rows, or deleting them, on the master schedule impact the formula that you created?

[Glenn Kennedy]

Its beer o'clock in lockdown Ireland. So, I'll produce another iteration for you to look at in my Wed morning.

When you say adding/deleting row, explain what you mean... or wait till tomorrow and try it out. If it goes belly-up there's always a fix!!

[TDavis94]

I'll test it out on the last sheet you sent me. Enjoy your cold one, or few! I appreciate all of your help with this!

[Glenn Kennedy]

Regarding the sheet going blank... You said:
"When I tried to manually change it, everything went blank."

Change which cell from what to what? You need to be specific. However, I suspect that you did not read what I said at Post 8:

"So you can keep the master sheet populated on every day, if you so wish, select the date in D300, and everything updates. I added a few shifts for the 27th. Overtype D300 with 27th and you'll see what I mean.

It had only been done in a limited way for demonstration purposes.

Looking at your sheet again now.

[Glenn Kennedy]

OK. You will see a few changes here. There are (I think) only 2 more things to do, but for those, I need your input.

The daily schedule hasn't changed that much. Big changes on Master schedule, though. We'll start there.

Cell A2. This defines the start date of the 6-week span of the sheet. Change it and (almost) everything else updates automatically for you (the exception is A5, and I'll explain that in a moment).

I moved the dates from row 4 to a column (AV), where they can be seen, instead of ##. Those dates are used auto fill the date and the weekday in rows 4 & 5. You don't need to do anything. The formulae do it for you.

If you change the value in A2 from 17/01/21 to whatever... the values in B4:Q5 update, as do the values in column AQ. The value in A5 does not. It's the day selected for the displaying daily sheet. However, if you click the dropdown in A5. you will see thta it runs for a six-week period from the date selected in A2.

Select a valid date from the dropdown in A5. You will see green highlighting appear in one of the columns B to AQ. The highlighted column is the column corresponding to A5.

Moving down the Master sheet, I made all the departmental blocks the same size... 10 rows. Excel LOVES uniformity. If you REALLY MUST change that, do so ONLY when the rest of the sheet is complete, as lack of uniformity messes up formula copy/pasting. It's so much easier if the blocks are all the same size.

I deleted the repeated date columns at page breaks and set Excel to repeat them automatically... again putting them in manually breaks up the uniformity of the data layout.

The helper columns (AV and AY&AZ) can be hidden, or moved, if desired.

I used a random number generator to populate the shifts on the master shedule... no I didn't type them all in manually!!

Now go to the daily sheet. It is all updated with the data for the day selected in MS A5. If you would prefer to select the "active day" from the daily sheet INSTEAD of from MS A5, let me know. It's a 30 second job to restore it.

Three things remain to be done.

1. So far there are only 2 shifts A & P. Clearly there are really 4. Can we standardise ther names: to either 1,2,3,4 or O,M,A,E (overnight morning afternoon evening)? Then I can adjust the formulae in Daily sheet to reflect the correct shift and hide/replace row 2 on the daily sheet).

2. then I will use DV to restrict entries on the MS to 1,2,3,4 or O,M,A,E (whichever you go for) to prevent mis-entry (e.g. the dreaded trailing space "A " instead of "A" which screws up so many fomulae).

3. Decide whether you want the DD the cntrols the date on Daily sheet should be on the Daily sheet or on the Master Schedule. I can see that there are advantages and disadvantages to bothaproaches. You choose...

Play with it, try to break it. No doubt there will be some bugs that I haven't noticed. Then reply to Qs. If there are no bugs it will take me 5 mins, tops, to finish it.

[TDavis94]

First of all, I love the new features on the master sheet! You are probably right I read the previous message incorrectly when manually changing the date that was in d300.

When I download the file, everything shows up correctly with the names and departments. The master sheet dates update just like you mentioned, and the individual date highlights the column like it should. So everything on that sheet appears to be working great, like it should.

The daily sheet seems to be where I am having issues. Following the instructions by selecting a date from the drop down in a5 of the master schedule, the date on the daily sheet changes in d4, and the names and departments disappear. This may be an issue on my side with the name formula that is used. I am also hoping that it is not just an error on my sheet or version of excel (2016), and that you will be able to replicate it on your own sheet. Even selecting 1/24/21 (the same date that was selected when opening the sheet) causes the names and departments on the daily sheet to disappear. I am attaching the file that you sent with only one adjustment made - selecting a different date from the drop down in a5 on the master sheet to show you what I see on the daily one.

As for the questions,

1. As mentioned previously, these sheets were based off of individual departments that have some associates working 4, 8, and 12 hour shifts. The individuals on this document should be only working 12 hour shifts, but wanted to keep the sheets uniform to the ones that the individual departments use.

2. I would restrict the entries to A, P, and R (R to show requested days off). The R would not need to carry any names or departments to the daily sheet, since they wouldn't be there.

3. I think that leaving the drop down on the master sheet would probably be best. I see the pros and cons to both as well, and think this way will work just fine.

[Glenn Kennedy]

You're one hour too late. I'm away now. Next iteration will be there in about 13 hr...

[Glenn Kennedy]

On phone. PC is OFF!!

[TDavis94]

That is perfectly fine! You are helping me a great deal, so we are operating on your time. I told them that it may take me a while to get this figured out, so there isn't a huge time crunch. Hope you enjoy the rest of your evening!

[Glenn Kennedy]

Bl@@dy Microsoft...

maybe this is better. BEFORE testing it.

Select Daily sheet A6, copy paste formula into a post here.

Select Daily sheet B6, copy paste formula into a post here.

CTRL-F3. Select "Range". Click the UP arrow in the "refers to" box (bottom). CTRL-C and paste into a post here. Close the dialogue boxes (click "X" at top right of box... it will need to be done 2 times, for each of the two dialogue boxes. If prompted to save ... don't.

Now test it. NOTE that I have only populated a couple of days with A, P and R.

If all else fails, are you set up for ZOOM? You are 6 hours behind my time...

[TDavis94]

Daily Sheet A6 - =IFERROR(INDEX('Master Schedule'!$A:$A,AGGREGATE(15,6,ROW(INDEX(Sched,,MATCH($D$3,Date,0)))/(INDEX(Sched,,MATCH($D$3,Date,0))="A"), ROWS(A$6:A6))),"")

Daily Sheet B6 - =IFERROR(INDEX('Master Schedule'!$A:$A,AGGREGATE(14,6,ROW(Range)/(ISNUMBER(SEARCH(CHAR(10),Range))),1)),"")

"Range Refers to" box - ='Master Schedule'!$A$5:INDEX('Master Schedule'!$A:$A,MATCH(INDEX('Daily Sheet'!$A:$XFC,ROW(),COLUMN()-1),'Master Schedule'!$A:$A,0))

I am set up for zoom, and would be more than happy to get something set up with you to discuss. We may not need that zoom call after all. After doing some testing, everything worked! I am not sure what you did, but the names and departments are changing like they should on the daily sheet. I cannot begin to express how grateful I am for your help with this!

I did have one last question though. Do you know any resources that I can use to improve my knowledge of excel? I have a background in Finance, but these functions and formulas go beyond my understanding. I didn't even completely understand the entirety of my name indexing formula. I love coming to places like these for help, but would love to be able to do these things myself. That way if there is ever an issue, I won't be stuck looking for you (the person that developed it), or needing someone else to start from scratch.

[Glenn Kennedy]

Great! When I opened the sheet you posted back to me, needless to say, it was working perfectly!! However, I think I discovered what was wrong. One of the Named Ranges had wandered off and was looking at column XFC instead of column A. Ten minutes of Googling later, I found a workaround. All I have to do now is remember the fix, next time i need it.

Where to learn Excel? Everyone is different. I bought a few books and never opened them. I called up a few YouTube videos and got annoyed by the endless adverts and closed them. finally, I discovered the Excel Forum and asked a few Qs. Many of the Regulars then are still active today and helped me out. What improved my Excel skills was... after about a year of asking Qs... on a quiet day, I nervously began answering Qs. Mostly my answers were awful, and the Regulars returned better versions. I am rather competitive and that was the spur for me to learn, from my own errors.

If/when you hit a wall... shout. But for now...



You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.