Formula works in some cells, doesn't in other

I have a multi-sheet 2003 workbook.

Sheet 1 is a summary that displays data from the other 4 sheets, the name of 
one of which is "Northeast" (though experimentation proves that it doesn't 
matter what the sheet is named)

On my summary sheet this particular sheet is referenced in row 4.  Column A
is nothing but the text "Northeast", the formulas in each of the next
columns are as follows (without the B: C: D: E: and F:)

B:  =SUMIF(Northeast!C:C,">0",Northeast!B:B)
C:  =SUM(Northeast!C:C)
D:  =SUM(Northeast!D:D)
E:  =1-B4/ABS(C4)
F:  =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))

The other rows have identical formula constructs with only the referenced
sheet name changed and all work perfectly.

As is F4 displays "#VALUE".  I have verified that the formula is exactly as 
it should be and aside from the reference to that particular worksheet is 
identical to the formula in column F in the other rows.

To further confuse me I did a little experimenting and discovered that the
formula works in some rows but not in others.  I copied the formula and
pasted it into several other rows.  Starting with row 6 this is how the
sheet now appears:

56.16666667
#VALUE!
56.16666667
56.16666667
#VALUE!
#VALUE!
56.16666667
56.16666667
56.16666667
56.16666667
56.16666667
#VALUE!

To be very clear, in F6 the formula

=AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
results in 56.16666667

In F7 the formula
=AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
results in #VALUE!

Can anybody shed some light on the situation?  Why does the result of a 
formula depend on which cell it is in?


0
Wowbagger
5/23/2005 9:25:36 PM
excel.newusers 15348 articles. 2 followers. Follow

13 Replies
719 Views

Similar Articles

[PageSpeed] 43

The formula in F4:
=AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
is an array formula (you used ctrl-shift-enter instead of just entering it into
the cell)

But array formulas don't work on whole columns:
=AVERAGE(IF(Northeast!B1:B65535<>0, Northeast!B1:B65535,""))

Should work ok.  (But I'd use a range that was big enough--but not too big.)

Wowbagger wrote:
> 
> I have a multi-sheet 2003 workbook.
> 
> Sheet 1 is a summary that displays data from the other 4 sheets, the name of
> one of which is "Northeast" (though experimentation proves that it doesn't
> matter what the sheet is named)
> 
> On my summary sheet this particular sheet is referenced in row 4.  Column A
> is nothing but the text "Northeast", the formulas in each of the next
> columns are as follows (without the B: C: D: E: and F:)
> 
> B:  =SUMIF(Northeast!C:C,">0",Northeast!B:B)
> C:  =SUM(Northeast!C:C)
> D:  =SUM(Northeast!D:D)
> E:  =1-B4/ABS(C4)
> F:  =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> 
> The other rows have identical formula constructs with only the referenced
> sheet name changed and all work perfectly.
> 
> As is F4 displays "#VALUE".  I have verified that the formula is exactly as
> it should be and aside from the reference to that particular worksheet is
> identical to the formula in column F in the other rows.
> 
> To further confuse me I did a little experimenting and discovered that the
> formula works in some rows but not in others.  I copied the formula and
> pasted it into several other rows.  Starting with row 6 this is how the
> sheet now appears:
> 
> 56.16666667
> #VALUE!
> 56.16666667
> 56.16666667
> #VALUE!
> #VALUE!
> 56.16666667
> 56.16666667
> 56.16666667
> 56.16666667
> 56.16666667
> #VALUE!
> 
> To be very clear, in F6 the formula
> 
> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> results in 56.16666667
> 
> In F7 the formula
> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> results in #VALUE!
> 
> Can anybody shed some light on the situation?  Why does the result of a
> formula depend on which cell it is in?

-- 

Dave Peterson
0
ec357201 (5290)
5/23/2005 10:47:20 PM
Just some guesses / thoughts ..

> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
The above will return #NUM! anywhere if it is *correctly* entered with
CTRL+SHIFT+ENTER (instead of just pressing ENTER). It's an array formula
which requires that special way of entering the formula. And think the error
#NUM! results because you can't use entire column references (B:B, C:C, etc)
in the formula

Try instead, something like:
=AVERAGE(IF(Northeast!B1:B10<>0, Northeast!B1:B10,""))
and array-enter the formula with CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"Wowbagger" <none> wrote in message
news:eCAbB29XFHA.4032@tk2msftngp13.phx.gbl...
> I have a multi-sheet 2003 workbook.
>
> Sheet 1 is a summary that displays data from the other 4 sheets, the name
of
> one of which is "Northeast" (though experimentation proves that it doesn't
> matter what the sheet is named)
>
> On my summary sheet this particular sheet is referenced in row 4.  Column
A
> is nothing but the text "Northeast", the formulas in each of the next
> columns are as follows (without the B: C: D: E: and F:)
>
> B:  =SUMIF(Northeast!C:C,">0",Northeast!B:B)
> C:  =SUM(Northeast!C:C)
> D:  =SUM(Northeast!D:D)
> E:  =1-B4/ABS(C4)
> F:  =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
>
> The other rows have identical formula constructs with only the referenced
> sheet name changed and all work perfectly.
>
> As is F4 displays "#VALUE".  I have verified that the formula is exactly
as
> it should be and aside from the reference to that particular worksheet is
> identical to the formula in column F in the other rows.
>
> To further confuse me I did a little experimenting and discovered that the
> formula works in some rows but not in others.  I copied the formula and
> pasted it into several other rows.  Starting with row 6 this is how the
> sheet now appears:
>
> 56.16666667
> #VALUE!
> 56.16666667
> 56.16666667
> #VALUE!
> #VALUE!
> 56.16666667
> 56.16666667
> 56.16666667
> 56.16666667
> 56.16666667
> #VALUE!
>
> To be very clear, in F6 the formula
>
> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> results in 56.16666667
>
> In F7 the formula
> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> results in #VALUE!
>
> Can anybody shed some light on the situation?  Why does the result of a
> formula depend on which cell it is in?
>
>


0
demechanik (4694)
5/23/2005 10:49:09 PM
"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message 
news:42925D78.DB70D4D0@netscapeXSPAM.com...
> The formula in F4:
> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> is an array formula (you used ctrl-shift-enter instead of just entering it 
> into
> the cell)

Don't array formulas always appear with {}s around them?

> But array formulas don't work on whole columns:
> =AVERAGE(IF(Northeast!B1:B65535<>0, Northeast!B1:B65535,""))
>
> Should work ok.  (But I'd use a range that was big enough--but not too 
> big.)

I tried with B1:B1000 as the range instead of B:B and I get the same 
problem: and the same issue with the formula working in some cells but not 
in others.  Even cells that never had any formula in them at all.

Interestingly enough I've found another weird aspect to this problem.

I cut/pasted row 4 to row 6 and the formula works fine (as it always has). 
Thinking that there may be some weird corruption or strange formatting 
glitch I deleted row 4 from the database.  Row 5 (which was now row 4) 
stopped working and generated the same error.

Everything works fine as long as I leave row 4 empty.  What could possibly 
be causing this?

>
> Wowbagger wrote:
>>
>> I have a multi-sheet 2003 workbook.
>>
>> Sheet 1 is a summary that displays data from the other 4 sheets, the name 
>> of
>> one of which is "Northeast" (though experimentation proves that it 
>> doesn't
>> matter what the sheet is named)
>>
>> On my summary sheet this particular sheet is referenced in row 4.  Column 
>> A
>> is nothing but the text "Northeast", the formulas in each of the next
>> columns are as follows (without the B: C: D: E: and F:)
>>
>> B:  =SUMIF(Northeast!C:C,">0",Northeast!B:B)
>> C:  =SUM(Northeast!C:C)
>> D:  =SUM(Northeast!D:D)
>> E:  =1-B4/ABS(C4)
>> F:  =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
>>
>> The other rows have identical formula constructs with only the referenced
>> sheet name changed and all work perfectly.
>>
>> As is F4 displays "#VALUE".  I have verified that the formula is exactly 
>> as
>> it should be and aside from the reference to that particular worksheet is
>> identical to the formula in column F in the other rows.
>>
>> To further confuse me I did a little experimenting and discovered that 
>> the
>> formula works in some rows but not in others.  I copied the formula and
>> pasted it into several other rows.  Starting with row 6 this is how the
>> sheet now appears:
>>
>> 56.16666667
>> #VALUE!
>> 56.16666667
>> 56.16666667
>> #VALUE!
>> #VALUE!
>> 56.16666667
>> 56.16666667
>> 56.16666667
>> 56.16666667
>> 56.16666667
>> #VALUE!
>>
>> To be very clear, in F6 the formula
>>
>> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
>> results in 56.16666667
>>
>> In F7 the formula
>> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
>> results in #VALUE!
>>
>> Can anybody shed some light on the situation?  Why does the result of a
>> formula depend on which cell it is in?
>
> -- 
>
> Dave Peterson 


0
Wowbagger
5/24/2005 2:10:08 PM
Array formulas will have the curly brackets around them if you enter them
correctly.

Do you have any merged cells in that range?

Did you adjust the range in each spot?

Do you have any errors in that range (B1:B1000)?

If you open a new workbook and try it there, does it work ok?

Wowbagger wrote:
> 
> "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> news:42925D78.DB70D4D0@netscapeXSPAM.com...
> > The formula in F4:
> > =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> > is an array formula (you used ctrl-shift-enter instead of just entering it
> > into
> > the cell)
> 
> Don't array formulas always appear with {}s around them?
> 
> > But array formulas don't work on whole columns:
> > =AVERAGE(IF(Northeast!B1:B65535<>0, Northeast!B1:B65535,""))
> >
> > Should work ok.  (But I'd use a range that was big enough--but not too
> > big.)
> 
> I tried with B1:B1000 as the range instead of B:B and I get the same
> problem: and the same issue with the formula working in some cells but not
> in others.  Even cells that never had any formula in them at all.
> 
> Interestingly enough I've found another weird aspect to this problem.
> 
> I cut/pasted row 4 to row 6 and the formula works fine (as it always has).
> Thinking that there may be some weird corruption or strange formatting
> glitch I deleted row 4 from the database.  Row 5 (which was now row 4)
> stopped working and generated the same error.
> 
> Everything works fine as long as I leave row 4 empty.  What could possibly
> be causing this?
> 
> >
> > Wowbagger wrote:
> >>
> >> I have a multi-sheet 2003 workbook.
> >>
> >> Sheet 1 is a summary that displays data from the other 4 sheets, the name
> >> of
> >> one of which is "Northeast" (though experimentation proves that it
> >> doesn't
> >> matter what the sheet is named)
> >>
> >> On my summary sheet this particular sheet is referenced in row 4.  Column
> >> A
> >> is nothing but the text "Northeast", the formulas in each of the next
> >> columns are as follows (without the B: C: D: E: and F:)
> >>
> >> B:  =SUMIF(Northeast!C:C,">0",Northeast!B:B)
> >> C:  =SUM(Northeast!C:C)
> >> D:  =SUM(Northeast!D:D)
> >> E:  =1-B4/ABS(C4)
> >> F:  =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> >>
> >> The other rows have identical formula constructs with only the referenced
> >> sheet name changed and all work perfectly.
> >>
> >> As is F4 displays "#VALUE".  I have verified that the formula is exactly
> >> as
> >> it should be and aside from the reference to that particular worksheet is
> >> identical to the formula in column F in the other rows.
> >>
> >> To further confuse me I did a little experimenting and discovered that
> >> the
> >> formula works in some rows but not in others.  I copied the formula and
> >> pasted it into several other rows.  Starting with row 6 this is how the
> >> sheet now appears:
> >>
> >> 56.16666667
> >> #VALUE!
> >> 56.16666667
> >> 56.16666667
> >> #VALUE!
> >> #VALUE!
> >> 56.16666667
> >> 56.16666667
> >> 56.16666667
> >> 56.16666667
> >> 56.16666667
> >> #VALUE!
> >>
> >> To be very clear, in F6 the formula
> >>
> >> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> >> results in 56.16666667
> >>
> >> In F7 the formula
> >> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> >> results in #VALUE!
> >>
> >> Can anybody shed some light on the situation?  Why does the result of a
> >> formula depend on which cell it is in?
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
ec357201 (5290)
5/25/2005 12:09:56 AM
"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message 
news:4293C254.E2BF1083@netscapeXSPAM.com...

> Do you have any merged cells in that range?

No.

> Did you adjust the range in each spot?

I'm not sure what you mean.

> Do you have any errors in that range (B1:B1000)?

No.

> If you open a new workbook and try it there, does it work ok?

No: I get the same error, always in row 4.


>
> Wowbagger wrote:
>>
>> "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
>> news:42925D78.DB70D4D0@netscapeXSPAM.com...
>> > The formula in F4:
>> > =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
>> > is an array formula (you used ctrl-shift-enter instead of just entering 
>> > it
>> > into
>> > the cell)
>>
>> Don't array formulas always appear with {}s around them?
>>
>> > But array formulas don't work on whole columns:
>> > =AVERAGE(IF(Northeast!B1:B65535<>0, Northeast!B1:B65535,""))
>> >
>> > Should work ok.  (But I'd use a range that was big enough--but not too
>> > big.)
>>
>> I tried with B1:B1000 as the range instead of B:B and I get the same
>> problem: and the same issue with the formula working in some cells but 
>> not
>> in others.  Even cells that never had any formula in them at all.
>>
>> Interestingly enough I've found another weird aspect to this problem.
>>
>> I cut/pasted row 4 to row 6 and the formula works fine (as it always 
>> has).
>> Thinking that there may be some weird corruption or strange formatting
>> glitch I deleted row 4 from the database.  Row 5 (which was now row 4)
>> stopped working and generated the same error.
>>
>> Everything works fine as long as I leave row 4 empty.  What could 
>> possibly
>> be causing this?
>>
>> >
>> > Wowbagger wrote:
>> >>
>> >> I have a multi-sheet 2003 workbook.
>> >>
>> >> Sheet 1 is a summary that displays data from the other 4 sheets, the 
>> >> name
>> >> of
>> >> one of which is "Northeast" (though experimentation proves that it
>> >> doesn't
>> >> matter what the sheet is named)
>> >>
>> >> On my summary sheet this particular sheet is referenced in row 4. 
>> >> Column
>> >> A
>> >> is nothing but the text "Northeast", the formulas in each of the next
>> >> columns are as follows (without the B: C: D: E: and F:)
>> >>
>> >> B:  =SUMIF(Northeast!C:C,">0",Northeast!B:B)
>> >> C:  =SUM(Northeast!C:C)
>> >> D:  =SUM(Northeast!D:D)
>> >> E:  =1-B4/ABS(C4)
>> >> F:  =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
>> >>
>> >> The other rows have identical formula constructs with only the 
>> >> referenced
>> >> sheet name changed and all work perfectly.
>> >>
>> >> As is F4 displays "#VALUE".  I have verified that the formula is 
>> >> exactly
>> >> as
>> >> it should be and aside from the reference to that particular worksheet 
>> >> is
>> >> identical to the formula in column F in the other rows.
>> >>
>> >> To further confuse me I did a little experimenting and discovered that
>> >> the
>> >> formula works in some rows but not in others.  I copied the formula 
>> >> and
>> >> pasted it into several other rows.  Starting with row 6 this is how 
>> >> the
>> >> sheet now appears:
>> >>
>> >> 56.16666667
>> >> #VALUE!
>> >> 56.16666667
>> >> 56.16666667
>> >> #VALUE!
>> >> #VALUE!
>> >> 56.16666667
>> >> 56.16666667
>> >> 56.16666667
>> >> 56.16666667
>> >> 56.16666667
>> >> #VALUE!
>> >>
>> >> To be very clear, in F6 the formula
>> >>
>> >> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
>> >> results in 56.16666667
>> >>
>> >> In F7 the formula
>> >> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
>> >> results in #VALUE!
>> >>
>> >> Can anybody shed some light on the situation?  Why does the result of 
>> >> a
>> >> formula depend on which cell it is in?
>> >
>> > --
>> >
>> > Dave Peterson
>
> -- 
>
> Dave Peterson 


0
Wowbagger
5/25/2005 3:25:25 PM
> > Did you adjust the range in each spot?
> I'm not sure what you mean.

Just a shot at re-tracing the thread relevant to the above exchange .. I'm
not sure if the phenomena you're facing, assuming the formula's now
correctly array-entered, was because the cell references were not fixed,
i.e. you copied (to other cells):

=AVERAGE(IF(Northeast!B1:B1000<>0, Northeast!B1:B1000,""))

instead of:

=AVERAGE(IF(Northeast!$B$1:$B$1000<>0, Northeast!$B$1:$B$1000,""))

Some strange things/results could happen <g> if the cell references are not
fixed where they should be.

Do hang around for Dave's comments, though ..
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----


0
demechanik (4694)
5/26/2005 1:00:11 AM
My question was more of a typo test:

=AVERAGE(IF(Northeast!B1:B1000<>0,Northeast!B1:B1000,""))

(just checking if B1:B1000 was used in both portions of the formula:


Max wrote:
> 
> > > Did you adjust the range in each spot?
> > I'm not sure what you mean.
> 
> Just a shot at re-tracing the thread relevant to the above exchange .. I'm
> not sure if the phenomena you're facing, assuming the formula's now
> correctly array-entered, was because the cell references were not fixed,
> i.e. you copied (to other cells):
> 
> =AVERAGE(IF(Northeast!B1:B1000<>0, Northeast!B1:B1000,""))
> 
> instead of:
> 
> =AVERAGE(IF(Northeast!$B$1:$B$1000<>0, Northeast!$B$1:$B$1000,""))
> 
> Some strange things/results could happen <g> if the cell references are not
> fixed where they should be.
> 
> Do hang around for Dave's comments, though ..
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8,  1� 22' N  103� 45' E
> xdemechanik <at>yahoo<dot>com
> ----

-- 

Dave Peterson
0
ec357201 (5290)
5/26/2005 5:17:55 AM
"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message 
news:42955C03.16706652@netscapeXSPAM.com...
> My question was more of a typo test:
>
> =AVERAGE(IF(Northeast!B1:B1000<>0,Northeast!B1:B1000,""))
>
> (just checking if B1:B1000 was used in both portions of the formula:

Yes.  As were B:B and B:B


0
Wowbagger
5/26/2005 2:23:38 PM
I don't have any more guesses.

Wowbagger wrote:
> 
> "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> news:42955C03.16706652@netscapeXSPAM.com...
> > My question was more of a typo test:
> >
> > =AVERAGE(IF(Northeast!B1:B1000<>0,Northeast!B1:B1000,""))
> >
> > (just checking if B1:B1000 was used in both portions of the formula:
> 
> Yes.  As were B:B and B:B

-- 

Dave Peterson
0
ec357201 (5290)
5/26/2005 8:36:26 PM
Maybe you'd like to upload a small zip copy of your file via:
http://flypicture.com/  (see caveat below**)
and then post the *link* to it here

Might be easier for those interested in your problem
to see what the deuce is going on over there

(Note: Do NOT post any attachments !)

** I'm not promoting the site, but it is a free, easy-to-use,
no subscribe service where one can upload files for a 30 day stretch ..
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----


0
demechanik (4694)
5/27/2005 3:04:35 AM
http://flypicture.com/p.cfm?id=53934

"Max" <demechanik@yahoo.com> wrote in message 
news:uz9AWkmYFHA.612@TK2MSFTNGP12.phx.gbl...
> Maybe you'd like to upload a small zip copy of your file via:
> http://flypicture.com/  (see caveat below**)
> and then post the *link* to it here
>
> Might be easier for those interested in your problem
> to see what the deuce is going on over there
>
> (Note: Do NOT post any attachments !)
>
> ** I'm not promoting the site, but it is a free, easy-to-use,
> no subscribe service where one can upload files for a 30 day stretch ..
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8,  1� 22' N  103� 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
> 


0
Wowbagger
5/27/2005 2:12:42 PM
Here's the corrected sample file with
implemented formulas in "Summary-Revised":
http://flypicture.com/p.cfm?id=54060

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: weird-file_wowbagger_newusers.xls

------
Think everything looks fine now ! Since A2:A5 holds the 4 sheetnames, think
we can use INDIRECT to point to A2:A5 in the formulas for cols B, C and F.
This allows us to then simply copy down the formulas from the ones placed in
the starting row.

Put in B2:
=SUMIF(INDIRECT("'"&$A2&"'!C:C"),">0",INDIRECT("'"&$A2&"'!B:B"))

Put in C2:
=SUM(INDIRECT("'"&$A2&"'!C:C"))

Select B2:C2, copy down to C5

Put in F2, array-enter (press CTRL+SHIFT+ENTER):
=AVERAGE(IF(INDIRECT("'"&A2&"'!B2:B100")<>0,INDIRECT("'"&A2&"'!B2:B100"),"")
)

Copy F2 down to F5

Note that the range used in F2 is corrected to "B2:B100", not "B1:B100"
since data starts from the 2nd row.
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----


0
demechanik (4694)
5/27/2005 4:15:04 PM
Here's a new link to the sample file:
http://www.savefile.com/files/1042706
File: weird-file_wowbagger_newusers.xls
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----


0
demechanik (4694)
6/30/2005 2:21:47 PM
Reply:

Similar Artilces:

Using Time in formulas #2
Frank: I don't want the user to enter them by hand. I want to use a formul to calculate the subsequent times. They would have to load 9 different lines. Staci -- SPenne ----------------------------------------------------------------------- SPenney's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=107 View this thread: http://www.excelforum.com/showthread.php?threadid=26920 ...

Outlook 2003 not working in WIN 7 OS
Unable to collect emails in Outlook 2003 on WIN 7 OS. Error message requests microsoft Agent 2.0 from Office Disk. Tried HotFix from Win 7 Website, but it didn't work. Need detailed instructions on how to fix this problem. Thanks. ...

How to add more field on My Work
Dear all, In PWA, as a Project Manager, when I go to Server Setting, then Manage Views and select "My Work" in order to manage the view of "My Tasks" for any users, It seems some fields are not available like the "Baseline Finish" or "HREF". However, they do appear on "Project" for GANT purpose. As I need to customize My Task view, how is it possible to add this fields? Thanks for your reply, P.S I use 2007 SP1, cannot migrate to SP2 cause of many errors with our architecture IT. 1) Check if you can create a new Enterprise D...

Help needed on date matching and cell reference.
Hi, I need a formula to return the value of a cell from a nearby column, based on a matching of the months and years within a range of dates to the months and years within a given date. Here is the setup: Cell BA58 contains the given date. Column AP, starting in Cell AP59 and going down to AP2000 contains the array of dates that need to be evaluated to find which date matches the given date in Cell BA58 Column AR, starting in Cell AR59 and going down to AR2000 contains the array of numbers from which the result must be displayed. If a match is found between the given date in...

In cell drop down button disappeared
I have a column with a data validation list. My drop down arrow doesn't show up anymore when I select the cell. It has been working for a year with no problem. Where did it go? I looked in Tools-Options to see if anything was unchecked, but all looks good. Any ideas? Joe Excel 2003 If you select the cell and do data>validation is allow list and in-cell dropdown still there (and checked)? -- Regards, Peo Sjoblom "lunker55" <this_is_not_my_email_address@hotmail.com> wrote in message news:e5LtKqoGFHA.2280@TK2MSFTNGP15.phx.gbl... > I have a column with a data va...

Referencing a referenced cell
Sheet1!C4 contains the formula =Sheet2!A1 I want Sheet1!D4 to find out what cell Sheet1!C4 is referencing an then return the value two columns over. (Offset will take care o returning the value two columns over). My problem is I need to kno which cell Sheet1!C$ is referencing. If I use the formul "=OFFSET(Sheet1!C4,0,1)" I get the value from one column to the righ of Sheet1!C4, I need the value of one column to the right of the cel being referenced by Sheet1!C4, (Sheet2!A1) How would I do this. -- Message posted from http://www.ExcelForum.com Hi see your post in Excel.mis...

formula #12
Hi, I have about 3000 rows filled with 114 unique items. Out of these unique items I want few of them only. How can I delete the rest without manually deleting each one of them. Through Custom Autofilter I can use only two conditions. Any suggessions.. Thanks in advance. Best Regards, Kirandeep Singh I don't know your data structure, but you might consider using a Helper Column and putting some combination of a IF/OR/AND/CONCATENATION formula to consolidate several cells at once for filtering them as a group. hth Vaya con Dios, Chuck, CABGx3 "Kirandeep Singh" <Kira...

Using Tab key to jump to specific cell
I've created a 'fillable form' and somehow (magically) when the tab key is pressed in certain cells, the cursor jumps to the next appropriate cell to be filled (Example I filled in cell B1 and the next fillable cell is D4. If I hit the tab key after typing in B1, the cursor will jump to D4). But this does not happen in all cells or at the appropriate places. I don't know how I managed to get it to do what it does already. Is there is a way to fine-tune it? When you protect a sheet, tabbing will cause the active cell to jump between the unlocked (aka unprotect...

Copy from many cells and paste to one
I need to copy a range of cells (C7:C20) and past the contents into one cell (B4). the concatenate function is not the solution because I need the results to appear as separate lines in the cell (B4) when pasted. (leaving a "return" between lines) Weird I know but Ahem, "It's for a friend". ;) Thanks, Robert -- eoreality ------------------------------------------------------------------------ eoreality's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24549 View this thread: http://www.excelforum.com/showthread.php?threadid=381448 ...

Merge two cells together without losing value of one cell
Is there any way to merge two cells together without getting one value deleted? As an example, I'd like to add ";" after a number, but when I do a merge the ";" would get deleted. Thanks. I have 300 cells with numbers to add ";" to. "Sharon" wrote: > Is there any way to merge two cells together without getting one value > deleted? As an example, I'd like to add ";" after a number, but when I do a > merge the ";" would get deleted. Thanks. On Aug 7, 10:00=A0am, Sharon <Sha...@discussions.microsoft.com&g...

Formulas not calculating???
My excel is no longer calculating formulas, when referencing other cells. How can I correct this? Hi maybe: Tools - Options - Calculate and enable automatic calculation -- Regards Frank Kabel Frankfurt, Germany "J Dizzle Fizzle" <J Dizzle Fizzle@discussions.microsoft.com> schrieb im Newsbeitrag news:9DD3E243-E887-458D-B6A4-C389652C2B27@microsoft.com... > My excel is no longer calculating formulas, when referencing other cells. > How can I correct this? ...

Copy formulas in Column
Hi, I have to copy numbers from cell a1, b1,c1...........z1 to a20,a21,a22,a23... so. for e.g-- a1 = 21 b1 = 23 c1 = 24 etc z1 = 40 i need to copy as follows:- a20 = a1 a21 = b1 a22 = c1 Is there an easy way to copy the numbers instead of typing one by one .... I have many records to copy..... Thanks for your kind help :) Meeru --Select the range A1:Z1 and copy --Select cell A20. Right click>PasteSpecial>check 'Transpose' and click OK -- Jacob "Meeru" wrote: > Hi, > > I have to copy numbers from cell a1, b1,c1...........

How do I delete cells in Excel without changing the data in others
I want to get rid of some of the rows in my spreadsheet but the data in the ones I am leaving behind are linked by formulas? As soon as I delete them all the data goes from the the others. Gemma, I may not understand your situation. An example of what I think you're saying is: Cell A2: A1+1. You want to delete cell A1, but leave A2 with the value it currently has. If this is the case, you need to copy A2 and the, using paste special, paste the value back into A2. This way A2 no longer has any formula at all and will remain unchanged when you delete A1. Art "G...

Linking a repeating pattern of non-adjacent cells
I need to transpose an Excel worksheet where every fourth cell is selected and placed into a single column of another workbook. Can I write a formula to do this so I don't have to manually copy each cell I need? If your original data were in col. A, you could put this in A1 of a new wb: =OFFSET([Book8]Sheet1!$A$1,ROW()*4-4,) and drag down. It'll pull in the values in row 1, row 5, row 9, and so on. HTH Jason Atlanta, GA >-----Original Message----- >I need to transpose an Excel worksheet where every fourth cell is selected >and placed into a single column of anoth...

Excel cell format #2
how can i display preceding zeros in excel without formatting as text? In article <5CBDC357-B0B2-49C2-906C-73E94C6172B9@microsoft.com>, "rockfam8" <rockfam8@discussions.microsoft.com> wrote: > how can i display preceding zeros in excel without formatting as text? Precede your entry with an apostrophe. For example... '012345 Hope this helps! Or give it a custom format like 00000 (as many 0's as you need) rockfam8 wrote: > > how can i display preceding zeros in excel without formatting as text? -- Dave Peterson ...

Formula for a fill color help
I can't figure out how to make a formula that will recognize a color a a value. More specifically; If b1 is made to be green (color index 4) would like c1 to insert the letter Y. I am I'm looking for an I statement so I can use it throughout the whole workbook. The formula if I understand it should kind of look like (in C1): =If(b1=colorindex4,"Y","") I'm looking to put a "Y" in c1 if b1 is colored in green. I would really like to also know how to insert a fill color in an formula! Thank you for any of your help and time, Brya -- Bryan J Yo...

vlookup function to return the cell address of the found item
I have a one column list of data (around 3,000 items) - and I am using the vlookup function to determine if an item is in that list using something like the formula below: =if(iserror(vlookup(A1,D1:D3000,1,false)),"not in list","in list") I would like to know if I can have this function return the cell address or row number to indicate the location of the item in the list - is this possible? Thank you for your time and assistance You can return the (relative) row number using MATCH, like this: =if(iserror(vlookup(A1,D1:D3000,1,false)),"not in list",MATCH(A1,...

Insert file name into Cell
Is there a way to insert the file name into a cell, rather than on header/footer? Hi Bonny, 1996FEDT.XLS =MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1) D:\driveM\excel\TAXES\1996FEDT.XLS [Sheet1] =SUBSTITUTE(SUBSTITUTE(CELL("filename",A1),"[",""),"]"," [") & "]" for more information, worksheet examples, and coding examples for pathname, filename, sheetname and combinations of ...

Formula for computing work time in Excel
Hi, For instance, A1 = 8:20, B1 = 16:30. I need to compute in C1 the following: B1 - A1 - 0.5 hour. What is the formula? If I use formula B1 - A1 - 0:30 I get invalid value. Thanks, -- Alex Vinokur email: alex DOT vinokur AT gmail DOT com http://mathforum.org/library/view/10978.html http://sourceforge.net/users/alexvn =B1-A1-TIME(0,30,0) -- HTH Bob Phillips "Alex Vinokur" <alexvn@big-foot.com> wrote in message news:uGyW$ZgvFHA.2008@TK2MSFTNGP10.phx.gbl... > Hi, > > For instance, A1 = 8:20, B1 = 16:30. > I need to compute in C1 the fo...

paste value vs type value (formula not working)
Hi. I have a formula that works just fine, as long as i paste the values from the original list and not type the numbers in. There are no hidden spaces (that I know of), but when I type the same value that I have in my orignial list, the formula gives me an error (#N/A). When I copy and paste values (from the original list) it works just fine. Any clues? (an array formula) =INDEX(TSCA_REQ,MATCH(1,(B202>=TSCA_MIN)*(B202<=TSCA_MAX),0)) HI Jeremy If its an ARRAY Formula, you must press Control-Shift-Enter, Try it and see Good Luck Cimjet "J.W. Aldridge" <jeremy.w.aldrid...

Date when I last change cell in a row?
Please, Help. In my database, every row represents a separate client. I wonder if it is possible to input the date when I last changed any value in a particular row. This way I will know when exactly I last updated the information for client in row 4, 5 etc. I don't need the date when the document was last saved. Please, let me know if it can be done in Excel. Thank you, Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Row = 5 Then Me.Range("H1").Value = Date ...

Displaying cell references next to embedded cells in Word 2000
Hello Group. I've embedded some excel cells into my report written in Word 2000. Is there a way I can display the cell references next to the embedded object in the printout. For example if cells B10:C15 are the embedded cells, I want those who read the printed document to see exactly which rows and which columns the numbers belong to. Something like this: B C 10 Jan 10% 11 Feb 15% 12 Mar 34% 13 Apr 14% 14 May 10% 15 Jun 12% So when I say somthing like "The formula used here is C10/SUM(C10:C15)" my readers would be able to refer to the ...

CRM task button not working???
I can't even open the form in the Personal Forms Library. Is there any way for me to reinstall this form? ...

Average Row Formula
Is there a way to calculate the average of a row if some cells are empty but when calculating the average they should have the value of the cell to the left? If the following data is enterered A B C D E F G 1 3 5 6 For the calculation the cells should use the data A B C D E F G 1 3 3 3 5 5 6 Also what would the average formula be if you wanted to skip one of the cells from the average calculation? Thanks Tom pls do NOT multipost. It wastes resources -- Don Guillett SalesAid Software donaldb@281.com "Tom" <tsanders123@hotmail.com> wrote in message news:111546...

Auto forward doesn't work for email sent to distribution list
Hi there, When I sent an email to a distribution list it's not auto forwarded for people (members of this distribution list) who have there Out of Office Assistant configured with "forward all messages to email@address.com". For all other messages the auto forward works fine. Anyone any idea to solve this problem??? Many thanks in advance, Lex. Define "auto forward". -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "Lex Haak" <LexHaak@discussions.microsoft.com> wrote in message news:08428C3D-3227-4705-8...