#### For Mike H.'s reply about my msg Jan 20, 'sumif but also filtering

```I apologize in advance from everybody if my this mail is aginst general rules
of the forum.
I' m new in the forum (~1-2 months old), so I do not know much about the
rules.
He wrote a formula for me
My question was "sumif but there may be also filterings"

=SUMPRODUCT(--(C1:C10="\$");SUBTOTAL(9;OFFSET(B1:B10;ROW(B1:B10)-MIN(ROW(B1:B10));0;1)))
His formula is working wonderfully.
I am a medium level excel user in general level office environments.
for i = 1 to 20
I looked at the formula,
worked on it to understand how it works
splitted the formula into pieces
etc
next i
I could not solve that offset part, why, how ?
I will make i=50:), and go on trying to understand it.
Then I recognized that I can see the profiles of people and looked Mike' s
profile
and then understood everything.
He is a 40 years worked, retired chemical engineer.
I' m sure
he is 'inititaing a chemical reaction in excel' with this formula : )
and therefore I can not see, understand how the formula is working.
Thanks and best regards,
Cousin Excel
```
 0
Utf
1/21/2010 8:40:01 AM
excel.misc 78881 articles. 5 followers.

4 Replies
753 Views

Similar Articles

[PageSpeed] 18

```Hi,

So you want to understand how the formula works. Lets take a shortened
version that looks at 5 rows only where row 3 is hidden by an auto filter.
Note i've had to change back to , instead of ; for my excel version

=SUMPRODUCT(--(C2:C6="\$"),SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)))

The formula is basically producing 3 arrays that sumproduct will multiply
together

--(C1:C6="\$")
Because all my cells contain \$ it produces this array. i.e 5 TRUE which the
double unary (--) convert to a numeric value
{1;1;1;1;1}

OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)
Produces the array which are the numbers in my cells
{6;7;8;9;10}

Now we wrap that in a subtotal function where hidden rows evaluate as 0 and
visible rows as 1 we get the following array
SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1))
Because row 3 is hidden it produces the array
{6;7;0;9;10}

Now we have the 3 arrays we need and Sumproduct will multiply them together
{1;1;1;1;1}
{6;7;8;9;10}
{6;7;0;9;10}

Hope this helps

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

"CousinExcel" wrote:

> I apologize in advance from everybody if my this mail is aginst general rules
> of the forum.
> I' m new in the forum (~1-2 months old), so I do not know much about the
> rules.
> He wrote a formula for me
> My question was "sumif but there may be also filterings"
>
> =SUMPRODUCT(--(C1:C10="\$");SUBTOTAL(9;OFFSET(B1:B10;ROW(B1:B10)-MIN(ROW(B1:B10));0;1)))
> His formula is working wonderfully.
> I am a medium level excel user in general level office environments.
> for i = 1 to 20
>   I looked at the formula,
>   worked on it to understand how it works
>   splitted the formula into pieces
>   etc
> next i
> I could not solve that offset part, why, how ?
> I will make i=50:), and go on trying to understand it.
> Then I recognized that I can see the profiles of people and looked Mike' s
> profile
> and then understood everything.
> He is a 40 years worked, retired chemical engineer.
> I' m sure
> he is 'inititaing a chemical reaction in excel' with this formula : )
> and therefore I can not see, understand how the formula is working.
> Thanks and best regards,
> Cousin Excel
```
 0
Utf
1/21/2010 10:57:03 AM
```By way of clarification.

3 arrays are produced but because of the way the formula is constructed the
sumproduct bit only works on 2 of them

{1;1;1;1;1}
{6;7;0;9;10}

1*6=6
1*7=7
1*0=0
1*9=9
1*10=10
=32
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

"Mike H" wrote:

> Hi,
>
> So you want to understand how the formula works. Lets take a shortened
> version that looks at 5 rows only where row 3 is hidden by an auto filter.
> Note i've had to change back to , instead of ; for my excel version
>
> =SUMPRODUCT(--(C2:C6="\$"),SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)))
>
> The formula is basically producing 3 arrays that sumproduct will multiply
> together
>
> --(C1:C6="\$")
> Because all my cells contain \$ it produces this array. i.e 5 TRUE which the
> double unary (--) convert to a numeric value
> {1;1;1;1;1}
>
> OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)
> Produces the array which are the numbers in my cells
> {6;7;8;9;10}
>
> Now we wrap that in a subtotal function where hidden rows evaluate as 0 and
> visible rows as 1 we get the following array
> SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1))
> Because row 3 is hidden it produces the array
> {6;7;0;9;10}
>
> Now we have the 3 arrays we need and Sumproduct will multiply them together
> {1;1;1;1;1}
> {6;7;8;9;10}
> {6;7;0;9;10}
>
> Hope this helps
>
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "CousinExcel" wrote:
>
> > I apologize in advance from everybody if my this mail is aginst general rules
> > of the forum.
> > I' m new in the forum (~1-2 months old), so I do not know much about the
> > rules.
> > He wrote a formula for me
> > My question was "sumif but there may be also filterings"
> >
> > =SUMPRODUCT(--(C1:C10="\$");SUBTOTAL(9;OFFSET(B1:B10;ROW(B1:B10)-MIN(ROW(B1:B10));0;1)))
> > His formula is working wonderfully.
> > I am a medium level excel user in general level office environments.
> > for i = 1 to 20
> >   I looked at the formula,
> >   worked on it to understand how it works
> >   splitted the formula into pieces
> >   etc
> > next i
> > I could not solve that offset part, why, how ?
> > I will make i=50:), and go on trying to understand it.
> > Then I recognized that I can see the profiles of people and looked Mike' s
> > profile
> > and then understood everything.
> > He is a 40 years worked, retired chemical engineer.
> > I' m sure
> > he is 'inititaing a chemical reaction in excel' with this formula : )
> > and therefore I can not see, understand how the formula is working.
> > Thanks and best regards,
> > Cousin Excel
```
 0
Utf
1/21/2010 11:07:04 AM
```Thank you Sir Master,
I am eager to study your explanations after the work.
Thanks and best regards,
Cousin Excel

"Mike H" wrote:

> By way of clarification.
>
> 3 arrays are produced but because of the way the formula is constructed the
> sumproduct bit only works on 2 of them
>
>  {1;1;1;1;1}
> {6;7;0;9;10}
>
>
> 1*6=6
> 1*7=7
> 1*0=0
> 1*9=9
> 1*10=10
> =32
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Mike H" wrote:
>
> > Hi,
> >
> > So you want to understand how the formula works. Lets take a shortened
> > version that looks at 5 rows only where row 3 is hidden by an auto filter.
> > Note i've had to change back to , instead of ; for my excel version
> >
> > =SUMPRODUCT(--(C2:C6="\$"),SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)))
> >
> > The formula is basically producing 3 arrays that sumproduct will multiply
> > together
> >
> > --(C1:C6="\$")
> > Because all my cells contain \$ it produces this array. i.e 5 TRUE which the
> > double unary (--) convert to a numeric value
> > {1;1;1;1;1}
> >
> > OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)
> > Produces the array which are the numbers in my cells
> > {6;7;8;9;10}
> >
> > Now we wrap that in a subtotal function where hidden rows evaluate as 0 and
> > visible rows as 1 we get the following array
> > SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1))
> > Because row 3 is hidden it produces the array
> > {6;7;0;9;10}
> >
> > Now we have the 3 arrays we need and Sumproduct will multiply them together
> > {1;1;1;1;1}
> > {6;7;8;9;10}
> > {6;7;0;9;10}
> >
> > Hope this helps
> >
> > --
> > Mike
> >
> > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > introduces the fewest assumptions while still sufficiently answering the
> > question.
> >
> >
> > "CousinExcel" wrote:
> >
> > > I apologize in advance from everybody if my this mail is aginst general rules
> > > of the forum.
> > > I' m new in the forum (~1-2 months old), so I do not know much about the
> > > rules.
> > > He wrote a formula for me
> > > My question was "sumif but there may be also filterings"
> > >
> > > =SUMPRODUCT(--(C1:C10="\$");SUBTOTAL(9;OFFSET(B1:B10;ROW(B1:B10)-MIN(ROW(B1:B10));0;1)))
> > > His formula is working wonderfully.
> > > I am a medium level excel user in general level office environments.
> > > for i = 1 to 20
> > >   I looked at the formula,
> > >   worked on it to understand how it works
> > >   splitted the formula into pieces
> > >   etc
> > > next i
> > > I could not solve that offset part, why, how ?
> > > I will make i=50:), and go on trying to understand it.
> > > Then I recognized that I can see the profiles of people and looked Mike' s
> > > profile
> > > and then understood everything.
> > > He is a 40 years worked, retired chemical engineer.
> > > I' m sure
> > > he is 'inititaing a chemical reaction in excel' with this formula : )
> > > and therefore I can not see, understand how the formula is working.
> > > Thanks and best regards,
> > > Cousin Excel
```
 0
Utf
1/21/2010 12:37:03 PM
```Thank you Master Mike.
If I have not tired you enough, I have question
I can not understand OFFSET part.
I try to run the OFFSET part for B2: to B6.
If I'm not making mistake (I'm sure I'm making),
creating 6 elements of the array:
1) OFFSET(B2:B2;row(B2:B2)-min(row(b2:b2));0;1)
=OFFSET(B2:B2;2-min(2));0;1)
=OFFSET(B2:B2;0;0;1)=6
2) OFFSET(B3:B3;row(B3:B3)-min(row(b3:b3));0;1)
=OFFSET(B3:B3;3-min(3));0;1)
=OFFSET(B3:B3;0;0;1)=7
....goes on.
But if this is corrrect,
then row(...)-min(row(..)) part is always zero.
Then, there will not be need for this part.
But, if I omit even only the word "min" I get wrong result, so it is needed,
so the way I run above is wrong. I tired to find myself and not to tire you
but I could not succeed.
Thanks and best regards,
Cousin Excel

"Mike H" wrote:

> By way of clarification.
>
> 3 arrays are produced but because of the way the formula is constructed the
> sumproduct bit only works on 2 of them
>
>  {1;1;1;1;1}
> {6;7;0;9;10}
>
>
> 1*6=6
> 1*7=7
> 1*0=0
> 1*9=9
> 1*10=10
> =32
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Mike H" wrote:
>
> > Hi,
> >
> > So you want to understand how the formula works. Lets take a shortened
> > version that looks at 5 rows only where row 3 is hidden by an auto filter.
> > Note i've had to change back to , instead of ; for my excel version
> >
> > =SUMPRODUCT(--(C2:C6="\$"),SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)))
> >
> > The formula is basically producing 3 arrays that sumproduct will multiply
> > together
> >
> > --(C1:C6="\$")
> > Because all my cells contain \$ it produces this array. i.e 5 TRUE which the
> > double unary (--) convert to a numeric value
> > {1;1;1;1;1}
> >
> > OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)
> > Produces the array which are the numbers in my cells
> > {6;7;8;9;10}
> >
> > Now we wrap that in a subtotal function where hidden rows evaluate as 0 and
> > visible rows as 1 we get the following array
> > SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1))
> > Because row 3 is hidden it produces the array
> > {6;7;0;9;10}
> >
> > Now we have the 3 arrays we need and Sumproduct will multiply them together
> > {1;1;1;1;1}
> > {6;7;8;9;10}
> > {6;7;0;9;10}
> >
> > Hope this helps
> >
> > --
> > Mike
> >
> > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > introduces the fewest assumptions while still sufficiently answering the
> > question.
> >
> >
> > "CousinExcel" wrote:
> >
> > > I apologize in advance from everybody if my this mail is aginst general rules
> > > of the forum.
> > > I' m new in the forum (~1-2 months old), so I do not know much about the
> > > rules.
> > > He wrote a formula for me
> > > My question was "sumif but there may be also filterings"
> > >
> > > =SUMPRODUCT(--(C1:C10="\$");SUBTOTAL(9;OFFSET(B1:B10;ROW(B1:B10)-MIN(ROW(B1:B10));0;1)))
> > > His formula is working wonderfully.
> > > I am a medium level excel user in general level office environments.
> > > for i = 1 to 20
> > >   I looked at the formula,
> > >   worked on it to understand how it works
> > >   splitted the formula into pieces
> > >   etc
> > > next i
> > > I could not solve that offset part, why, how ?
> > > I will make i=50:), and go on trying to understand it.
> > > Then I recognized that I can see the profiles of people and looked Mike' s
> > > profile
> > > and then understood everything.
> > > He is a 40 years worked, retired chemical engineer.
> > > I' m sure
> > > he is 'inititaing a chemical reaction in excel' with this formula : )
> > > and therefore I can not see, understand how the formula is working.
> > > Thanks and best regards,
> > > Cousin Excel
```
 0
Utf
1/21/2010 2:08:04 PM

Similar Artilces:

We are implementing a new policy for handling users who leave the company. The policy states that when a person is terminated, all of his/her email will be forwarded to their direct supervisor. We will then auto-respond to the sender to notify them that the person no longer works with our company, and give them an alternate contact. I need a solution that uses Exchange 2003 to setup a rule that both forwards messages, and responds to the user who sent the original message. Is there a way to do this in Exchange 2003? It makes sense to me that it would be some type of Server based rule, ...

I just Installed Office 2003. I am trying to define my settings in outlook. I've imported all my mail and filters from OE. I use several different email addresses, How do I automatically get a reply message to be sent from the address that it was addressed to... When I click reply and send a message, it looks like the message is being sent from my default address. Probably more questions to come... Rina -- Please respond to the newsgroup, mail from this account is left on my server Rina wrote: > I just Installed Office 2003. > > I am trying to define my settings in ou...

sumif question #11
I want to sum the values in a column if the text in a different column same row CONTAINS a certain word. ie the text doesnt have to match exactly just include that word. Is the anyway to do this? Cheers, Steven =SUMIF(a5:a10,"*TEXT*",b5:b10) a5-a10 is text field to measure b5-b10 is value to add if true Betaman wrote: > I want to sum the values in a column if the text in a different column same > row CONTAINS a certain word. ie the text doesnt have to match exactly just > include that word. Is the anyway to do this? > Cheers, > Steven I knew there had to be s...

Filter formula 05-19-10
HI I have a database with repeated informations as listed below and is increasing daily Nat Firm Name SPAIN VIAJES EL CORTE INGLES SPAIN VIAJES EL CORTE INGLES SPAIN VIAJES EL CORTE INGLES GREECE AMEX INTERNATIONAL- HOLLAND GERMANY AIRTOURS INTERNATIONAL-GERMAN ITALY DERTOUR GMBH & CO. KG UNITED KINGDOM INTERNATIONAL TRAVEL CONNE BELGIUM RAINBOW TRAVEL LTD I would like to have a formula which will list the firm name once when choosing the country. I don't want to perform it through a pivot table as I am using the sumproduct to add the qty and amount to have a com...

Outlook 2002 SP3 reply emails change to wingdinds font
Outlook 2002 SP3 reply emails change to wingdinds font I recently purchased a HP Pocket PC that apparently has to use Outlook to sync with it's contacts since it came on the companion CD. Since installing Outlook, all of my reply email fonts, including the original message, change to wingdings font. How can I fix this? Also, my Pocket PC uses MS Windows Mobile 2003 Second Edition. Do I have to use Outlook to sync contacts? I prefer Outlook Express. Thanks davidoncoast wrote: > Outlook 2002 SP3 reply emails change to wingdinds font > > I recently purchased a HP Pocket PC tha...

mike
http://www.freewebs.com/wandererartificialstupidity/ http://www.geocities.com/wandererartificialstupidity http://wandererartificialstupidity.tripod.com http://members.aol.com/lalonm/ http://www.wanderers.freewebweb.com/ Wanderer Tax Solutions 702 Barrydowne Road - Unit 9 Sudbury, ON P3A 3T5 705 562 2399 Barrydowne Road Sudbury, Ontario. Second Location. 1630 Agincourt Avenue Sudbury, ON. wanderer rampar wdr c64 commodore 64. jason john lalonde michael lalonde mike lalonde sudbury wanderer tax solutions 3665 Arista Way These test messages are purposely filled with inaccurate a...

How do I delete an auto reply rule from Outlook
I set up an auto reply when I went on holidays. I don't know how I did it, but I just typed in the message reply and it worked. When I came to delete the rule, it was not there. I did not use a template, but somehow I managed to find something that let me do it. I have tried to replicate the rule, but I still can't. It is a POP account, but other than that I am not very computor savvy so if anyone replies, please make it simple! "grants51" <grants51@discussions.microsoft.com> wrote in message news:A01FE189-81BD-4857-A66B-4E7C34A4E74E@microsoft.com... &g...

Boss wants to secretly intercept all outgoing mail for ONE user. h
Dear friends My boss wants to secretly/transparently intercept/divert all outgoing email for one particular user in our system so that the mail can be read by him before it really gets delivered. How can I implement this? Would Exchange 2007 support this tweak? Don't know what version of Exchange you are using, however: I don't know about delaying it's delivery but you can have Exchange 2003 send a copy of all that person's email to your boss. You would go into Active Directory Users and Computers and open that user's AD profile. Go to the "Exchange General&...

Filter Query
Is there a way to filter for records that have one of two values. I have two Yes/No fields, and they can't both be True. I want the records that have one or the other. The way I have it now, the records that have one or the other are grouped on top, but after those records, the records that have neither appear in the form. Given Field1 and Field2, both yes/no, I'd put "<>Field1" under Field2 or visa versa. This will return only records where the two fields are unequal. Todd On Aug 2, 1:26 pm, DandimLee <Dandim...@discussions.microsoft.com> wrote: > Is...

LDAP Returned the error [20] or [34] (EventID: 8270)
We're currently having a problem in Exchange Server 2003. We've been getting these event log messages for quite a while now. We don't see any adverse affect but would like to stop these messages from coming in. What is the cause of these messages, should we be concerned? I've run DomainPrep again on our Root Domain Controller and even rebuilt some Recipient Update Services with no success. Thanks! Here is an example of the Event Log ([20] Error): LDAP returned the error [20] No Such Object when importing the transaction dn: <GUID=33B24F0D-CF67-4BD4-8BD3-8BF95AA...

Signature in Reply messages do not appear
Hello, I posted this message before buty I did got get any help. I believe this is a bug. I am using Outlook 2003 with Win XP. MS Word 2003 is the default editor. I set a signature text for new messages and replies/forwards at Tools>Mail Format. Outlook does not place a signature text to my replies. It always works for new messages but not replies. I have to do it manually. This is very annoying. Please help. Cem Does anyone have a resolution to this problem? It is plauging me as well! Brent "athena" <girit@comcast.net> wrote in message news:<#vhzfBPPEHA.632@...

negative [h]:mm:ss does not show on the x axis of a chart
If you have minus hh:mm:ss time on a chart or pivot chart the x axis does not display any values down it's axis. and when you hove over the bar, value is empty. In Excell 2003 it work fine in a normal chart, but not pivot chart. In Excel 2007 neither work. Also when you select the 1904 date system, the times fly through the roof. time is displayed as 1:00:00 on the worksheet, but within the pivottable / chart they are shown as 35089:00:00, formula in the cell = 3600 / 86400. fields / cells all are formatted as [h]:mm:ss ---------------- This post is a suggestion for Microsoft, an...

PO Commitment feature should work with AA Budgets also
The PO Commitment feature is wonderful but it doesn’t work with AA Budgets. We really need this for Grant Management clients and other AA customers who use AA budgets. See support response below: Microsoft Dynamics Support Incident Number 8833035 : PO Commitments and AA -------------------------------------------------------------------------- 3/13/2007 8:09:00 PM PDT -- Robert We would like to use PO Commitments but it appears that you can only setup budget by GL account. We use AA dimension codes to track spending on projects. Can PO Commitments work with AA? Can it use AA budget...

I have outlook 2002 but I appear not to have out of office reply available to me. I have discovered you need Microsoft Exchange that has an auto reply wizard. I am having the same problem. In Outlook you have to creat rules. If you determine how to write a rule to notify senders that you are Out of the Office, would you please share it with me. Thank you. >-----Original Message----- >I have outlook 2002 but I appear not to have out of office >reply available to me. >. > ...

Outlook
Whenever i try to delete an email ( by clicking the cross-thingy) somehow the next message also deletes from the inbox... i know i only selected one message soo what can this be? The deleted mail can be recovered but it does create the extra task of following the contents of the deleted mail box. Does anyone have a solution? So to summarize: one mail selected --> delete --> multiple messages are deleted.... Thanks in advance. ...

Out of Office restrict replys to contact list
It there a way to restrict out of office reply to only recieptants in the contact list? We would like to turn on out of office reply to people outside our organization, but would liketo restrict it to only people in our contact list. At present we have a rule setup locally on the users system, but would like to have a company wide solution. Thank you On Tue, 18 Apr 2006 11:08:02 -0700, Paul <Paul@discussions.microsoft.com> wrote: >It there a way to restrict out of office reply to only recieptants in the >contact list? > >We would like to turn on out of office reply t...

Charting Items in H:MM:SS format and/or converting H:MM:SS to all
I want to create a chart using a time format. (it was to do with how long it takes different people to do the same function). I can't find a way to do that. So I thought I'd beat the system and just convert it all to all seconds. I can't find a way to do that either. Ideas? 1. To keep it in time format, make sure you enter the times into the cells in H:MM:SS format (or H:MM). If you enter M:SS, Excel's going to interpret it as H:MM. You'll have a column with names and the next column with times. Put a label above the Time column ("Time"), but leave th...

I am having an annoyance in Outlook 2003. Whenever I reply to a specific person Outlook is formatting my text with double spacing. When I enter a carriage return it doubles, but wordwrapped text is not doubled. Is there a way to fix this? It only happens with one specific member of my address book. are you using HTML formatted messages and do they use a web account? If so, use shift-enter, not enter. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ ...

Hi, We have an access db containing email addresses and a word letter containing instructions and hyperlinks. When we do an email merge, the hyperlinks become disabled in the resulting email. When we merge the letter as an attachment, the links are available using CTRL-click. Is there a setting in security on client or our part that causes this to happen? How can we send live links? Thanks. ...

can a workbook with macros created in excel 2003 work in excel 20.
I created a workbook in excel 2003. I then made changes to it on another computer using excel 2000 and saved it to a CD. When I open the file up in excel 2003 I get a message that says "data may be lost". When it does open all the macros are lost. Are ther limitations working between excel 2000 and 2003? I suspect that your problem is not the version difference but is due to the fact that you saved it to a cd. Never save a file to a cd or floppy. Always save the file on the HD and then COPY to the cd. Then, copy from the cd to the HD and then open from the HD. However, there ma...

Cannot Start Microsoft Outlook XP (msg file)
Hello, We store archive copies of messages on our network server (as *.msg). These are saved manually by users from within microsoft outlook 2000 and outlook 2002. Occasionally, when double clicking on these network copies, we get the following errors within outlook: (2002) "Cannot Start Microsoft Outlook" (2000) "The messaging interface has returned an unknown error." This is all made worse by the fact that the message is then corrupted and cannot be opened on any machine at all (even though i know some of the messages have been opened successfully in the past). If i re...

Allen Browne's Filter
I have built a filtering system for our sales order modeled after Allen Browne's Search procedure (http://www.allenbrowne.com/ser-62.html). Some of the criteria are applied to the forms filter, and others to the forms recordsource. I originally made a handful of queries and based on criteria, the code selected the correct query for the recordsource. I'm going to have more options, so I decided it would be better to just have the code 'build' an sql statement for the recordsource. I'm having a problem creating the sql expression. But first I need to find out how to...

XL03 suppress 'missing XLA' msg on Workbooks.open
This started in another thread, but that thread got orphaned and I've got a clearer idea of what is happening now, so reposting in search of additional help. Thank you all for your continued assistance. I have a file that I open with UpdateLinks:=0 My goal is to automatically open this file from a LAN, copy the data (pastespecial/values into my workbook), then close this book. The intent is to have this fully automated. However, when opening the file with VBA (see code below) I get a "find file" type dialog window; it seems to want me to locate an XLA. I'm as...