Availability code

clschofield

New Member
Joined
May 24, 2015
Messages
1
I have 34 spreadsheets in a workbook that all look alike, except for the data. Days of the week go across, and time goes down. (rows (1-4) headers (days of week and the particular sheets location), Cols (A-B) time( 24hr clock in ha;f hour increments). I froze these areas, so they appear while scrolling through the work area (c5:i52). I gave the workarea (c5:I52) on each sheet a unique name, related to the sheet. These sheets can be broken into groups of 9, 4,15,5.
I have a beginning sheet that contains userforms (WhatToDo). The user can close the workbook from here, Select the sheet to go to to book time, and each data entry sheet has a home button, to take them back to this WhatToDo Sheet when done.
The one other thing I would like on this sheet (well the same thing 4x's), is what is giving me a major headache. I'm not even sure if it's possible after all the research I have done, but I'm putting this out there just in case I'm brain fried and just not seeing it.
I would like to show a quick overview of all the sheets that are available to book.
I wrote the code to select all the blank spaces on a sheet, but when I try to merge them together it's not working. I even tried to just chart them all together,(kind of like a gant chart) but it doesn't like my range selections. So I'm doing something wrong, or just not seeing the error of my ways.
I need to show what times on what days at which location are available in an overview (quick view), so the user knows where to go when they need to book a time slot. Or vise versus, show whats booked and blanks in what is not booked. either by listing the locations available or color coded chart showing whats available. Then using the userform created to go to a specific location. I'm ready to switch to Access, but since I've already done so much work in excel, I want to finish it, if possible.

Cleaning******* 1 - cooktop, ovens, 2 work top tables ( possible share)
Multi - Occupants
Time AvailabilityMondayTuesdayWednesdayThursdayFridaySaturdaySunday
12:00:00 AM Baker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableUltimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)
12:30:00 AMBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableUltimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)
1:00:00 AMBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableUltimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)
1:30:00 AMBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableUltimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)
2:00:00 AMBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableUltimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)
2:30:00 AMBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableUltimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)
3:00:00 AMBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableUltimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)
3:30:00 AMBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableUltimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)
4:00:00 AMBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableUltimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)
4:30:00 AMBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableUltimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)
5:00:00 AMBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableUltimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)
5:30:00 AMBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableUltimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)Ultimate Pies (ovens and work tables)
6:00:00 AMBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableBaker Goods (Ovens and work tableCleaningCleaningCleaning
6:30:00 AMCleaningCleaningCleaningCleaning Cookies Galore (Ovens and prep tables)Cookies Galore (Ovens and prep tables
7:00:00 AMSoup ******* (cooktop and one prep table) Bakers Dozen (ovens and prep table)Soup ******* (cooktop and one prep table) Bakers Dozen (ovens and prep table)Soup ******* (cooktop and one prep table) Bakers Dozen (ovens and prep table)Soup ******* (cooktop and one prep table) Bakers Dozen (ovens and prep table)Soup ******* Cooktop and 1 prep tableCookies Galore (Ovens and prep tables)Cookies Galore (Ovens and prep tables
7:30:00 AMSoup ******* (cooktop and one prep table) Bakers Dozen (ovens and prep table)Soup ******* (cooktop and one prep table) Bakers Dozen (ovens and prep table)Soup ******* (cooktop and one prep table) Bakers Dozen (ovens and prep table)Soup ******* (cooktop and one prep table) Bakers Dozen (ovens and prep table)Soup ******* (Cooktop & 1 prep table)Cookies Galore (Ovens and prep tables)Cookies Galore (Ovens and prep tables
8:00:00 AMSoup ******* (cooktop and one prep table) Bakers Dozen (ovens and prep table)Soup ******* (cooktop and one prep table) Bakers Dozen (ovens and prep table)Soup ******* (cooktop and one prep table) Bakers Dozen (ovens and prep table)Soup ******* (cooktop and one prep table) Bakers Dozen (ovens and prep table)Soup ******* (Cooktop & 1 prep table)Cookies Galore (Ovens and prep tables)Cookies Galore (Ovens and prep tables
8:30:00 AMSoup ******* (cooktop and one prep table) Bakers Dozen (ovens and prep table)Soup ******* (cooktop and one prep table) Bakers Dozen (ovens and prep table)Soup ******* (cooktop and one prep table) Bakers Dozen (ovens and prep table)Soup ******* (cooktop and one prep table) Bakers Dozen (ovens and prep table)Soup ******* (Cooktop & 1 prep table)Cookies Galore (Ovens and prep tables)Cookies Galore (Ovens and prep tables
9:00:00 AMSoup ******* (Cooktop & 1 prep table)Soup ******* (Cooktop & 1 prep table)Soup ******* (Cooktop & 1 prep table)Soup ******* (Cooktop & 1 prep table)Soup ******* (Cooktop & 1 prep table)Cookies Galore (Ovens and prep tables)Cookies Galore (Ovens and prep tables
9:30:00 AMSoup ******* (Cooktop & 1 prep table)Soup ******* (Cooktop & 1 prep table)Soup ******* (Cooktop & 1 prep table)Soup ******* (Cooktop & 1 prep table)Soup ******* (Cooktop & 1 prep table)Cookies Galore (Ovens and prep tables)Cookies Galore (Ovens and prep tables
10:00:00 AMCleaning by previous occupantCleaning by previous occupantCleaning by previous occupantCleaning by previous occupantCleaning by previous occupantCookies Galore (Ovens and prep tables)Cookies Galore (Ovens and prep tables
10:30:00 AM Cookies Galore (Ovens and prep tables)Cookies Galore (Ovens and prep tables
11:00:00 AM Cookies Galore (Ovens and prep tables)Cookies Galore (Ovens and prep tables
11:30:00 AMWednesday's SpecialsWednesday's Specials Weekend WarriorsWeekend WarriorsCookies Galore (Ovens and prep tables)Cookies Galore (Ovens and prep tables
12:00:00 PMWednesday's SpecialsWednesday's Specials Weekend WarriorsWeekend WarriorsCookies Galore (Ovens and prep tables)Cookies Galore (Ovens and prep tables
12:30:00 PMWednesday's SpecialsWednesday's Specials Weekend WarriorsWeekend WarriorsCookies Galore (Ovens and prep tables)Cookies Galore (Ovens and prep tables
1:00:00 PMWednesday's SpecialsWednesday's Specials Weekend WarriorsWeekend WarriorsCookies Galore (Ovens and prep tables)Cookies Galore (Ovens and prep tables
1:30:00 PMWednesday's SpecialsWednesday's Specials Weekend WarriorsWeekend WarriorsCookies Galore (Ovens and prep tables)Cookies Galore (Ovens and prep tables
2:00:00 PMWednesday's SpecialsWednesday's Specials Weekend WarriorsWeekend WarriorsCookies Galore (Ovens and prep tables)Cookies Galore (Ovens and prep tables
2:30:00 PMWednesday's SpecialsWednesday's Specials Weekend WarriorsWeekend WarriorsCookies Galore (Ovens and prep tables)Cookies Galore (Ovens and prep tables
3:00:00 PMCleaning Cleaning Weekend WarriorsWeekend WarriorsCleaningCleaning
3:30:00 PM Weekend WarriorsWeekend Warriors
4:00:00 PM Weekend WarriorsWeekend Warriors
4:30:00 PM Weekend WarriorsWeekend Warriors
5:00:00 PM Weekend WarriorsWeekend Warriors
5:30:00 PM Weekend WarriorsWeekend Warriors
6:00:00 PM Weekend WarriorsWeekend Warriors
6:30:00 PM Cleaning by previous occupants
7:00:00 PM
7:30:00 PM
8:00:00 PM
8:30:00 PM
9:00:00 PM
9:30:00 PM
10:00:00 PM
10:30:00 PM
11:00:00 PM
11:30:00 PM

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

This is just sample data...I have 33 of these. I need to be able to do a quick view of what's available, or what's booked which ever is easier, but enough data so the user knows where to go to book someone who wants a 6 hours, 5 days a week, or 5 hours 3 days a week, or 9 hours 2 days a week etc.....without having to change location. What location has 9 hours available on Sat and Sun.... What location has 4 hours available Mon thru Thur.....
The user needs to see up front what is available where, instead of having to go through each sheet individually. Can it be done? Should I have layed out my sheets differently than what I did? Recommendation on what I should do differently?
I'm all ears.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top