>Hi all! I really hope that someone can be of assistance to me - because I
>am at a loss. I have a spreadsheet with various formulas, which vary only
>based on the row in which they are contained. For some reason, one formula
>is returning the #VALUE! error. We have attempted reentering all data
>which the formula draws from, rewriting the formula, reformatting the cells
>to ensure none are formatted as text. Nothing is working. All other
>formulas on this sheet work fine. Any help would be greatly appreciated!
Very difficult to help you if you do not share the formula with us.
Bill Manville
Oxford, England
Microsoft Excel - MVP
Before we look at the actual workbook, can you give us the formula which is
giving the trouble, as it seems you have covered the obvious
HTH
--
Nick Hodge
Oxford, England
Nick_...@CompuServe.com
Donna Newcomb <d...@amherst.com> wrote in article
<01bcbf7d$4d8f85c0$7201...@dan-95.amherst.com>...
> Hi all! I really hope that someone can be of assistance to me - because
I
> am at a loss. I have a spreadsheet with various formulas, which vary
only
> based on the row in which they are contained. For some reason, one
formula
> is returning the #VALUE! error. We have attempted reentering all data
> which the formula draws from, rewriting the formula, reformatting the
cells
> to ensure none are formatted as text. Nothing is working. All other
> formulas on this sheet work fine. Any help would be greatly appreciated!
First the obvious, which you appear to have covered
Help --> topics --> index --> VALUE! error
Since Format --> Cells --> ...
only applies to new material added, it wouldn't tell you much. What you
want to see is how Excel sees what you have. In order to to this the
CELLS worksheet function can be used. Refer to the help, you will need it
interpret the format codes.
Help --> topics --> index --> CELLS worksheet function
I believe CELLS is a add-in. So under Tools --> Add-ins..
Analysis Toolpak should be checked.
Also since my example uses INDIRECT so that you can show the cellname you
want to examine separately.
refer to Help --> topics --> index --> INDIRECT worksheet ... --
I find the following macro installed in a MODULE sheet also very useful.
It is also used in my example:
Function ShowFormula(cell)
ShowFormula = cell.Formula
End Function
My example can be copied then pasted to new worksheet. It is a CSV (comma
separated variable) file and is expanded in Excel using Data --> Text to
columns and comma as the separator
,a,=B1/3,1:01:01,=3/0
,,,0,
,b1,c1,d1,e1,x
address,"=CELL($A4,INDIRECT(B3))","=CELL($A4,INDIRECT(C3))","=CELL($A4,IND
IRECT(D3))","=CELL($A4,INDIRECT(E3))",x
contents,"=CELL($A5,INDIRECT(B3))","=CELL($A5,INDIRECT(C3))","=CELL($A5,IN
DIRECT(D3))","=CELL($A5,INDIRECT(E3))",x
format,"=CELL($A6,INDIRECT(B3))","=CELL($A6,INDIRECT(C3))","=CELL($A6,INDI
RECT(D3))","=CELL($A6,INDIRECT(E3))",x
prefix,"=CELL($A7,INDIRECT(B3))","=CELL($A7,INDIRECT(C3))","=CELL($A7,INDI
RECT(D3))","=CELL($A7,INDIRECT(E3))",x
type,"=CELL($A8,INDIRECT(B3))","=CELL($A8,INDIRECT(C3))","=CELL($A8,INDIRE
CT(D3))","=CELL($A8,INDIRECT(E3))",x
FORMULA,=ShowFormula(INDIRECT(B3)),=ShowFormula(INDIRECT(C3)),=ShowFormula
(INDIRECT(D3)),=ShowFormula(INDIRECT(E3)),x
INDIRECT is used so that you specifiy and see what cell you are examining.
In this example, since cell B3 contains "b1" the following are equivalent:
=CELL("format",b1)
=CELL("format",indirect(b3))
Note in the example #VALUE! error is caused by attempting to divide a
literal by 3.
Hope this helps,
David McRitchie
DMcRitchie
Well, we're not going to be able to help you too much without
an example of your problem. There are ALL SORTS of ways to
create this error! What's doing it in your case, and where?
Feel free to email a copy of the sheet direct, if you don't want
to post it.