creating a yearly database file using info in daily file

**bilbobagginz** · 02-07-2008, 10:26 PM

Hey.

I would like to automatically update a 'yearly' database file with info from a file that is changed on a daily basis.

The daily file that i use has info like date, truck number, delivery stops, weight.

the database file has the similar headings.

at the end of each day this daily file is saved. I would like to have the info that is entered into the daily file automatically plugged into the yearly database file into the next available group of cells with respect to the salesperson.

This is kind of a generalization but i'm hoping to just get pointed in the right direction. If something like this involves vba then it will be beyond my ability and i'll have to do it manually, which is fine, .........

I will post an example excel file, if needed,...........

So thanks in advance to anyone who can help. much appreciated.

**stevekirk** · 02-07-2008, 11:16 PM

hi,

is the daily file just one worksheet , that has the data deleted after it is put into the master file

is the master file just one worksheet

what do you want the spread sheet to look like when it is done

could a pivot table do what you need it to do when it is in one file

could the daily file be a seperate worksheet but in the master file

steve

**bilbobagginz** · 02-08-2008, 06:56 AM

Originally Posted by stevekirk

hi,

is the daily file just one worksheet , that has the data deleted after it is put into the master file

each 'truck' has it's own separate worksheet, yes

is the master file just one worksheet

separate workbook, yes

what do you want the spread sheet to look like when it is done

see attached. the information that's need to be plugged in to the 'yearly' is in a 'green' group of cells, it's a just a basic 'example'

could a pivot table do what you need it to do when it is in one file

pivot tables ?. I'll look into it

could the daily file be a seperate worksheet but in the master file

worksheet within a master workbook. yes, it could be

steve

i've placed my answers with the quote.

**stevekirk** · 02-08-2008, 08:14 PM

hi,

have a look at the attached let me know if this could be on the right track

steve

**bilbobagginz** · 02-09-2008, 11:19 AM

thanks stevekirk.

I committed the cardinal sin of assuming everyone would know what i was talking about. I've created another file that hopefully shows more of what I'm looking for. See attached.

The daily worksheet is opened daily, the information is completely changed everyday. The date, what trucks are delivering for that day, number of delivery stops and their weight. At the end of each day the 'daily' worksheet is saved. What I would like to have happen is once the 'daily' sheet is saved to have the info sorted and logged into the yearly file as a 'daily' record of truck deliveries per day. Is there an 'automatic' way to have this happen, let's say an 'update' button that I could have on the daily worksheet. Once pushed the 'update' button would sort the info per truck and date. The only information I need to have in the 'yearly' is truck number, date, weight, and how many delivery stops.

I hope this makes sense. You'll see in the example file that I'm tracking 3 trucks a day. In reality I'm tracking about 20+ trucks a day and so therefore you can see how much time the 'automation' would save.

I appreciate the help stevekirk. The only compensation I can give is thanks and much appreciated.

I'm not sure if this is still a 'function' question anymore. My apologies if I've posted in the wrong forum but when you're not sure how to solve the problem it's hard to know where to post the question.

**Bjornar** · 02-09-2008, 12:19 PM

Your yearly master data should be stored in a list (like in a database) that is arranged in a way that you can analyse the data easily. Steve mentioned Pivot Table, and I understand that you are not familiar with this. A Pivot Table can give you reports of your data in many ways. You can group by months, do averages, find what trucks has most stop, on what day you delivers the most load and lots of more results. This is very easy, when you learn how to use a Pivot Table.

But most important is to store your data in format that can be a source for these reports.

My attachment sheet "Yearly Master" shows you how you MUST store your data. Don't even think about storing it other ways, or you will regeret later, when you want to use the data for something useful. How you sort the data in this list is of no importance, and the order of the colums can be changed. The Pivot Report takes care of all the sorting you need.

The sheet "Pivot Report" shows you an example of such a report made with the source data in the "Yearly Master" sheet. This example report arranges your data just like you showed in your request, but can easily be changed.

If you dont want to copy the data from your daily sheet to this Master sheet manualy every day, you need a macro to do it for you. For someone to be able to help you with this, you should upload a real example of what your daily sheet looks like at the end of a day.

**bilbobagginz** · 02-09-2008, 01:28 PM

You're right, I'm new to the whole pivot table movement. I am definitely going to incorporate that into a worksheet.

You're right again about how the info is displayed. I totally agree. But for some idiotic reason I'm not 'allowed' to change this format. Maybe once I show the usefulness of pivot tables I can change their point of view.

How did you take the info, in the yearly, from rows and transfer it to columns, did you type it all manually or is there an option to do this??

I'm unable to post the 'actual' file due to 'security' reasons so if you can provide some info about the macro I need to do that would be great. I'm sure there are some good macro tuts around here.

Thanks again to all for the help, you guys / gals at this forum are awesome.

**Bjornar** · 02-09-2008, 02:21 PM

But for some idiotic reason I'm not 'allowed' to change this format. Maybe once I show the usefulness of pivot tables I can change their point of view.

The format is probably okay for display purpose, and as you see in my example the Pivot Report shows the result this way. But without the blank rows. And I'm sure when you learn the power of Pivot Tables and show them, they will understand that a little layout change does not hurt.

How did you take the info, in the yearly, from rows and transfer it to columns, did you type it all manually or is there an option to do this??

I did some copy and paste, but used Paste Special - Transpose to transpose horisontal data to vertical data.
I also noticed something strange: Your number of stops values contained desimal values, and not whole numbers. What does that mean ? That the truck almost stopped

I rounded of these decimals permanently, by changing Excels calculation to "Precision as displayed" and then turned of this option after the changes where made permanent.

I'm unable to post the 'actual' file due to 'security' reasons so if you can provide some info about the macro I need to do that would be great. I'm sure there are some good macro tuts around here.

I'll see if I can make a macro for you tomorrow, If someone else does not make it before that. But maybe you could provide me with a sample of the exact layout of your daily sheet, without any confidential data.

Just promice me that you store the yearly data in the format i showed you. You can hide this sheet from your boss if you have to

. Then we can use a Pivot Table or some other trick to format it the way you boss want you to. That way you can experiment with Pivot Tables and impress your boss later....

**stevekirk** · 02-09-2008, 11:50 PM

hi,

as Bjornar has said try and keep it simple. it will reward you in the end

see attached file and see if that helps. i have added the daily sheet
tha macro just copies it to the yearly sheet ready for the pivot table

steve