Any MS Excel experts here?

[ponyfool]

I recently took over scheduling at work, and I am trying to simplify our process for when someone changes days off, etc. I realize I am waaaay out of my league here, and this is probably one of those things people make BIIIG bucks doing, but I am wondering if what I am proposing is even possible.

Below is a link to a web page I created that has a sample MS Excel spreadsheet with detailed information on what I am trying to accomplish.

If you have spare time, I would really, really, really appreciate you looking and seeing if you can point me in the right direction.

Oh, and a general question about Excel: If I have a work book with say, 6 sheets (tabs), and one of the sheets is populated by data from the other sheets, is there a way to save a copy of the displayed sheet only without the other referenced sheets and still retain the data? In other words, is it possible to take a snapshot of the populated sheet and save it? I don't care if it loses any formulas, etc, I just want the final display saved for others to view rather than having the view them entire work book.

My web page with the Excel example and questions

 

[TriggerT]

I use Excel at work all the time. If you don't care about being able to save any of the formulas, and just want a printable, or easy to E-mail version of one of the tabs, I would recommend using Adobe. You will need the full Acrobat version, not just the reader, but it works great.

If I get chance, I will look at the link while I am at work later today.

 

[ViperDriver]

Regarding your second question, copy whatever you want, go to the new location, and do an Edit | Paste Special | Values.

Your other question requires more time than I have this morning. If you don't have an answer by tonight, I'll give it a shot.

 

[git-r-dan]

I use Excel at work all the time. If you don't care about being able to save any of the formulas, and just want a printable, or easy to E-mail version of one of the tabs, I would recommend using Adobe. You will need the full Acrobat version, not just the reader, but it works great.
If I get chance, I will look at the link while I am at work later today.

PDF Factory Pro may do the same thing and may be a little cheaper. If you don't mind the advertisement of pdf factory across the page, pdf factory has a free version. I use the pro version all the time for emails and work. All you do is go to print and select pdf factory as the printer. You can stack other documents just by choosing each one to print, then arrange, view the pdf, save, email, ...

I'm not great on Excel, but will look at your link to see if there's any help I can give. Viper is on target with copying / pasting to a new document created which will include the formulas.

There's a ton of teckies here just waiting for a question like this. You're in good hands.

 

[ponyfool]

ViperDriver, thank you! That solves one of the issues. I paste special>values, then paste special>format and it works great!

The reason I am using Excel is, all of our work computers have Excel and the city will NOT buy a program for this. They believe doing it the way they have done it for years (using MS Word) and simply cutting and pasting officer's names and numbers is just fine.

Everytime someone changes days off, there are about 14 individual forms that have to be changed (I only included a few). I know enough about auto population to get me to believe I can do this, but not quite enough to actually know how to do it.

In addition, I also need to have the other sergeants change the daily rosters to make changes due to sickness, district assignments, etc. I just don't ever want them touching the masters. This way, using ViperDriver's technique, I open the masters for a week in advance, make whatever changes I am aware of, copy and save it as a new non formulated daily roster, and anyone can change it as needed without screwing up my masters.

Did I mention I don't want anyone dinking with my masters???

 

[HaulinAshe]

Just gave your sheet a quick look.

One suggestion, not sure if it might help you. If you prefer to have actual names instead of Officer-nn, you could use a separate sheet that lists Officer-nn, then the name.

Have the other sheets reference that appropriate cell on your "Officer Names" sheet. That way you change the names in one place and it spreads the change throughout.

I can do a quick example if you are interested in getting this done.

Also noticed that you have a lot of Date fields that do not have the Date format applied. It will save you a lot of cleanup work and assure better data, if you select, right-click, and take the time for Format Cells to restrict the type of info that can be entered. Excel has a lot of data validation tools that are very useful.

Yes, I volunteer to help you with that too!



Okay, just looked again and saw that you have a sheet called Seniority Roster that has the names listed next to Officer-nn. So my suggestion might be to use Paste Special, Paste Link, from the cell where the name exists, so that when you change the name for Officer-1 it gets reflected throughout all the sheets. And you get to see the persons real name rather than the generic reference.

But admittedly, I am not 100% sure what want to accomplish with the spread.

 

[ponyfool]

Okay, just looked again and saw that you have a sheet called Seniority Roster that has the names listed next to Officer-nn. So my suggestion might be to use Paste Special, Paste Link, from the cell where the name exists, so that when you change the name for Officer-1 it gets reflected throughout all the sheets. And you get to see the persons real name rather than the generic reference.But admittedly, I am not 100% sure what want to accomplish with the spread.

Done! Thanks! Except for where I have two officers. I can't get that cell to put both names down.

 

[HaulinAshe]

Done! Thanks! Except for where I have two officers. I can't get that cell to put both names down.

Hmmm... Working... working....

Sorry, but I have to give up for the moment. Tried using the CONCATENATE function, but cannot seem to make it work with external references. I'll ask somebody that works for me. (I hire my intelligence!)

Well, it works in Office 2003, but has probs in XP. So if you have the latest version of Office/Excel it should work.

=CONCATENATE(Sheet1!$A$2,", ",Sheet1!$A$3)

Gives you "Name1, Name2" in the same cell.

Hope this helps!

 

[ponyfool]

=CONCATENATE(Sheet1!$A$2,", ",Sheet1!$A$3)
Gives you "Name1, Name2" in the same cell.

You are a god! Thank you!!!!! This shit might work in the end afterall!!!!

 

[Malve]

ponyfool,

It sounds like you have received the answers that you were looking for. I too am proficient in Excel. Feel free to PM me anytime with questions.

 

[ponyfool]

Thanks everyone for their help. I had about a day and a half to learn what I needed, and completed my assigned task!

I will say that vlookup is an AWESOME feature of Excel!!!

 

[sradek]

I recently took over scheduling at work, and I am trying to simplify our process for when someone changes days off, etc. I realize I am waaaay out of my league here, and this is probably one of those things people make BIIIG bucks doing, but I am wondering if what I am proposing is even possible.
Below is a link to a web page I created that has a sample MS Excel spreadsheet with detailed information on what I am trying to accomplish.

If you have spare time, I would really, really, really appreciate you looking and seeing if you can point me in the right direction.

Oh, and a general question about Excel: If I have a work book with say, 6 sheets (tabs), and one of the sheets is populated by data from the other sheets, is there a way to save a copy of the displayed sheet only without the other referenced sheets and still retain the data? In other words, is it possible to take a snapshot of the populated sheet and save it? I don't care if it loses any formulas, etc, I just want the final display saved for others to view rather than having the view them entire work book.

My web page with the Excel example and questions

You need to setup a netmeeting (free, or equivalent screen conference) to ask and get answers. Excel is complicated for the un-initiated, but simple who have used it.

Otherwise you will be writing a thousand words and still may not get the answer quickly.

 

[Woodstock]

Google calendar?

 

[jimbemotumbo]

If I were trying to accomplish what I believe you are trying to, I would be using a relational database instead of a spreadsheet. MS Access or similar would tie all this up very nicely. Although you can force a spreadsheet to do this sort of thing, it's not what they are intended to do, or very good at, and the output is not easy to review.

 

[ionbeam]

+1 on Access. A bonus is that the existing data in Excel can be imported to Access and the Access data can exported back into Excel. You don't have to throw away everything you've got and don't have to start from scratch in Access.