Replies

You see, I knew that banging my head against wasn’t going to get the answer as quickly as asking the Loungers!

I was sooo close.

Thanks – it works great!

You see, I knew that banging my head against wasn’t going to get the answer as quickly as asking the Loungers!

I was sooo close.

Thanks – it works great!

Alright, I did copy and paste macro – and I must admit, even thought I have dabbled in VBA macros it still amazes me how you guys (them thar lounge experts) can come up with the coding so fast, I did I complete – DoH ! when your idea of using the Rules Wizard was given. Sure enough, it did the trick.
Sorry John, I did not have a chance to do a speed test to see which idea was faster, but I thank you both for your prompt replies!
Thanks!

Alright, I did copy and paste macro – and I must admit, even thought I have dabbled in VBA macros it still amazes me how you guys (them thar lounge experts) can come up with the coding so fast, I did I complete – DoH ! when your idea of using the Rules Wizard was given. Sure enough, it did the trick.
Sorry John, I did not have a chance to do a speed test to see which idea was faster, but I thank you both for your prompt replies!
Thanks!

In the greater scheme of things, yes, Access would be better but the system (and my skills) are not ready for that transition.
Thank you for the code. As soon as I saw it – . I knew what had to be done. Then I discovered the complete error in my logic.
I had the macro copy / paste the information into a form and print it. The premise for this inquiry was to addon additional lines – which does work.
However, since each page is set to print at the completion of the cycle, it would print the first customer line, then it would print the next line . Then (assuming multiple entries for that customer) print another page once each product line was added. Very linear.

To make a long story short – you idea does work – but my idea of printing a form for each customer doesn’t. My whole exercise was to have the data fit the form at hand when I should have been thinking about the data and having the final form fit the data.

Of course you are both right. I am a big fan of Autofilters and one single layout. This ‘exercise’ was more an experiment on my behalf to get a 3D feel. The Data, Consolidate option was considered, but I simplified the multi-tab data into one Pivot table with Page fields for each VAN/Tab. This allows a uniform design down and the user can pull up the Page/Tab of the individual units and get more thorough data. Which goes to show, with Excel, there are countless ways to do one thing. I will probably resort to a single sheet if I can keep it from displaying too much data.

I thank you both for your opinions. They are appreciated. I knew I came to WOPR for a reason.

I was initially thinking along those lines but my data is spread across 9 tabs. Every option I have to try and create a named range over those tabs results in only 1 area in one tab being selected. I am not sure a named range can exist across multiple tabs. I can not even use DMAX due to the multiple ranges.
Any ideas on how to get arount this so I can try your formula?

My apologies. I forgot Office 97 can only hold 1 clipboard item.

The link to the KBA is: http://support.microsoft.com/default.aspx?…B;EN-US;q164337%5B/url%5D

Edited: and[/i]%20″] and tags added around the URL to make the link live. -Mod.

(Edited by gwhitfield on 27-Sep-02 06:52. Hyperlinks added)

I believe there still might be an “in” with the field codes with regards to Outlook. Microsoft has documented why wordmail does not document changes to a document sent via Outlook.
Since this spyware was originally triggered by the notion of making changes to a document and returning it, I would assume that if the fields could be updated by tracking changes, it might work.

Here is the address to the Microsoft KBA: http://www.wopr.com/cgi-bin/w3t/showthread…l?Number=181427%5B/url%5D
It would necessitate both users having Wordmail and the sender and possibly the recepient having – “Track changes while editing” selected from the Tools menu.
Assuming that this has now been done, then the Wordmail document could track any changes made to the document, thereby having the fields being updated and at the same time, update the nefarious “includetext” field and snagging a document. That’s my theory anyway.

Alas, I can not experiment any further as I have no Wordmail option with my Outlook.

One further question to this melee – I have seen some web-sites with a survey/entry form in Word format on their site. If set up as a survey, with input fields, and with that lovely Office interchange set up in IE, could a user, when filling out the survey online be passing more than a few answers back? Of course it relies on the end user knowing the file name and path to documents, but can a file be snatched this way too? Just curious.

I spoke too soon. Regardless of which tact I take, if I use the sumif function, it will sum all group codes regardless of product. Even with a mulitple criteria, it will still read true and still add all applicable groups. In the end, I went for simple ugly.

I inserted a new column as A, then added group with product to form a unique code – ie, ABCAP and ABCOR. Now when I use the sumif, using the incentives table in a vertical mode, it will calculate and pull (through vlookup) the incentive amount and only apply that calculation to that group and that product. Simple, a couple of steps more to insert the columns and add in the group and the product code – but, heh, it works. Thanks again for pointers.

The wonders of trying to automate something. Yes, the incentives are applied by group, but I have a few instances where the incentives for a group are applied to more than one product so previous formulas only found the first incentive by group/product. After reviewing your example – I agree. I was thinking vertically, but setting up a horizontal table will allow me to use a lookup formula easily. I will have to reformat my original data table but I think this shall do nicely.
My thanks to you and Hans for the discussion and guidance!

My apologies.

It is difficult to put into words what I am trying to do. I started with Hans idea of the SumIf and I took it further with trying DSUM. The problem I have is that I do have incentives to apply to groups and their products with some groups having incentives across more than one product. Taking Hans insight and adapting the concept to the nth degree… I still encounter the problem where the incentive can not be found and calculated if there is more than one product line for a group. The sample file, borrowed from Hans, has been attached showing the sample with my spastic formula. The final incentives should match those in J, but for those groups with multiple product lines, alas no.
So yes, maybe arrays might be the way to go (an area of minimal experience with me), but I don’t think sumproduct would do the trick.
Thoughts?

Yes and no. The ratio calculation is based on the Group more so than the product, but the product has be factored in. But I see what you are getting at – use a table reference with Sumif. I could probably incorporate the whole criteria (ie Group = ABC, Product = AP, Bonus=500) in the sumif range. Because of the number of entries, I would have to bring over a trap, like iserror for some entries not meeting the criteria. Thanks for the point in the (hopefully) right direction. I will let you know how it works on the real data.

I may be off – since I am doing this from memory and not near my XP/Office XP system (dang), but you might be able to kill it if you going into Accessories – System Tools – System Info – and go to startup and kill – ctrmon.exe. I know it has something to do with the speech/language system. I just can’t remember what.

Well, I have had limited success opening up a version of QP in notepad/wordpad. There is a lot of garbage characters, but I could make out general entries. It would depend on the size of the file too. More garbage to sift through. Who know, perhaps you could even try in Excel as a txt document?