Update Daily Progress Sheet from Sage Report

[mark-cox]

Dear Members

We use sage 50 Accounts as a Sales and purchasing system and its not very good when it comes to progressing outstanding orders or late deliveries.
what I am after is creating a Excel workbook for tracking our Sales and Purcahsing. I would like to add 2 custom columns one "Actual ETA" and 2 "comments" to the excel sheet not the sage report.

the know issue's

1.) Sagewill not automatically put the data into my work book it will create a temp worksheet - which is fine.
2.) there is no way to add the Actual ETA and Comments into the sage data before creating the Excel Sheet (becasue if I could do that I wouldnt need to create this workbook)
3.) how do I remove duplicates sales orders with out removing the information in the 2 extra columns

I have attached a Sample work book below showing a examples of how the data will be shown. im not sure if the correct approch would be use the built in remove duplicates function or to use VBA to scan the sales order numbers and
only paste in the numbers that dont exsist.

another issue that you also may be able to help with is to collapse any repeating sales order numbers.
i.e I may have a several Sales order with more that one item, the sage report will display each item on its own line is it possible to have a peice of VBA to collapses all the sales order with the same number down to one row that can be expaned and collasped when need.

[dflak]

Hmm. I could not download the spreadsheet. Also I am going to assume that what you're providing is the sage report itself. I think the solution will involve copying and pasting this data into a spreadsheet that has the formulas and such set up.

[mark-cox]

Hi Dflak

Thanks for the reply, I have reattached the workbook for you. (assuming you wont mind trying again that is)
yes the idea behind it would be to run the sage reports one for sales and one for purchasing daily which would open in a temp excel files and then copy and paste the data from those reports
into a progressing workbook that would contain all the code. I know the nature of this task shouts out pivot table, but on this occasions the user would need to add and amend notes for the relevent sale orpurchase order and as far as im aware that is not possible within a pivot table. also just to clarify i would have one worksheet for sales order and one worksheet for purchase orders. I also would considerhiding all the data sheets in the workbook and have a type of front page that pull all the data through. ( just thinkinh allowed now)

all the code would need to do is delete any repeat orders just making sure that didndt delete any that had comments on.


Shout if any of the above does not make sense.

Thanks
Mark

[dflak]

OK. I'm looking at the workbook now. How do you define a "repeat order?" Don't discard the possibility of a pivot table just yet. With the help of a helper column or two, you can get pivot tables to do a lot of interesting things.

However, I am probably going to convert the data range into an Excel Table mostly because Excel tables are very "addressable" and formulas use a natural-language like syntax. Here is some information on Excel Tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

[newdoverman]

If you have the data from Sage copied into a table, you can have the two additional columns and they will be blank ready to be filled after the copy. A Pivot Table can then be created from the table and refreshed to show the new data.

Other than that, I'm not at all sure what you want to do.

Your profile shows that you have Excel 2007, I suggest that you update to the new file format.

[dflak]

I added a couple of additional helper columns.

I assumed that a duplicate order was a combination of the customer order number, Product Code and Description. I made a "composite key" from these three items. The composite key is simply a concatenation of the three fields with a delimiter between them for ease of reading. The formula is:
=[@[Customer O/No]]&"@"&[@[Product Code:]]&"@"&[@[Descprition:]]

The next helper column is a count of how many times the composite key appears. Duplicates have a count greater than one. The formula is:
=COUNTIF([Composite],[@Composite])

The cell after that checks to see if there are comments. =NOT(ISBLANK([@Comments]))

Finally the column headed "Use" has the following formula: =OR([@[Has Comments]]=TRUE,[@Count]=1)

Use is true if it has a comment (regardless of how many duplicates might exist) OR it is unique.

All these helper columns can be hidden.

Read the information I provided on tables. It will tell you how to clear the data in the table. So clear out the old data, and copy and paste the new data in. The nice thing about tables is that they remember the formulas and copy them down for you.

[mark-cox]

Hi Dflak

Thanks for all your help it looks great I will have a good look around the workbook and read the the information that you provided.

Thanks Again
Mark