Editing Lookup Formula Error

I've adapted the following formula from "How to lookup a value..." from 
Office Online:  

=IF(ISERROR(INDEX($A$2:$F$1007,SMALL(IF($B$2:$F$1007=G$1,ROW($A$2:$A$1007)),ROW(1:1)),1)),"",INDEX($A$2:$F$1007,SMALL(IF($B$2:$F$1007=G$1,ROW($A$2:$A$1007)),ROW(1:1)),1))

date	  no1	no2	no3	no4	no5	1
6/7/2008	    2	4	11	15	28	6/24/08
6/10/2008	    8	13	14	23	30	6/28/08
6/14/2008	    5	13	24	35	45	
6/17/2008	   10	12	18	30	36	
6/21/2008	   1	2	5	23	45	
6/24/2008	   1	4	37	45	46	
	    	

However, the date that is returned is from the row BELOW the I would like.  
In other words, in column "1" the dates 6/21/2008 and 6/24/2008 should 
appear.  Any suggestions?  Many thanks.

Bud
0
Utf
1/24/2010 10:31:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

6 Replies
823 Views

Similar Articles

[PageSpeed] 2

It looks like the smallest number that is possible in your data set is the 
number 1. Is that correct? It also looks like the numbers are in ascending 
order from left to right. So, if my guess is correct then you should only 
need to test the first column of the number data for the number 1.

To improve the formula based on my interpretation...

Enter this formula in H1. This will return the count of 1s in column B.

=COUNTIF(B2:B1007,1)

Enter this array formula** in G2:

=IF(ROWS(G$2:G2)>H$1,"",INDEX(A:A,SMALL(IF(B$2:B$1007=G$1,ROW(A$2:A$1007)),ROWS(G$2:G2))))

** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
key then hit ENTER.

Format as Date. Copy down until you get blanks.

-- 
Biff
Microsoft Excel MVP


"Bud" <Bud@discussions.microsoft.com> wrote in message 
news:6581B3CB-2073-45F6-95B6-4B1B9668434F@microsoft.com...
> I've adapted the following formula from "How to lookup a value..." from
> Office Online:
>
> =IF(ISERROR(INDEX($A$2:$F$1007,SMALL(IF($B$2:$F$1007=G$1,ROW($A$2:$A$1007)),ROW(1:1)),1)),"",INDEX($A$2:$F$1007,SMALL(IF($B$2:$F$1007=G$1,ROW($A$2:$A$1007)),ROW(1:1)),1))
>
> date   no1 no2 no3 no4 no5 1
> 6/7/2008     2 4 11 15 28 6/24/08
> 6/10/2008     8 13 14 23 30 6/28/08
> 6/14/2008     5 13 24 35 45
> 6/17/2008    10 12 18 30 36
> 6/21/2008    1 2 5 23 45
> 6/24/2008    1 4 37 45 46
>
>
> However, the date that is returned is from the row BELOW the I would like.
> In other words, in column "1" the dates 6/21/2008 and 6/24/2008 should
> appear.  Any suggestions?  Many thanks.
>
> Bud 


0
T
1/25/2010 2:37:36 AM
Thanks for your response.  I should have been more specific.  While the 
smallest number in my dataset is 1, the largest is lookup number is 52 and 
the remainder appear in row 1 to the right of the number 1 of my very brief 
example.  So my objective is to identify (i.e., list) each date that any of 
the 52 possible numbers appear, regardless of the column (i.e., no1, no2, 
no3, etc) that that specific number appeared.  The dates are arranged in 
increasing date order and the numbers are increasing in each row.  Since 
there are over 2,000 rows of data, I was thinking that I could copy an array 
formula down and across (formatting as date) without having multiple columns 
for each lookup number.  Can you provide some guidance?

Oh and by the way, I have seen many of your posts and I must acknowledge how 
insightful and accurate you are!  Cheers.

Bud

"T. Valko" wrote:

> It looks like the smallest number that is possible in your data set is the 
> number 1. Is that correct? It also looks like the numbers are in ascending 
> order from left to right. So, if my guess is correct then you should only 
> need to test the first column of the number data for the number 1.
> 
> To improve the formula based on my interpretation...
> 
> Enter this formula in H1. This will return the count of 1s in column B.
> 
> =COUNTIF(B2:B1007,1)
> 
> Enter this array formula** in G2:
> 
> =IF(ROWS(G$2:G2)>H$1,"",INDEX(A:A,SMALL(IF(B$2:B$1007=G$1,ROW(A$2:A$1007)),ROWS(G$2:G2))))
> 
> ** array formulas need to be entered using the key combination of 
> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
> key then hit ENTER.
> 
> Format as Date. Copy down until you get blanks.
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Bud" <Bud@discussions.microsoft.com> wrote in message 
> news:6581B3CB-2073-45F6-95B6-4B1B9668434F@microsoft.com...
> > I've adapted the following formula from "How to lookup a value..." from
> > Office Online:
> >
> > =IF(ISERROR(INDEX($A$2:$F$1007,SMALL(IF($B$2:$F$1007=G$1,ROW($A$2:$A$1007)),ROW(1:1)),1)),"",INDEX($A$2:$F$1007,SMALL(IF($B$2:$F$1007=G$1,ROW($A$2:$A$1007)),ROW(1:1)),1))
> >
> > date   no1 no2 no3 no4 no5 1
> > 6/7/2008     2 4 11 15 28 6/24/08
> > 6/10/2008     8 13 14 23 30 6/28/08
> > 6/14/2008     5 13 24 35 45
> > 6/17/2008    10 12 18 30 36
> > 6/21/2008    1 2 5 23 45
> > 6/24/2008    1 4 37 45 46
> >
> >
> > However, the date that is returned is from the row BELOW the I would like.
> > In other words, in column "1" the dates 6/21/2008 and 6/24/2008 should
> > appear.  Any suggestions?  Many thanks.
> >
> > Bud 
> 
> 
> .
> 
0
Utf
1/29/2010 2:17:01 AM
Here's a small sample file that demonstrates this.

Bud.xls  21kb

http://cjoint.com/?bDd4VvyxrT

-- 
Biff
Microsoft Excel MVP


"Bud" <Bud@discussions.microsoft.com> wrote in message 
news:AC9C75AF-E643-4D1A-BCA5-010F6EAABDF0@microsoft.com...
> Thanks for your response.  I should have been more specific.  While the
> smallest number in my dataset is 1, the largest is lookup number is 52 and
> the remainder appear in row 1 to the right of the number 1 of my very 
> brief
> example.  So my objective is to identify (i.e., list) each date that any 
> of
> the 52 possible numbers appear, regardless of the column (i.e., no1, no2,
> no3, etc) that that specific number appeared.  The dates are arranged in
> increasing date order and the numbers are increasing in each row.  Since
> there are over 2,000 rows of data, I was thinking that I could copy an 
> array
> formula down and across (formatting as date) without having multiple 
> columns
> for each lookup number.  Can you provide some guidance?
>
> Oh and by the way, I have seen many of your posts and I must acknowledge 
> how
> insightful and accurate you are!  Cheers.
>
> Bud
>
> "T. Valko" wrote:
>
>> It looks like the smallest number that is possible in your data set is 
>> the
>> number 1. Is that correct? It also looks like the numbers are in 
>> ascending
>> order from left to right. So, if my guess is correct then you should only
>> need to test the first column of the number data for the number 1.
>>
>> To improve the formula based on my interpretation...
>>
>> Enter this formula in H1. This will return the count of 1s in column B.
>>
>> =COUNTIF(B2:B1007,1)
>>
>> Enter this array formula** in G2:
>>
>> =IF(ROWS(G$2:G2)>H$1,"",INDEX(A:A,SMALL(IF(B$2:B$1007=G$1,ROW(A$2:A$1007)),ROWS(G$2:G2))))
>>
>> ** array formulas need to be entered using the key combination of
>> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the 
>> SHIFT
>> key then hit ENTER.
>>
>> Format as Date. Copy down until you get blanks.
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Bud" <Bud@discussions.microsoft.com> wrote in message
>> news:6581B3CB-2073-45F6-95B6-4B1B9668434F@microsoft.com...
>> > I've adapted the following formula from "How to lookup a value..." from
>> > Office Online:
>> >
>> > =IF(ISERROR(INDEX($A$2:$F$1007,SMALL(IF($B$2:$F$1007=G$1,ROW($A$2:$A$1007)),ROW(1:1)),1)),"",INDEX($A$2:$F$1007,SMALL(IF($B$2:$F$1007=G$1,ROW($A$2:$A$1007)),ROW(1:1)),1))
>> >
>> > date   no1 no2 no3 no4 no5 1
>> > 6/7/2008     2 4 11 15 28 6/24/08
>> > 6/10/2008     8 13 14 23 30 6/28/08
>> > 6/14/2008     5 13 24 35 45
>> > 6/17/2008    10 12 18 30 36
>> > 6/21/2008    1 2 5 23 45
>> > 6/24/2008    1 4 37 45 46
>> >
>> >
>> > However, the date that is returned is from the row BELOW the I would 
>> > like.
>> > In other words, in column "1" the dates 6/21/2008 and 6/24/2008 should
>> > appear.  Any suggestions?  Many thanks.
>> >
>> > Bud
>>
>>
>> .
>> 


0
T
1/29/2010 2:58:18 AM
wonderful... i'll give it a try.

"T. Valko" wrote:

> Here's a small sample file that demonstrates this.
> 
> Bud.xls  21kb
> 
> http://cjoint.com/?bDd4VvyxrT
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Bud" <Bud@discussions.microsoft.com> wrote in message 
> news:AC9C75AF-E643-4D1A-BCA5-010F6EAABDF0@microsoft.com...
> > Thanks for your response.  I should have been more specific.  While the
> > smallest number in my dataset is 1, the largest is lookup number is 52 and
> > the remainder appear in row 1 to the right of the number 1 of my very 
> > brief
> > example.  So my objective is to identify (i.e., list) each date that any 
> > of
> > the 52 possible numbers appear, regardless of the column (i.e., no1, no2,
> > no3, etc) that that specific number appeared.  The dates are arranged in
> > increasing date order and the numbers are increasing in each row.  Since
> > there are over 2,000 rows of data, I was thinking that I could copy an 
> > array
> > formula down and across (formatting as date) without having multiple 
> > columns
> > for each lookup number.  Can you provide some guidance?
> >
> > Oh and by the way, I have seen many of your posts and I must acknowledge 
> > how
> > insightful and accurate you are!  Cheers.
> >
> > Bud
> >
> > "T. Valko" wrote:
> >
> >> It looks like the smallest number that is possible in your data set is 
> >> the
> >> number 1. Is that correct? It also looks like the numbers are in 
> >> ascending
> >> order from left to right. So, if my guess is correct then you should only
> >> need to test the first column of the number data for the number 1.
> >>
> >> To improve the formula based on my interpretation...
> >>
> >> Enter this formula in H1. This will return the count of 1s in column B.
> >>
> >> =COUNTIF(B2:B1007,1)
> >>
> >> Enter this array formula** in G2:
> >>
> >> =IF(ROWS(G$2:G2)>H$1,"",INDEX(A:A,SMALL(IF(B$2:B$1007=G$1,ROW(A$2:A$1007)),ROWS(G$2:G2))))
> >>
> >> ** array formulas need to be entered using the key combination of
> >> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the 
> >> SHIFT
> >> key then hit ENTER.
> >>
> >> Format as Date. Copy down until you get blanks.
> >>
> >> -- 
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Bud" <Bud@discussions.microsoft.com> wrote in message
> >> news:6581B3CB-2073-45F6-95B6-4B1B9668434F@microsoft.com...
> >> > I've adapted the following formula from "How to lookup a value..." from
> >> > Office Online:
> >> >
> >> > =IF(ISERROR(INDEX($A$2:$F$1007,SMALL(IF($B$2:$F$1007=G$1,ROW($A$2:$A$1007)),ROW(1:1)),1)),"",INDEX($A$2:$F$1007,SMALL(IF($B$2:$F$1007=G$1,ROW($A$2:$A$1007)),ROW(1:1)),1))
> >> >
> >> > date   no1 no2 no3 no4 no5 1
> >> > 6/7/2008     2 4 11 15 28 6/24/08
> >> > 6/10/2008     8 13 14 23 30 6/28/08
> >> > 6/14/2008     5 13 24 35 45
> >> > 6/17/2008    10 12 18 30 36
> >> > 6/21/2008    1 2 5 23 45
> >> > 6/24/2008    1 4 37 45 46
> >> >
> >> >
> >> > However, the date that is returned is from the row BELOW the I would 
> >> > like.
> >> > In other words, in column "1" the dates 6/21/2008 and 6/24/2008 should
> >> > appear.  Any suggestions?  Many thanks.
> >> >
> >> > Bud
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
1/29/2010 5:07:01 AM
it works... a million thanks!!!

Bud

"Bud" wrote:

> wonderful... i'll give it a try.
> 
> "T. Valko" wrote:
> 
> > Here's a small sample file that demonstrates this.
> > 
> > Bud.xls  21kb
> > 
> > http://cjoint.com/?bDd4VvyxrT
> > 
> > -- 
> > Biff
> > Microsoft Excel MVP
> > 
> > 
> > "Bud" <Bud@discussions.microsoft.com> wrote in message 
> > news:AC9C75AF-E643-4D1A-BCA5-010F6EAABDF0@microsoft.com...
> > > Thanks for your response.  I should have been more specific.  While the
> > > smallest number in my dataset is 1, the largest is lookup number is 52 and
> > > the remainder appear in row 1 to the right of the number 1 of my very 
> > > brief
> > > example.  So my objective is to identify (i.e., list) each date that any 
> > > of
> > > the 52 possible numbers appear, regardless of the column (i.e., no1, no2,
> > > no3, etc) that that specific number appeared.  The dates are arranged in
> > > increasing date order and the numbers are increasing in each row.  Since
> > > there are over 2,000 rows of data, I was thinking that I could copy an 
> > > array
> > > formula down and across (formatting as date) without having multiple 
> > > columns
> > > for each lookup number.  Can you provide some guidance?
> > >
> > > Oh and by the way, I have seen many of your posts and I must acknowledge 
> > > how
> > > insightful and accurate you are!  Cheers.
> > >
> > > Bud
> > >
> > > "T. Valko" wrote:
> > >
> > >> It looks like the smallest number that is possible in your data set is 
> > >> the
> > >> number 1. Is that correct? It also looks like the numbers are in 
> > >> ascending
> > >> order from left to right. So, if my guess is correct then you should only
> > >> need to test the first column of the number data for the number 1.
> > >>
> > >> To improve the formula based on my interpretation...
> > >>
> > >> Enter this formula in H1. This will return the count of 1s in column B.
> > >>
> > >> =COUNTIF(B2:B1007,1)
> > >>
> > >> Enter this array formula** in G2:
> > >>
> > >> =IF(ROWS(G$2:G2)>H$1,"",INDEX(A:A,SMALL(IF(B$2:B$1007=G$1,ROW(A$2:A$1007)),ROWS(G$2:G2))))
> > >>
> > >> ** array formulas need to be entered using the key combination of
> > >> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the 
> > >> SHIFT
> > >> key then hit ENTER.
> > >>
> > >> Format as Date. Copy down until you get blanks.
> > >>
> > >> -- 
> > >> Biff
> > >> Microsoft Excel MVP
> > >>
> > >>
> > >> "Bud" <Bud@discussions.microsoft.com> wrote in message
> > >> news:6581B3CB-2073-45F6-95B6-4B1B9668434F@microsoft.com...
> > >> > I've adapted the following formula from "How to lookup a value..." from
> > >> > Office Online:
> > >> >
> > >> > =IF(ISERROR(INDEX($A$2:$F$1007,SMALL(IF($B$2:$F$1007=G$1,ROW($A$2:$A$1007)),ROW(1:1)),1)),"",INDEX($A$2:$F$1007,SMALL(IF($B$2:$F$1007=G$1,ROW($A$2:$A$1007)),ROW(1:1)),1))
> > >> >
> > >> > date   no1 no2 no3 no4 no5 1
> > >> > 6/7/2008     2 4 11 15 28 6/24/08
> > >> > 6/10/2008     8 13 14 23 30 6/28/08
> > >> > 6/14/2008     5 13 24 35 45
> > >> > 6/17/2008    10 12 18 30 36
> > >> > 6/21/2008    1 2 5 23 45
> > >> > 6/24/2008    1 4 37 45 46
> > >> >
> > >> >
> > >> > However, the date that is returned is from the row BELOW the I would 
> > >> > like.
> > >> > In other words, in column "1" the dates 6/21/2008 and 6/24/2008 should
> > >> > appear.  Any suggestions?  Many thanks.
> > >> >
> > >> > Bud
> > >>
> > >>
> > >> .
> > >> 
> > 
> > 
> > .
> > 
0
Utf
1/29/2010 11:33:02 AM
You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"Bud" <Bud@discussions.microsoft.com> wrote in message 
news:68DFE546-869E-405B-9F8C-DA5653FAE4A6@microsoft.com...
> it works... a million thanks!!!
>
> Bud
>
> "Bud" wrote:
>
>> wonderful... i'll give it a try.
>>
>> "T. Valko" wrote:
>>
>> > Here's a small sample file that demonstrates this.
>> >
>> > Bud.xls  21kb
>> >
>> > http://cjoint.com/?bDd4VvyxrT
>> >
>> > -- 
>> > Biff
>> > Microsoft Excel MVP
>> >
>> >
>> > "Bud" <Bud@discussions.microsoft.com> wrote in message
>> > news:AC9C75AF-E643-4D1A-BCA5-010F6EAABDF0@microsoft.com...
>> > > Thanks for your response.  I should have been more specific.  While 
>> > > the
>> > > smallest number in my dataset is 1, the largest is lookup number is 
>> > > 52 and
>> > > the remainder appear in row 1 to the right of the number 1 of my very
>> > > brief
>> > > example.  So my objective is to identify (i.e., list) each date that 
>> > > any
>> > > of
>> > > the 52 possible numbers appear, regardless of the column (i.e., no1, 
>> > > no2,
>> > > no3, etc) that that specific number appeared.  The dates are arranged 
>> > > in
>> > > increasing date order and the numbers are increasing in each row. 
>> > > Since
>> > > there are over 2,000 rows of data, I was thinking that I could copy 
>> > > an
>> > > array
>> > > formula down and across (formatting as date) without having multiple
>> > > columns
>> > > for each lookup number.  Can you provide some guidance?
>> > >
>> > > Oh and by the way, I have seen many of your posts and I must 
>> > > acknowledge
>> > > how
>> > > insightful and accurate you are!  Cheers.
>> > >
>> > > Bud
>> > >
>> > > "T. Valko" wrote:
>> > >
>> > >> It looks like the smallest number that is possible in your data set 
>> > >> is
>> > >> the
>> > >> number 1. Is that correct? It also looks like the numbers are in
>> > >> ascending
>> > >> order from left to right. So, if my guess is correct then you should 
>> > >> only
>> > >> need to test the first column of the number data for the number 1.
>> > >>
>> > >> To improve the formula based on my interpretation...
>> > >>
>> > >> Enter this formula in H1. This will return the count of 1s in column 
>> > >> B.
>> > >>
>> > >> =COUNTIF(B2:B1007,1)
>> > >>
>> > >> Enter this array formula** in G2:
>> > >>
>> > >> =IF(ROWS(G$2:G2)>H$1,"",INDEX(A:A,SMALL(IF(B$2:B$1007=G$1,ROW(A$2:A$1007)),ROWS(G$2:G2))))
>> > >>
>> > >> ** array formulas need to be entered using the key combination of
>> > >> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and 
>> > >> the
>> > >> SHIFT
>> > >> key then hit ENTER.
>> > >>
>> > >> Format as Date. Copy down until you get blanks.
>> > >>
>> > >> -- 
>> > >> Biff
>> > >> Microsoft Excel MVP
>> > >>
>> > >>
>> > >> "Bud" <Bud@discussions.microsoft.com> wrote in message
>> > >> news:6581B3CB-2073-45F6-95B6-4B1B9668434F@microsoft.com...
>> > >> > I've adapted the following formula from "How to lookup a value..." 
>> > >> > from
>> > >> > Office Online:
>> > >> >
>> > >> > =IF(ISERROR(INDEX($A$2:$F$1007,SMALL(IF($B$2:$F$1007=G$1,ROW($A$2:$A$1007)),ROW(1:1)),1)),"",INDEX($A$2:$F$1007,SMALL(IF($B$2:$F$1007=G$1,ROW($A$2:$A$1007)),ROW(1:1)),1))
>> > >> >
>> > >> > date   no1 no2 no3 no4 no5 1
>> > >> > 6/7/2008     2 4 11 15 28 6/24/08
>> > >> > 6/10/2008     8 13 14 23 30 6/28/08
>> > >> > 6/14/2008     5 13 24 35 45
>> > >> > 6/17/2008    10 12 18 30 36
>> > >> > 6/21/2008    1 2 5 23 45
>> > >> > 6/24/2008    1 4 37 45 46
>> > >> >
>> > >> >
>> > >> > However, the date that is returned is from the row BELOW the I 
>> > >> > would
>> > >> > like.
>> > >> > In other words, in column "1" the dates 6/21/2008 and 6/24/2008 
>> > >> > should
>> > >> > appear.  Any suggestions?  Many thanks.
>> > >> >
>> > >> > Bud
>> > >>
>> > >>
>> > >> .
>> > >>
>> >
>> >
>> > .
>> > 


0
T
1/29/2010 11:30:27 PM
Reply:

Similar Artilces:

Syntax error (missing operator) 04-06-10
Hi, I used the following code supplied by Rick Brandt to a message from a 2005 post "Open Forms in Hidden Mode" where user was using one form to open another form to extract data. Dim db as Database Dim rs as Recordset Dim strSQL as String strSQL = "SELECT FieldName " & _ "FROM TableName " & _ "WHERE SomeField = SomeValue" Set db = CurrentDB Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot) If rs.EOF = True Then 'No matching records found (do whatever) Else 'In here values can be extracted by... SomeVariable = rs!Fi...

Help on subscript out of range error (VB6/VBA)
Hi, I want to work with multiple excel workbooks en worksheets using a stand alone VB6 application. 1. First I open and close the workbooks and populate a listbox with sheetnames. 2. Then I select some sheets and fill an Array. (This array contains sheetnames from different workbooks) 3. The last step is to open and close the workbooks again and loop (For Each) through the sheets to get the information I need using the above mentioned array. The only problem is that I receive an "Subscript out of Range" error. This error occurs because I'm attempting tot access a workshee...

HELP! **Transfer orders to invoices error
I need some serious help. We just upgraded from GP 7.5 to GP 8.0 and now I am unable to transfer orders to invoices. I keep getting an error that says "Order ORD1555 has no line items to transfer to Invoice INV144408." Order# 1555 does have about 10 line items, which I can view on the screen. I did go in and run the "Check Links" function, but it hasn't done anything. This is our busiest time of year and a really bad time for something like this to happen. Can someone please give me some advice on how to fix this? if this is just one order, why not cancel...

MSExchangeIS Public Store Errors 1110
We are running Exchange Verson 6.0 (Build 6249.4; Service Pack 3 We are getting the following error 39 times every hour Event Type: Erro Event Source: MSExchangeIS Public Stor Event Category: General Event ID: 111 Date: 2/20/200 Time: 6:21:36 P User: N/ Computer: MAI Description Error 0x0 occurred while writing per-user information for <user@outdomain.com> on database "Staff\Public Folder Store (MAIL)". For more information, click http://www.microsoft.com/contentredirect.asp. This error is supposed to be solved by the service pack (Q312966). We applied...

We are getting stat=Deferred: Connection reset errors .....
Does anyone know how to fix this issue? I am getting a lot of emails BUT an Exchange admin from another company that sends SMTP email to is saying that he sees a lot of stat=Deferred: Connection reset error messages in his log file. very very weird. Has anyone seen this issue? He can telnet to the server fine etc... plus I am getting email from his company but it is deferring aa ton though. Thanks What version of Exchange and SP level Oliver Exchange 5.5 SP 4 is our IMS. "Oliver Moazzezi" wrote: > What version of Exchange and SP level > > Oliver > >...

Letter Writing Assistant selection based on Smart List Lookup
We had a great opportunity to use the letter writing assistance to send out a notice to any vendor used within the last 6 months. Using the smartlist lookup up we can see those vendors we have issued a check to based on Last Check date, but the Letter Writing Assistant only allows you to select ranges. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the ...

Object or class does not support the set of events Error
This post is for anyone who has seen this problem. I have posted a solution below. Situation: The error message "Object or class does not support the set of events" occurred in my database just after I received a "database corruption error". The corruption error appeared right after I closed then reopened my database. I assumed this happened because I opened it immediately after it crashed and I had recently set the database to auto compact & repair on close. MS Access asked me if I wanted to save a backup, and I said yes. After this, neither the original database...

multiple text edit
Hi all, I'm a beginner when it comes to Visio. I’m using Visio 2007 and I’m wondering if the following is possible. What I want to be able to do is to change the text in a number of textboxes by just editing one of them - that is, when I change the text in one textbox, the three other textboxes will display what I’ve just written automatically. Can this be done? Thanks in advance. the answer is "kinda". You can coordinate text between shapes so that changing a specific shape will be mirrored to others. It's a one to many, you don't get the option of changing anyo...

Fixing a formula
I have the following equation, it is looking up in another worksheet an pulling through information when it finds a match in second file fro cell d in first file. =VLOOKUP(D10,'C:\New Code Se up\[gcodenewsetupcopy.xls]Sheet1'!C4:O81,4,FALSE) This is working but when i copy the equation down a few rows th equation changes to this and it should still be looking at Sheet 1 C to O81. =VLOOKUP(D14,'C:\New Code Se up\[gcodenewsetupcopy.xls]Sheet1'!C8:O85,4,FALSE) I know i need to fix part of the equation but don't know how or whic bit. Please help -- Boethius -----------...

Replacing part of a formula
Currently I am working on a project that uses formulas to referenc another sheet in the workbook. I plan to use the newly created shee monthly. However I am in need of changing part of the cell formula. Example one cell says =Nov!D12 and I am needing to change it to rea =Dec!D12 or =Jan!D12 etc.... Any help -- Message posted from http://www.ExcelForum.com One way is to use the INDIRECT function when creating your formulas, eg:- Assuming your month was in say A3 then =INDIRECT(A3&"!A1") will give you the data from cell A1 on whatever sheet is listed in A3. Another option i...

Formula Help #23
If I have a number in cell A1 like 5 and another number in cell A3 like 6 I want the number in A7 to show -1 and if cell A3 was 4 to read +1 and if the numbers are 2 away from A1 then the numbers in cell A7 would be -2 or +2 depending on the number in cell A3. All help is appreciated. Thanks Karl You must be kidding! =A1-A3 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Karl" <Karl@discussions.microsoft.com> wrote in message news:21FB7D9E-DC79-42...

Another oddball WLM error message
When I left WLM running while I went to breakfast this morning, I got another oddball error message. Just the frame for showing the error message, with all the space inside the frame still transparent. Robert Miles ...

is there a way to check flowcharts for programming errors?
I am in an intro to programming class and have no experience with programming. Does the Visio program offer a way to double check your flowchart for programming errors? I am a terrible perfectionist and don't like to turn something in until I have double checked it first. Thanks! Visio does not check interpret the flow chart thus you should implement the flow chart in some programming language and check the results. Deepa -- http://www.EventHelix.com/EventStudio EventStudio 2.5 - Generate Sequence diagrams from a simple declarative language ...

ERROR 20488
I am using GP 7.0. I am helping to set up a new additional company. And I have posted many entries from Sub-ledger model to G/L model, and they worked fine. However, last Friday, when I post from Bank transaction model to G/L model, the system shows: DBMS 0, Great Plains 20488. I searched that in the knowledge base, they said that it might be the Posting Definitions Master talbe is damaged. But the above solution applies to GP 8.0, I only use GP7.0, and my posting is OK before. Can anyone help me on that? Thanks, Gavin ...

Error created when try to Print Preview or Print
When try to print or print preview system is generating an error: "Print titles must be contiguous and complete rows or columns." Used excel for years ever seen this error before. Using Excel 2007 on Vista, we just upgraded to SP2 for both systems. After testing a few times, I notice that system is generating a Name Range with no referencing cell range, the name range is called Print_Titles. If I delete the name range it removes the error message and able to use print preview and able to print. Notice it is occurring more ofter when client is copying a worksheet to ...

Entourage 2008 Web Services Edition cannot connect to Exchange Server 2007
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange I have been using Entourage 2008 to connect to our Exchange server since it's release (part of Office 2008) and I have recently followed the instructions to update it to the Web Services Edition. I am now unable to connect to my Exchange account with this version but as soon as I re-install the 'normal' version of Entourage 2008 everything works. <br><br>Is there something I need to enable on the Exchange 2007 server to allow the Web Services Edition to connect? ...

Error 9316
Hi, I've been seeing error 9316 in my logs. Looked it up in technet and found article Q247782 which recomended I set the XAPI concurent connections to 50. It was already set at 50 and I can find no further info. Before I do the obvious hit in the dark I though I'd ask if anyone else has had the same experience? TIA, Pete ...

451 Internal error
Hi, I would appreciate any help on this problem. When trying to send a single 5 meg file through my web smtp server (Go Daddy) I get a return error message stating; "your outgoing (smtp) e-mail server has reported an internal error".. "The server responded: 451 Internal error" Note: I can send a 3 meg file with no problems and can receive a 5 meg file. Go Daddy tech support person said "it appears the error you are receiving is due to a program running on the server with is aimed at blocking viruses being sent out, and the server also runs anti-phishing software. T...

Outlook IMAP Error
I'm getting the following intermittent error when Outlook tries to receive mail from an IMAP server: Task 'Checking for new mail in subscribed folders on <email address>.' reported error (0x8004DF0B) : 'Outlook is unable to download folder (null) from the IMAP e-mail server for account <email address>. Error: The connection to the server is unavailable. Outlook must be online or connected to complete this action. If you continue to receive this message, contact your server administrator or Internet service provider (ISP).' We are on a wired LAN, and ...

Microsoft Office Small Business Edition 2003
Just acquired new Lap Top with the above loaded. I confirmed codes on phone and have operational. Would now like to have on my other 2 office computers to maintain comformity. What does it cost to allow this to happen and what is the process? I'm currently running Office 200 on other computers. Fred Fred wrote: > Just acquired new Lap Top with the above loaded. I > confirmed codes on phone and have operational. Would now > like to have on my other 2 office computers to maintain > comformity. What does it cost to allow this to happen and > what is the process? I'...

Excel macro error msg..
Hi I get the following msgbox when I attempt to run a Macro - which is just a simple text string to be put into a cell. " 'xxx'.xls could not be found. Check the spelling of the file name, and verify that the file location is correct. If you are trying to open a file from your list of most recently used files on the file menu, make sure that the file has not been renamed , moved or deleted." (ok) 'xxx.xls' trefers to a file name that was on my disk - but I deleted - I can't find it after a search. All macros when run, exhibit the same thing..... Tried a goog...

Outlook 2003
Getting "The messaging interface has returned an unknown error. If problem persits, restart Outlook." when attempting to forward, send or reply, using Outlook 2003. Restarting does not help. If you delete the NT Profile the problem goes away for a few days and then returns. We have also tried "Detect and repair" and Uninstall / re-install without good results. Only replacing the NT Profile seems to have an impact. Help! ...

Percentage formula in Excel spreadsheet
Hi, In my spreadsheet cell E14 adds up E2 - E14 and cell C14 adds up C2 - C14. In G14 I have put a formula to show the difference between E14 and C14 as a Percentage ( formula for G14 is =SUM(E14-C14)/ABS(E14) ). When the Formula for G14 looks at E14 and C14 all it sees is a SUM formula to add up the column. Does anyone know how to solve this problem. Many thanks for looking. Rick Not sure I understand the problem; tell us what answer or error you ar getting. Also, don't you mean you are summing C2:C*13* in C14 and E2:E*13* i E14? Oh, and you can get rid of the Sum - this ...

Correcting errors
Is there any way to change the tender type that a Cashier has entered if they do it wrong? Someone settles the credit card as AMEX when it's really a Visa and I want the Z report to be correct. Any ideas? If you still have the card available, void the sale then re-enter it. If you don't have the card, it's really not worth the trouble or the risk... To prevent this from happening again, setup Validation masks for each credit card tender type. Amex: 3############## MC 5############### Visa 4############### Discover 6############### Glenn Adams Tiber Creek Consulting http://...

error when opening large file
Hi, I am trying to open a large file in Publisher 2002, and Publisher just closes right up. ANy clues? How large is large? Do you get any error message? Did you create this file or was it passed on to you? -- JoAnn Paules - MVP Microsoft Publisher "sherri" <anonymous@discussions.microsoft.com> wrote in message news:1d10701c4229e$127b63b0$a101280a@phx.gbl... > Hi, I am trying to open a large file in Publisher 2002, > and Publisher just closes right up. > > ANy clues? Have you got the garbage trash junk Symantec Norton installed? If you have, RE-move/UN-i...