Creating a printable list based on dates from other Excel Files

[DanBlooms]

Hi there,

I work for a Wedding Suit Hire company.

I need to create a list in excel to check what Wedding Suit parties need to be packed for that week.

What I want to happen is this:

• Every Sunday morning I print out a list of the wedding parties that need to be packed that week.
• I want this list to be created from excel checking other workbooks which contain the information for an individual wedding party and checking the date that the wedding is on, then if the date is within the next 7 days it gets added to the list of wedding parties to be packed.
• If the wedding party has already been collected or the date of the wedding has passed it needs to be removed from the list.

Firstly, is it actually possible to achieve this with Excel?

Secondly, can anyone offer any starting points for me to look at as I am new to excel programming? If anyone can offer any advice on what topics to read up on that I need to know to achieve my goal, that would be really appreciated.

Thank you,

Dan

 

[MikeDBMan]

In order to help answer your question I have a few questions:
1. Are spreadsheets located in multiple directories? Do the directories vary?
2. Is there more than one wedding per spreadsheet?
3. Is the date of the wedding stored in the same place in each spreadsheet?
4. What does "if the wedding party has already been collected" mean? Does this mean you've already packed it? If so where is that information stored in each file? Same place in each file? If not, where?

 

[DanBlooms]

In order to help answer your question I have a few questions:
1. Are spreadsheets located in multiple directories? Do the directories vary?
2. Is there more than one wedding per spreadsheet?
3. Is the date of the wedding stored in the same place in each spreadsheet?
4. What does "if the wedding party has already been collected" mean? Does this mean you've already packed it? If so where is that information stored in each file? Same place in each file? If not, where?

Hi MikeDBMAN, thanks for the reply.

At the moment the spreadsheets don't exist. The company only has a paper system, but I want to create a mirror system on the computer so that it eliminates human error.

So really, I'm open to your advice on how to organise the directories.

They could all be in one directory if needed.

The way the paper system is as follows:
*People book the wedding/function suit hire fitting.
*They pay a booking deposit.
*They tell us the date of the wedding/function.
*They arrange a date for the suit fittings with us.
*We put the paperwork in a folder for the month in which the wedding/function takes place.
*The date of the fitting appointment comes, all the details are taken for the wedding party members - sizes, measurements etc are recorded.
*The paperwork is returned to the same month (wedding/function date) folder.
*We get the suits ready for the wedding/function party, and pack them ready for collection - usually pack them a week before the collection date, but can be up to only the day before if we are really busy.
*The day the hire suits are to be collected by the customer comes by.
*The customer pays for the items and we take the paperwork out of the wedding/function date folder and put it into a Hires Still Out folder. The customer goes away with the suits they've hired.
*When the customer returns the items we transfer the paperwork from the Hires Still Out Folder into a Hires Completed folder.

In a nutshell, that's how the current system works.
I'd like to create a simpler system with excel, so that I can keep track of the Hire Party's Admin easier and minimise human error.
There have been a few instances of the party paperwork being put into the wrong month (corresponding to the function date) which have caused a few near misses for errors. Luckily, we've been able to catch them just in time - so no customers were let down.
But I want to create a better system that minimises this risk.

To answer each one of your questions:
1. Are spreadsheets located in multiple directories? Do the directories vary? They could all be in the same folder, the paper system currently uses 3 (Function Month Folder, Hires Still Out, and Completed Hires)
2. Is there more than one wedding per spreadsheet? Again, theoretically they could be all together, but I want to keep it as simple as possible really - If having only one Wedding/Function Party per spreadsheet is the easiest method then that's probably the best option.
3. Is the date of the wedding stored in the same place in each spreadsheet? Yes, it would be unless you have reasons to advise otherwise.
4. What does "if the wedding party has already been collected" mean? Does this mean you've already packed it? If so where is that information stored in each file? Same place in each file? If not, where? Yes, sorry for being unclear. It does mean that the wedding date has gone, and so the suits would already have been packed. Hopefully, my explanation of how the current paper system works will have given you a better idea of the process.

To improve on the paper system and reduce the risk of human error I'd like to have the system look for what wedding/function parties need to be packed that week and create a list that can be printed out at the start of that week (on a Sunday morning - which is the start of our week).

Ideally, I'd like the ability to print out two lists really. One which just tells us what wedding/function parties need to be packed that week. This would be the priority.

And a second one which prints out all items for each individual member of a wedding party. This would be a nice feature but isn't the main priority.

Apologies for the long-winded explanation, but I wanted to be clear so that you get a good idea of what I need to do.

Again, thanks for the reply.

If you or any of the other site members can offer further advice on how to create the system I've described I'd really appreciate it.

Cheers.

 

[MikeDBMan]

I will try to work something up to help with this. Since your long-term solution would include the second list as well, would it also be desired to take that information and "bump" it up against your inventory of stuff so you can know if you are going to perhaps be short something. For example, let's say you have 3 weddings in one week and they all need 4 size X garments. If you only have 3 in stock, you would need to know this as soon as possible, right? What do you normally do? Buy more? Up-size someone? What?

 

[DanBlooms]

Hi Mike,

thanks for getting back to me so quickly.

If it's possible to bump the items needed for the hire parties against our inventory, yes, that would be fantastic.

Usually, if we don't have enough suits in a certain size etc we do order in extra stock to cover our needs. If we don't have enough time to order things in, we do occasionally size up, or we have a small team of tailors on site and if there's enough time we can do minor alterations as needed.

To be honest, we usually gauge if we will need to order in things based on previous experience, but it would be a very handy thing to have too as an early warning.

 

[MikeDBMan]

I did a VERY quick and VERY dirty creation of files. There are 3 files and the links are below:
https://www.dropbox.com/s/ebc2z5gttxaz60i/Master Order Book.xlsm?dl=0
https://www.dropbox.com/s/hfzo74x9gjs0eso/E091617-1.xlsm?dl=0
https://www.dropbox.com/s/ebc2z5gttxaz60i/Master Order Book.xlsm?dl=0

Hopefully you can access these files. It sort of does what you want but in a different way. The file called "Master Order Book" is where the info you want is stored. It gets updated when you open the file "Order Template" and save it as a different name. The file "E091617-1" is a sample of a created file. All these files would reside in the same directory and Macros have to be enabled. There is a lot more that could be done with this that is NOT in this simple example I created in about an hour:
1. Track the inventory usage and show shortages
2. Create invoices
3. Track payments and balances owed.
4. Etc.

Do not change the name of the "Master Order Book". Do save-as to create another order from the "Order Template" file.
Take a look and if you have questions, please let me know.