Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Creating Schedule in Excel

0

I'm fairly new to deep programming in Excel. I run a business with several departments. I have created drop downs in the schedule to indicate the postion of each staff member that day. Its built into a standard schedule. Name/dates. I want to create a page per day of the week and have it pull information based on what selection is made in the drop down. So basically each day would have the different departments and the form would pull the names and hours from the main schedule. I tried the "if" function and got close to pulling at least the name based on department selected but it was a never ending list of the same name. I need information pulled by the drop down by department and it pulls that persons name and hours for the day over into the list for the appropriate day.

_____________________________________________________________

Thank you for your help John. Let me see if I can clarify. I appreciate your assistance. 

So if you look at the excel file I sent over I'll try to break down what I'm looking to pull and the purposes of each sheet. I have continued editing so there are more tabs. 

Tabs - Full Cast (All Departments, except Leadership. Leadership tab is for management.)

Then there are tabs for Friday, Saturday, Sunday, Monday, Tuesday, Wednesday, Thursday.

What I'm looking to do is be able to put in the schedule for the "Full Cast" and "Leadership" Tabs. The daily sheets would pull information from these two schedules. 

The Data Validation I'm speaking about can be found along row B6 on the excel file "Full Cast" and runs along the top of each day. So basically we can put the staff hours in and assign a department, B6.

So, on the Friday Schedule for instance it would pull from column the B column. If it detects Concessions in B6 and any others in that column it would pull the name and hours over to the corresponding day. Basically I'm wanting a main schedule input with it planning out the days. In this example I used concessions but it could feed over other departments as well. Basically we would see how everything was for each day in the week. It would help plan out the days better. So I don't know how to get it to detect "Concessions" in B6 to pull A6, B7, B8 over automatically. And if it detects it in B11, How to pull over A11, B12, B13. And etc.

excel
posted by: ntarlton79 on Thu Jun 10, 2021 at 12:57 am
Answer
Discuss

Discussion

Hi Nick and welcome to the Forum

Sorry but I'm a bit confused by your question and suggest you go back to edit it to clarify the points in the paragpraph below (please).

I'm not clear about the relationship between departments and the Full Cast / Leadership sheets- will there be several sheets like those? In the sheet Friday, are Leadership, Concessions, Ushers, Door Greeter and Ticket Collection the theatre department names? If the Full Cast sheet, say, do you expect Time in and Time out entires if "Day Off" is recorded for an individual? When you say you have "have created drop downs in the schedule", I don't see data validation dropdowns. Which cells are you referring to please?

Also am I right in guessing you're using Excel 365? (Your TeachExcel profile doesn't say).
John_Ru (rep: 6142) Jun 10, '21 at 7:32 am
I added more detail. Thank you so much for the help.
ntarlton79 Jun 11, '21 at 1:15 am
The basic logic of a workbook is that there must be a database whose duty is to enable quick and faultless data capture. and reports to display the captured data in a clear and easy-to-understand fashion. It's Excel's particular capability to combine these two functionalities into one worksheet, and that is fine when you have a single sheet.
But as your project grows, as yours has done, it becomes progressively more difficult to extract data from, effectively, reports. Formulas become convoluted and some retrievals become impossible. At the same time data entry becomes more difficult and error-prone. That's the stage you are at now. Hence your question.
The best way forward is to abandon the demand to let every database be a report, and let every report act as database, and establish clear functionality for your tabs, either as database or report. That's what my answer below is about. You should look at it. It solves your problem.
Variatus (rep: 4889) Jun 11, '21 at 3:24 am
Nick

Thanks for the additional information but I think you didn't attach the new file to your question (you say "I have continued editing so there are more tabs" but the current file has only 3). The new file would help with understanding your revision however....

I don't have much spare time today unfortunately but did you look at the Answer from Variatus and his file? As usual, his logic is good and I can see he's applied some useful formulae to get you to where you want to be.

BTW in providing future revisions to your questions, no need to address it to me or other contributors (if fact it's better not to). It's quite understanable (and nice) that you did so but the question should be complete and understandable to someone landing on the page (without them needing to refer to subsidiary discussions).
John_Ru (rep: 6142) Jun 11, '21 at 3:31 am
Add to Discussion

Answers

0

You workbook is a nice dermonstration of how thought developes during a project, and how subsequent thoughts prove the initial ones wrong. Your Leadership tab draws on data from Full Cast, meaning you felt the need for a database from which to draw information for specific reports. That's absolutely correct but your execution is faulty because you didn't think of that when you started and now you appear stuck in the groove you are in.

The way to advance is not to accept that. Even on the short to medium term, it will be easier to start over.

You need a staff list and you need a Log. (Note that LOG() is an Excel function. Therefore the use of that name for other purposes is potentially problematic.) I suspect that you will also need lists of staff positions and locations which you have awkwardly combined in a dropdown. When this starts giving you pain don't fight for its survival. Create a dedicated sheet for each or both of those lists - yes, they probably ought to be split eventually - name them and use the names in the data validations.

For now, I have added two sheets to your workbook called "StaffLog" and "Cast". The latter is simply a list of your staff. One of its tasks is to ensure that names are never spelled wrong because users select them from a list. Daily use will suggest other uses. I didn't create such dropdowns in the Full Cast sheet because that sheet must be repurposed. In your design it's the principal, the database. In my design it's a dependent, a report.

The Staff Log is a simple listing of check-in and check-out times. The first purpose of such lists is their ease of creation. Please study my design. There are lots of formulas, formats and some comments, all targed to facilitate data capture.

In the attached workbook I have shown how to extract check-in and check-out times from the log to your Full Cast sheet. Similar formulas also work on your Friday tab. On this sheet the above mentioned inadequacy of your Positions/Locations list comes to the fore. There is no position "Leadership" in that list. You tried to compensate by creating an extra sheet for that. I don't think that is the best logical path to follow but don't want to venture another suggestion - beyond saying that positions and locations aren't well suited to be contained in one list.I think, if you spend effort on the design of these lists the Leadership tab will become reduntant.

posted by: Variatus (rep: 4889) on Thu Jun 10, 2021 at 10:37 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login