I have a project that has me stumped as to where to even go. My boss makes
excel sheets monthly displaying customers and costs for each resource.

Customer Resource1 Resource2 ..etc
1 $200 $300
2 $40 $900

She wants to be able to take all the months that she has made in a given
year and sum the respective costs so that she can put it in a different
worksheet to create a sort of true-up. The worksheet she puts the sums in
looks exactly like the orignal worksheet.

I originally started with Access and made a sort of database with all of the
costs but getting the SUM worksheet to look like the oringal worksheet kept
screwing up Access and eventually I got a file too big error (or something to
that effect).

So I'm currently trying to use SQL but I'm not sure how:
1. To have a form upload the excel file into the database
2. To even get the sums of each resource for each customer
3. To get the format to look like the excel sheet
4. To export back to an excel file

So I thought maybe SQL isn't the right route.

I know this is slightly confusing but any help is greatly appreciated.

I have fair knowledge of Access, SQL, and Visual Basic.

I don't understand why you need a database. Can't you do it with Excel
?
Regards.
Daniel

I have a project that has me stumped as to where to even go. My boss makes
excel sheets monthly displaying customers and costs for each resource.

Customer Resource1 Resource2 ..etc
1 $200 $300
2 $40 $900

She wants to be able to take all the months that she has made in a given
year and sum the respective costs so that she can put it in a different
worksheet to create a sort of true-up. The worksheet she puts the sums in
looks exactly like the orignal worksheet.

I originally started with Access and made a sort of database with all of the
costs but getting the SUM worksheet to look like the oringal worksheet kept
screwing up Access and eventually I got a file too big error (or something to
that effect).

So I'm currently trying to use SQL but I'm not sure how:
1. To have a form upload the excel file into the database
2. To even get the sums of each resource for each customer
3. To get the format to look like the excel sheet
4. To export back to an excel file

So I thought maybe SQL isn't the right route.

I know this is slightly confusing but any help is greatly appreciated.

I have fair knowledge of Access, SQL, and Visual Basic.

How would I do it with Excel?

The point of doing a database is to automate the monthly process of summing
the numbers. The sheet contains about 40 columns and 50 rows. I tried
adding between worksheets and I would have to do each individual cell (I
can't drag and auto change the formula) because it creates a "circular
reference". Plus I also need to watch out for new customers and new
resources.

Jennifer

"Daniel.C" wrote:

I don't understand why you need a database. Can't you do it with Excel
?
Regards.
Daniel

I have a project that has me stumped as to where to even go. My boss makes
excel sheets monthly displaying customers and costs for each resource.

Customer Resource1 Resource2 ..etc
1 $200 $300
2 $40 $900

She wants to be able to take all the months that she has made in a given
year and sum the respective costs so that she can put it in a different
worksheet to create a sort of true-up. The worksheet she puts the sums in
looks exactly like the orignal worksheet.

I originally started with Access and made a sort of database with all of the
costs but getting the SUM worksheet to look like the oringal worksheet kept
screwing up Access and eventually I got a file too big error (or something to
that effect).

So I'm currently trying to use SQL but I'm not sure how:
1. To have a form upload the excel file into the database
2. To even get the sums of each resource for each customer
3. To get the format to look like the excel sheet
4. To export back to an excel file

So I thought maybe SQL isn't the right route.

I know this is slightly confusing but any help is greatly appreciated.

I have fair knowledge of Access, SQL, and Visual Basic.

Have a look at the following file :
http://www.filedropper.com/jennifer
I put a command button on the "Year" sheet. Customers are added, sorted
and totals are computed.
Daniel

How would I do it with Excel?

The point of doing a database is to automate the monthly process of summing
the numbers. The sheet contains about 40 columns and 50 rows. I tried
adding between worksheets and I would have to do each individual cell (I
can't drag and auto change the formula) because it creates a "circular
reference". Plus I also need to watch out for new customers and new
resources.

Jennifer

"Daniel.C" wrote:

I don't understand why you need a database. Can't you do it with Excel
?
Regards.
Daniel

I have a project that has me stumped as to where to even go. My boss makes
excel sheets monthly displaying customers and costs for each resource.

Customer Resource1 Resource2 ..etc
1 $200 $300
2 $40 $900

She wants to be able to take all the months that she has made in a given
year and sum the respective costs so that she can put it in a different
worksheet to create a sort of true-up. The worksheet she puts the sums in
looks exactly like the orignal worksheet.

I originally started with Access and made a sort of database with all of
the costs but getting the SUM worksheet to look like the oringal worksheet
kept screwing up Access and eventually I got a file too big error (or
something to that effect).

So I'm currently trying to use SQL but I'm not sure how:
1. To have a form upload the excel file into the database
2. To even get the sums of each resource for each customer
3. To get the format to look like the excel sheet
4. To export back to an excel file

So I thought maybe SQL isn't the right route.

I know this is slightly confusing but any help is greatly appreciated.

I have fair knowledge of Access, SQL, and Visual Basic.

This is great, Daniel! Thank you.

1 question though:

1. Customers aren't actually given numbers and they are already in a
particular order. Is there a way to not sort them but still add new
customers?

I tried simply deleting the order...Ascending but I think the .sort also has
something to do with it but I'm not sure what to delete.

Thanks again,
Jennifer


"Daniel.C" wrote:

Have a look at the following file :
http://www.filedropper.com/jennifer
I put a command button on the "Year" sheet. Customers are added, sorted
and totals are computed.
Daniel

How would I do it with Excel?

The point of doing a database is to automate the monthly process of summing
the numbers. The sheet contains about 40 columns and 50 rows. I tried
adding between worksheets and I would have to do each individual cell (I
can't drag and auto change the formula) because it creates a "circular
reference". Plus I also need to watch out for new customers and new
resources.

Jennifer

"Daniel.C" wrote:

I don't understand why you need a database. Can't you do it with Excel
?
Regards.
Daniel

I have a project that has me stumped as to where to even go. My boss makes
excel sheets monthly displaying customers and costs for each resource.

Customer Resource1 Resource2 ..etc
1 $200 $300
2 $40 $900

She wants to be able to take all the months that she has made in a given
year and sum the respective costs so that she can put it in a different
worksheet to create a sort of true-up. The worksheet she puts the sums in
looks exactly like the orignal worksheet.

I originally started with Access and made a sort of database with all of
the costs but getting the SUM worksheet to look like the oringal worksheet
kept screwing up Access and eventually I got a file too big error (or
something to that effect).

So I'm currently trying to use SQL but I'm not sure how:
1. To have a form upload the excel file into the database
2. To even get the sums of each resource for each customer
3. To get the format to look like the excel sheet
4. To export back to an excel file

So I thought maybe SQL isn't the right route.

I know this is slightly confusing but any help is greatly appreciated.

I have fair knowledge of Access, SQL, and Visual Basic.

Just delete the "sort" line. The order will be customers from the "Jan"
sheet, then new customers from "Feb" and so on. The list is recreated
each time by the macro :

Sub test()
Dim Coll As New Collection, Sh As Worksheet, c As Range
Dim Ctr As Integer, myRow As Integer, Resource As Integer
Sheets("Year").Range("A2:IV10000").ClearContents
On Error Resume Next
For Each Sh In Worksheets
If Sh.Name < "Year" Then
For Each c In Range(Sh.[A2], Sh.[A65000].End(xlUp))
Coll.Add CStr(c), CStr(c)
Next c
End If
Next Sh
On Error GoTo 0
With Sheets("Year")
Ctr = 1
For Each Item In Coll
Ctr = Ctr + 1
.Cells(Ctr, 1) = Item
Next Item
Set Coll = Nothing

For Each Sh In Worksheets
If Sh.Name < "Year" Then
For Each c In Range(Sh.[A2], Sh.[A65000].End(xlUp))
myRow = Application.Match(c, .[A:A], 0)
For i = 2 To Sh.[IV1].End(xlToLeft).Column
.Cells(myRow, i) = .Cells(myRow, i) + _
Sh.Cells(c.Row, i)
Next i
Next c
End If
Next Sh
End With
End Sub

Regards.
Daniel

This is great, Daniel! Thank you.

1 question though:

1. Customers aren't actually given numbers and they are already in a
particular order. Is there a way to not sort them but still add new
customers?

I tried simply deleting the order...Ascending but I think the .sort also has
something to do with it but I'm not sure what to delete.

Thanks again,
Jennifer


"Daniel.C" wrote:

Have a look at the following file :
http://www.filedropper.com/jennifer
I put a command button on the "Year" sheet. Customers are added, sorted
and totals are computed.
Daniel

How would I do it with Excel?

The point of doing a database is to automate the monthly process of summing
the numbers. The sheet contains about 40 columns and 50 rows. I tried
adding between worksheets and I would have to do each individual cell (I
can't drag and auto change the formula) because it creates a "circular
reference". Plus I also need to watch out for new customers and new
resources.

Jennifer

"Daniel.C" wrote:

I don't understand why you need a database. Can't you do it with Excel
?
Regards.
Daniel

I have a project that has me stumped as to where to even go. My boss
makes excel sheets monthly displaying customers and costs for each
resource.

Customer Resource1 Resource2 ..etc
1 $200 $300
2 $40 $900

She wants to be able to take all the months that she has made in a given
year and sum the respective costs so that she can put it in a different
worksheet to create a sort of true-up. The worksheet she puts the sums
in looks exactly like the orignal worksheet.

I originally started with Access and made a sort of database with all of
the costs but getting the SUM worksheet to look like the oringal
worksheet kept screwing up Access and eventually I got a file too big
error (or something to that effect).

So I'm currently trying to use SQL but I'm not sure how:
1. To have a form upload the excel file into the database
2. To even get the sums of each resource for each customer
3. To get the format to look like the excel sheet
4. To export back to an excel file

So I thought maybe SQL isn't the right route.

I know this is slightly confusing but any help is greatly appreciated.

I have fair knowledge of Access, SQL, and Visual Basic.