A daily sales spreadsheet few questions

I work at a Resturant and we of course use excel to do all of our daily sales inputs to find out our deposits each night. We also use quick books to enter everything just for information purposes. I've been playing around with the date formula but can't quite figure it out.

The desires are to take the daily sales totals sorted by day of week and create an additional spreadsheet that grabs those daily sales and puts them into a top 10 so I no longer have to do it manually.

Right now it's a month by month template spreadsheet that counts to 31 and we just enter up to the end of the month and name it that month so for example February ends the 28 but still 3 days left on the template.

Would I need to make a spreadsheet for every month? I assume not but any information would be awesome. I know I can pull the numbers from one spreadsheet to another but to sort them into days of the week and the. Find only the top 10. I'm sure it's probably easy but I'm still learning. Thanks for any info!

Corey

Could you post a copy of the template you're currently using to give us a better idea of what you're trying to do?
Also a manually created example of your desired outcome, with some explanation if it's not immediately obvious.

Ok

The attached spreadsheet as a sheet named template.

clicking on the button will create a new sheet and list the days of the month in columns I and J.

the new sheet will be named for the current month and the year.

it is possible to make the macro run automatically whenever the workbook is opened
or when the template is selected.
it can be made to only create the new sheet if it dosn't already exist.

you need to list your requirements clearly and post a sample workbook.

TOP 10 DAYS of the WEEK.xlsxTemplate Spreadsheet.xlsx

These are the workbooks we are trying to connect and use via a date. If you could open it and edit it. Let me know what you did to link the "sales" field to the top sales other workbook.


The template is what we use for daily sales and the sales field on the sales workbook is what i want to link to the second excel workbook called Top 10 days of the week. HOWEVER As it sits the date is not set as a date its just a number we use a generic excel template to open a new every month. With no link of the 2. If we could link the two together to auto pull the top sales days of the week based on the day then my life would be so much easier. Please if you have any more questions im not sure how to explain it any better.

To answer a few of your questions, It is sales Sun- Saturday. And yes I'd like to record the top sales days FOREVER to keep an ongoing track of the top sales as long as it's open.

A generic template that creates every month would be awesome. However I'd like to include prior years back to almost 10 years ago for this whole formula to work. I see that your new "sales" creates a new template for us to work on. Some other info to help you possibly is that there's about 10 different people who touch this work book. It needs to be unchangeable aside from the input areas. (we've had some people really mess it up)

I will give this a test run this week. Thank you for helping. Its probably rather easy but i'm no excel expert thats for darn sure! Although i am trying!

thanks again for your help.

Originally Posted by mehmetcik

Sorry I don't understand.

Top Ten Days of the week lists six days Mon to Sat

Ok I assume that there will be ten rows.

So can I assume that you want to record the dates and takings for the top ten days.

So Top ten mondays
Top ten tuesdays
Top ten wednsdays
Top ten thursdays
Top Ten Fridays
Top ten Saturdays

So sixty dates and the takings on those dates?

So is that forever or for the last year or the last two years?

Ok so you have a generic template that you use and create a duplicate for each month.

Lets start there. Do you want that created automatically on the first of the month or do you want the user to press a button to create it?

I will start there until I get some feedback from you.

I think that the best solution is to create a macro so that when you try to open the template, it tries to open the workbook for the current month,
if that is not possible it creates a workbook for the current month and then closes itself.

Ok. this is your first peice of code.

Please Login or Register  to view this content.

Save this file into a new folder. once you have tested it and are happy that my macro is safe, the declare that folder as a safe folder].

call it sales if you like.

try to open the file. it should create a new file called "Sales April 2014"

close the file

try and open the file again. Now it should open "Sales April 2014".