HELP with Excel header/footer question!

[vrf19977]

I use Excel as an appointment book for a doctor's office. So every year, I have a spreadsheet with 300+ tabs, and I use the header/footer feature to put each day's date on the schedules.

My problem is that when the next year rolls around, and I use the former year's file for the new appointment book, all of the dates carry over incorrectly (because it's a new year). Is there a way to go in and delete ALL of the headers/footers in one step? Or do I need to manually go in and change each one (which will take forever)? I would rather delete them all and just type in a new header once a day. Plus, some of my supervisors can't seem to grasp the concept of going in and changing the dates manually, so they'll print a schedule and then freak out when the wrong date is on it.

[Pete_UK]

Can you describe the layout of your data in a bit more detail? There may be better ways of doing it, like having a way of selecting the date on one tab that then displays the appropriate data for that day, instead of having 300+ sheets.

Pete

[vrf19977]

I hate to sound like an idiot, but I'm not sure what you mean by your suggestion, sorry. I can tell you that the layout of each day's schedule is 12 columns and 50 rows, so it definitely takes up an entire piece of 8.5x11 paper when printed out.

[Pete_UK]

Instead of having the date contained within the header, why not have it in the same cell on each sheet (e.g. L1) with a formula that uses the year from the first sheet, and then you only need to change the year once and have it ripple through (although you will also have to set all those sheets to blank as well).

What I was thinking in the earlier post is that you would have one Master appointments sheet, where you would enter all appointments for all doctors in any order, then on another sheet you would have drop-downs which would allow you to select a particular doctor (or all) and select a particular date, and then the display on that sheet would automatically adjust to the new settings.

Hope this helps.

Pete

[vrf19977]

I don't suppose you could walk me through how to do your 1st idea (putting the dates into a cell on each sheet)? Also bear in mind that we don't see patients 365 days a year, we skip weekends, holidays, etc. So the spreadsheet does not consist of 365 consecutively dated sheets. I know, it's a mess.

Why my employer can't just purchase electronic scheduling software... who knows. Cheapskates!

[Pete_UK]

How do you name the sheets? Is it based on a particular date?

Pete

[vrf19977]

Every sheet is named with the day and date, like "Mon Jan 8".

[Pete_UK]

So you have to change the name of every sheet when you start a new year?

Pete

[vrf19977]

Yes, that's a whole pain in the butt by itself!

[Pete_UK]

Okay, well assuming you have changed all those sheet names and saved the file, then select the tab for the first day (Wed Jan 1 ??), right click on the tab and choose Select All Sheets. Every sheet will now be grouped together, which means that any operation that you carry out will affect every sheet, so you only have to do this once.

Select the cell that you want this to occur in (L1?) then enter this formula:

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

It returns the name of the sheet. Then right-click on the tab once more and select Ungroup Sheets.

Now, if you look in any other sheet, you will see the correct sheet name in cell L1. What's more, if you change the name of the sheet, then L1 will automatically change, so it means that if you are still doing the same thing next year, then when you change the sheet names to suit 2015, at least the entry in L1 will change with it.

Hope this helps.

Pete

[vrf19977]

Pete - I think I can work with this! I successfully did what you suggested, so the dates are now displayed in a cell, so I can delete the whole header/footer thing, even though I'll have to do it one sheet at a time (unless you know a way to delete them all at once?). And even though I'll have to update all the tab names every year, at least I won't have to manually do a header each day with the date. Thanks!

[Pete_UK]

Glad to hear that it worked for you - thanks for feeding back.

You can change the header/footer in a similar way, i.e. Group all the sheets, then click on Page Setup | Headers/Footers | Custom header and clear everything out of that (same with footers if you have any), then when you ungroup the sheets you will find that the header has disappeared from them all.

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

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[vrf19977]

Pete, you are a lifesaver!!!! Thank you! It worked perfectly Now I have dates on all my schedules that will print correctly, and I don't have to touch headers and footers anymore. I would kiss you across the Atlantic if I could lol. Thanks again