Sorting and breaking up Excel Worksheet

[MSAccessRookie]

For over a year I have been learning to use MS Access for projects at the company that I am working for. Now I am being asked to look into a new issue with MS Excel. The Nature of the Project is as follows:

• The initial Data Source is a Spreadsheet containing Invoice Records
• Each Invoice Record consists of 1 Row with 22 Columns of data, including a State Code.
• Each Invoice Record also contains blank fields representing tax to be added to the invoice. Taxes vary from state to state, and on occasion include a city tax as well.

Solution to the project seems to boil down to two identifiable (simple?) steps:

1. Calculate the Tax (and as a result the Final Price). for each Invoice on the Initial Sheet.
2. Sort the Sheet into updated worksheets that are based on the Initial Sheet, and sorted by the State Code.

I found a Link in the Archive to a user that had a need similar to Step 2 (I believe that I can use the Function in chergh's reply and change the references from Date to State Code). I also believe that I can write a Function to handle Step 1 (if one is required).

http://www.access-programmers.co.uk/forums/showthread.php?t=156228&highlight=sort

While I understand how to create a VB Macro that will do the processing, I am not sure how or where to activate it. I will post a copy of the Initial Spreadsheet if it is required, but I have to wait until tomorrow morning, so that I can get approval to do so.

 

[simon4amiee]

Surely better exporting to Access matey, you will have more reporting capabilities!

 

[MSAccessRookie]

Surely better exporting to Access matey, you will have more reporting capabilities!

Simon4amiee,

Thanks for taking time to reply. Believe it or not, I am a really no longer an MS Access Rookie, and I have become a relatively strong Access user over the last year or so. I have already proposed doing what you have suggested, and the client did not want that, so I am looking for an all Excel solution, or a defensible explanation to present to a group of Excel Power Users as to whay I need to use MS Access.

 

[DCrake]

I get the gist of what you want, but just to make sure can you post a example of a solution layout based on the source data?

David

 

[Mike375]

You can do a lot of your actions while Record Macro is running and it gives you a start for the VBA.

Triggering it is a real pain in Excel compared to Access.

If you right click on Sheet Number at the bottom left and then View Code you can use stuff like this

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
With Target

With ActiveCell

If Range("D1") = 10 Then
Call Macro1
End If

This triggers things when clicking on cells but you can need a lot of If Thens so it only works where you want.

You can also make a form and put a combo on it and triggers similar to Access.

I have a link here that someone posted recently that is all about Event Triggers in Excel. There is also a forum called Mr Excel which is the reverse of AWF, that is, Excel is the main game and Access is the low life

 

[chergh]

What your wanting is quite possible when you have your functions you probably want to call them using the worksheet change event. Then have some validation to make sure all necessary values are in the spreadsheet and if they are then call the necessary functions.

 

[Mike375]

Here is a site on Excel Event trigggers

http://www.cpearson.com/excel/Events.aspx

 

[Mike375]

I just noticed that link was originally posted by chergh

 

[qafself]

This is a good site to post on

www.excelforum.com

 

[MSAccessRookie]

I get the gist of what you want, but just to make sure can you post a example of a solution layout based on the source data?

David

David,

I should have thought about that. Attached is an updated spreadsheet (data only) that represents the client's expectations. It does not contain any formulas or VBA Code.

 

[chergh]

None of what your client is particularly difficult but it may be tedious. One suggestion I have is have the only way to enter data in the spreadsheet be a userform this should make things easier.

 

[DCrake]

Good Idea.

If you create a user form within the main Excel workbook which collects the data as per the column headings. Then have a submit button, which will first look at a field that determines which customer it is. Then checks to see if a worksheet exists for that customer, if not create one, then it goes to the next blank row on the worksheet and copies the data from the form into the appropriate cells. At the same time performing the calculation if there is no formula in the calculated cells.

Finally clears the form controls. By doing this you can also introduce some validation into the data collection form, and it becomes more user friendly.

David