Forum Discussion
Solved
Help referencing a cell from a different sheet
Hello, I have a master sheet with the schedule for all of my employees and am trying to make a sheet with individual employee's schedules. My idea is that the Sheet for each employee will reference t...
Is it possible for you to post a mockup of your actual workbook--no real names but enough of a set of fake names to represent the issue -- posting it on OneDrive or GoogleDrive with a link here that grants access to it,
mathetes Thank you for your help!
https://1drv.ms/x/s!AgWnVeQ6FtUXgnUUBrAJAc-LgLsk?e=raAZid
Here is a link, please let me know if that works.
Thank you again for taking some time to help!
Now that I can see what you're working with, I have one observation (possibly more to follow) and more questions.
The observation: Your master sheet is designed for human viewing. Such a design actually interferes with robust Excel functioning. In particular, too many empty rows, and merged cells. A good excel set of "raw data" or, if you prefer, "master data," should be simple rows and columns of data, no blank rows or columns, no merged cells (your column N and all of rows 5 and 6). All of those blanks and merges make a sheet "look nice" to the human eye, but are (sorry to have to say it) a bad practice for good Excel. Especially given that this is mostly for "behind the scenes" data, don't waste time and energy making it look pretty.
That said, it's not altogether clear which data you are wanting on the "Supervisor" sheet: do you want the hours of the supervisor OR the of one of the employees that is going to be supervised by a given supervisor? If the former, there should be designations like "Supervisor A" and "Supervisor B" just as you in this demo sheet have Employees A, B, C etc. If the latter, then we'll need to know which employee.
So I've done a very basic formula to get the data for the first supervisor into the first several cells--that formula can be copied across to fill in the rest of the row.
But my longer term preference would be to help you design a spreadsheet so that all you'd need to do is enter the name of one of the employees and the entire four weeks' schedules for that employee would appear in your "Supervisor" Tab. That will require an extensive (but straightforward) modification to your "master" sheet. Are you willing to do that?
- Thanks for your detailed response!
- Yes, that makes perfect sense- and is totally fine with me.
-So the way i'm envisioning it, The Supervisor's schedule will appear in the sheet titled supervisor, and then each employee there after will have a sheet with only their schedule appearing. Employee A on Master will appear under a sheet titled Employee A .. if that makes sense.
- I see your edit and that looks great! it definitely seems like it will accomplish what i'm hoping to accomplish. My concern would be, as you said, it's not the best way, so would this be something that will have issues down the line?
Yes I am open to extensive modification!
Thank you again!So the way i'm envisioning it, The Supervisor's schedule will appear in the sheet titled supervisor, and then each employee there after will have a sheet with only their schedule appearing. Employee A on Master will appear under a sheet titled Employee A .. if that makes sense.
It makes sense, but is unnecessary. I created a single Output sheet in the attached. All you need to do is change the name, in one cell, and it will be populated for all four weeks with the data pertaining to that person. Print each person's schedule from that same sheet.
As you'll see, I modified the "master" sheet so it's less cluttered with empty and merged cells. Again, it's background data and does not need to made pretty. Do any beautification on the output end.
There still is more that could be done, but I think this is probably workable. Let me know what you think.