page formattiing and adding extensions to room schedule in XL

In the attached workbooks in XL 2010,  I have a room schedule for 2016, each workbook represents 1/4 of the year (Jan - Mar, Apr - June, etc.). I will need to print these out each day and right now, the page margins do not fit into 1 page per day.  So I need the margins to fit one page per day.

In addition, I will need to copy the room extensions for each day for Guest 1, Guest 2 and Guest 3. So on the first day of each quarter for each workbook, I have the extensions for each guest room listed but will need this copied to all instances of each guest room, in the cell below the room name, for every day on each sheet in all books.

Thanks.
SF_Conference_Rooms_2016_1stQtr.xlsx
SF_Conference_Rooms_2016_2ndQtr.xlsx
SF_Conference_Rooms_2016_3rdQtr.xlsx
SF_Conference_Rooms_2016_4thQtr.xlsx
contrainAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

regmigrantCommented:
to fix the page margins go to print (file print) and select the correct paper size (its currently set to Letter). then on the View menu select 'page break preview' and you can move the dotted blue line to cover the whole page. - I've done one to show what I mean. I think you are producing these from an earlier answer, if you do the same changes to the source spreadsheet you will get the correct margins each time you generate a quarter.

For the extensions the best approach would be to have a lookup table in the spreadsheet which you can update each month and a reference in the table to pick up the new extension each time you change it - I've put an example in the first table and a lookup in AA - again if you are working from a source sheet to generate the quarterly sheets it might be better to have the lookup in there so you only need to change it in one place.
Copy-of-SF_Conference_Rooms_2016_1s.xlsx
Saqib Husain, SyedEngineerCommented:
I have attached a file in which I have done the Month of January 2016. Make a copy of this tab and name it February and delete the last few days. Make another copy of the January tab and rename it to March.

You can rename the tab to any month and get the desired table. Delete the last day where necessary.
SF_Conference_Rooms_2016_1stQtr.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
contrainAuthor Commented:
Hi Saqib,

Sorry it took so long to get back, I was on a mini-vacation. I like that your solution produces one exact page of data per sheet, but there are a few issues:

1.  The guest rooms are Guest 1, Guest 2 and Guest 3, it looks good for the first 2 days of 2016, but as I get to January 3rd, it says Guest 2, Guest 3 and Guest 4, and then on january 4th they say, Guest 3, Guest 4, and Guest 5.

2. The date in column K no longer works after the January sheet. It was done initially by referencing the cell of the previous date and then adding 1 but after the january sheet, I just get a #Value error message.
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

contrainAuthor Commented:
Hi regmigrant,

Thanks for putting your time and effort into this. Is there some way to make each day fit onto its own page without having to do the pagebreak preview and dragging. I looked at that option, but with a full year, that is a lot of clicking and dragging. Because all of the days of the year are in the worksheets I attached, I won't be generating additional worksheets from the first one since that part is already done,

Also the vlookup for the room extensions is a good idea, but is there some way to automate that rather than having to copy and paste by hand each one for the days of the year. I'm really trying to avoid copy and paste hundreds of times over 4 workbooks.

Thanks,
contrainAuthor Commented:
I've made it all one workbook to make it easier to work with. But here is the new issue since only part of the original is working: In the attached xl 2010 workbook, I have 12 sheets, one for each month of 2016. It is a schedule for conference rooms so the room numbers and extensions do not change. For January 1st, starting in row 33, the Guest 1 room has the extension (3171),  Guest 2 is (4325) and Guest 3 is (4282), but starting from day 3 (January 3rd) the extensions no longer show correctly. I need to have each day of the year have the same extension for each guest room.
SF_Conference_Rooms_2016.xlsx
Saqib Husain, SyedEngineerCommented:
Hi, I have redone it here for you.

The trick here is to delete all sheets except January.
Correct the sheet for January.
Copy the sheets and generate all the other months.
In this revised version if you change anything for 1st of January the change will be copied to the full month automatically.
SF_Conference_Rooms_2016.xlsx
contrainAuthor Commented:
I basically had 2 issues here so the expert gave me 2 solutions. But together, they were just what I needed. He gave great examples, easy-to-follow instructions, and I was able to have my sheets just as I needed them. I also didn't have to delete any days of the month as the the solution he provided even accounted for the differnt number of days in various months. Outstanding work and wonderful solutions to my issues!!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.