Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

the cell currently being evaluated contains a constant

Excel generates the message:
"the cell currently being evaluated contains a constant"
and then does not evaluate a formula. The same formula, character for
character, works fine in a different workbook. I can find no differences
between their properties.
1) Does anyone know what causes this?
2) Does anyone know why there is no reference to this message in any
Microscoft help or assistance, although the message is generated by Excel?
Thanks,
Barrie


Post your answer or comment

comments powered by Disqus
Excel generates the message:
"the cell currently being evaluated contains a constant"
and then does not evaluate a formula. The same formula, character for
character, works fine in a different workbook. I can find no differences
between their properties.
1) Does anyone know what causes this?
2) Does anyone know why there is no reference to this message in any
Microscoft help or assistance, although the message is generated by Excel?
Thanks,
Barrie

Hello,

I know enough about excel to be dangerous. The issue I have is I have created a worksheet that references another workbook and worksheet. It has been working fine until now. I had to insert a new row and copy the formula from the cell above down to the cell in the new row. This works fine until I need to change the referenced cell i.e. "sheet1!A1" to "sheet1!A4". I hit F2 to edit the cell number, I change A1 to A4 hit enter and instead of it referencing the information from the "sheet1!A4". The formula shows up. So I went to Tools then Formula Auditing then Evaluate Formula it tells me that "The Cell Currently Being Evaluated Contains a Constant"

Like I said I know enough about Excel to screw things up. I would appreciate your help


	VB:
	
 Range("c2:c" & iLastRow0) 
    Rng0.Select 
    ActiveCell = "=IF(RC[1]=""(-)"",""(-)"","""")" 
Next Rng0 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
inserts correctly but the result is a text field and if i try to evaluate it. it states " the cell currently being evaluated contains a constant."

The folllowing formula works well for other columns in the worksheet but fails on one column.

=OFFSET(Automatrix!$B$1,MATCH(C3,TEST,0),MATCH(H1,Automatrix!B1:AA1,0))

The message that i'm getting from 'Evaluate formula' tool is:

The cell currently being evaluated contains a constant.

http://img91.imageshack.us/my.php?im...formulals2.jpg

Can somebody pls tell me what this means ?
I'm quite perplexed as to why it works for other cols. but fails on another !

DC

Hi, I am using MOSS and Office 2007. I am collecting data via the SharePoint
list and then exporting the list to Excell on my desktop to manipulate and
aggregate the data. I have removed the table and broken the link with the
SharePoint connection, unlocked the cells. When I attempt to add formulas to
the excel spreadsheet, the formulas will work - when I evaluate the formula I
get "The cell currently being evaluated contains a constant". I have found
the cells that contain constants but need to remove the constants and can't
figure out how to do that. Can you help? This is very urgent for my business.

Sometimes Excel just stops processing certain formulas on a worksheet and
considers the as plain text (regardless of the fact that the cell contents
starts with an equation sign "="). For example formula "=1+2" doesn't show
anymore as "3" but as "=1+2", as if it's only a string of text .But if I type
the same formula in the cell next to it, it shows the correct result "3".

Also, when I try to evaluate the formula, excel tells me that "the cell
currently being evaluated contains a constant" -- so its not about wether my
formulas are "visible" (tools-options-view-formulas) or not. The number of my
formulas asre always under the maximum 1024 characters.

Hello,

I'm using the following formula to return multiple results from a set of data:

where A1:B13 on Sheet 2 is the data set
and A1 on the current sheet (sheet1) is the value to match

When filled down this produces all the values in the dataset that are greater than A1 (on sheet one)

This works a treat but when I try and use it across a range of sheets:

I get a #VALUE! error

When I evaluate I get:
"The cell currently being evaluated contains a constant" - referring to A1

Any ideas?

Cheers

Sometimes Excel just stops processing certain formulas on a worksheet and
considers the as plain text (regardless of the fact that the cell contents
starts with an equation sign "="). For example formula "=1+2" doesn't show
anymore as "3" but as "=1+2", as if it's only a string of text .But if I type
the same formula in the cell next to it, it shows the correct result "3".

Also, when I try to evaluate the formula, excel tells me that "the cell
currently being evaluated contains a constant" -- so its not about wether my
formulas are "visible" (tools-options-view-formulas) or not. The number of my
formulas asre always under the maximum 1024 characters.

On an Open sheet where the remaining sheets are named sheet2 and sheet3. I entered in d2, a general-formatted cell: =VLOOKUP(c2,’*’!a:b,2,0). The formula was accepted and excel converted the formula to =VLOOKUP(C2,Sheet2:Sheet3!A:B,2,0) and returned an error code of #VALUE. (Col c on this sheet is formatted for text.) If I “step in” when the evaluation underlines c2, I get the message: The cell currently being evaluated contains a constant. This was addressed at: http://www.excelforum.com/excel-work...-constant.html but didn’t solve my problem. I’ve attached a file representing the problem. I’ve struggled with this over a day, visiting many forums where the question seems to be answered, to no avail. Can anyone tell me what I’m doing wrong? Thanks. Boyd Carter

I am following a specific set of instructions for school. I have checked my formulas again and again using the evaluate formula button as well. is what keeps coming up. I have formatted the cells to be general and then put the cursor in the formula bar and pressed enter. I have also made sure the calculation is on automatic.

I have a medium-sized excel 2003 project with several worksheets, some of
them containing data that is imported from several access 2003 querys. In
the beginning everything worked fine, but then I tried to change a cell
reference (from $B1 to $B2) and the worksheet stops automatically calculating
the formula. For example, the cell would read =$B2+$B3, instead of a
numerical value. If I use the "Evaluate Formulae" button on the formula
auditing toolbar it says that "the cell being evaluated contains a constant".
If I go to a new cell and type a simple formula, like =1+1, it has the exact
same problem- the cell reads =1+1, instead of 2. If I highlight the formula
and press F9 if solves correctly, but the formula is replaced by the
solution. If I take a broken cell and fill down with it, relative references
do not change. Existing cells in the same worksheet continue to display
correctly, but if I change them at all they too will become broken, and will
not go back to reading correctly even if I change it back to what it was
before. Cells in other worksheets operate without any problems. Any help on
this problem will be greatly appreciated.

I have a medium-sized excel 2003 project with several worksheets, some of
them containing data that is imported from several access 2003 querys. In
the beginning everything worked fine, but then I tried to change a cell
reference (from $B1 to $B2) and the worksheet stops automatically calculating
the formula. For example, the cell would read =$B2+$B3, instead of a
numerical value. If I use the "Evaluate Formulae" button on the formula
auditing toolbar it says that "the cell being evaluated contains a constant".
If I go to a new cell and type a simple formula, like =1+1, it has the exact
same problem- the cell reads =1+1, instead of 2. If I highlight the formula
and press F9 if solves correctly, but the formula is replaced by the
solution. If I take a broken cell and fill down with it, relative references
do not change. Existing cells in the same worksheet continue to display
correctly, but if I change them at all they too will become broken, and will
not go back to reading correctly even if I change it back to what it was
before. Cells in other worksheets operate without any problems. Any help on
this problem will be greatly appreciated.

I have used the following code to format cells C76 to F76:-


	VB:
	
 Range) 
    Dim icolor As Integer 
     
    If Not Intersect(Target, Range("C76:F76")) Is Nothing Then 
        Select Case Target 
        Case 71 To 152 
            icolor = 4 
        Case 0 To 70 
            icolor = 43 
        Case -70 To -1 
            icolor = 46 
        Case -152 To -71 
            icolor = 3 
             
        Case Else 
             'Whatever
        End Select 
         
        Target.Interior.ColorIndex = icolor 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The cells that I have applied the formatting to require the positive/negative numbers being displayed to be added up from a selection of other cells i.e. =SUM(C22,C30,C38,C47,C55,C65,C73). When I put the formula into the cells to get the total number my formatting stops working properly.

Can anyone help me fix this if possible please?

Im hoping there is an Excel Formula (not VBA Code) solution to this problem.

I understand how to use the Substitute command in Excel, but I would like to be able to provide a list of words and have the Substitute command use that list to replace every occurrence within a given cell with a blank ("").

The twist to this is, that the List of Words will be Dynamic, and thus the formula will need to account for that.

NOTE: the formula should NOT replace parts of words, e.g., if the List Word is "can" and the cell to be evaluated contains "candle" the formula should NOT replace the "can" in "candle" with "". Only whole word matches should be replaced.

The example work book shows my feeble attempts at figuring this out.

Again, im looking for non-macro, non-VBA solution...thanks!

I currently have a simple If statement evaluate the contents of a cell on another worksheet Lets call it WK1.

If the value of the cell in WK1 meets the criteria, it is copied to the formula cell in the active worksheet (we'll call this WK2). (simple stuff so far)

Now I want to nest another IF statement (within the first one mentioned) which evaluates the color of the cell in WK1; copying it (or turning on the same color in the formula cell) if the cell color being evaluated is a specific value (not clear)

Conditional formatting will not do the trick here, as I sort the "if formula" cells in WK2....

I notice conditional formatting doesn't change or follow cells when they're sorted...however, if the cell is colored manually, the cell color does move with the value when sorted. I use the color to separate names from an aphabetized list. (former employees vs current employees)

I hope to have a solution in the workbook... as opposed to a macro...
Thanks in advance.

I need to delete a row if one of the cells in that row contains a Z but not the ones that contain a ZC# (i.e. ZC5, ZC6).

For example.. the row that contains ADH-15-STD ZKPINTC1-2500 needs to be deleted.

Thanks,
Bill

ZC5 ZC5 ZC5
ADH-15-STD QR87-18450-20R
ZC6 ZC6 ZC6
ZC7 ZC7 ZC7
ADH-15-STD CD-QUAL-1
ZC8 ZC8 ZC8
ADH-15-STD ZKPINTC1-2500
ADH-15-STD ZKPINTC1-1900
ADH-15-STD ZKPINTC1-2400
ADH-15-STD ZKPINTC1-2000
ADH-15-STD ZKPINTC1-2300
ADH-15-STD ZKPINTC1-2100
ADH-15-STD ZKPINTC1-2200
ADH-15-STD ZKC-965-2700
ADH-15-STD ZKC-965-2600
ADH-15-STD ZKC-965-2500
ADH-15-STD ZKC-965-2400
ADH-15-STD ZKC-965-2300
ADH-15-STD ZKC-965-1700
ADH-15-STD ZKC-965-1800
ADH-15-STD ZKC-965-1900
ADH-15-STD ZKC-965-2000
ADH-15-STD ZKC-965-2100
ADH-15-STD ZKC-965-2200
ADH-15-AZ3312 Q312-13400-SRRC
ADH-15-AZ3312 Q312-13400-20R
ADH-15-AZ3312 Q312-13400-20R

I am drawing a total blank here, lets say I have a column of cells:

A1 ("Word")
A2 ("No")
A3 ("No")
A4 ("Word")
A5 ("Word")

(they all have words in them). How can I return all cells (an array?) that contain a specific word?

=COUNTIF(A1:A5,"Word")

returns: 3

That function is almost perfect, except that returns a number - I need this to return an array of all of those cells that contain the word. Because I have another function to run after that takes in cells and THEN counts how many are a certain color (this one already works) - so it obviously cannot take in a number, it needs a list of cells

So it should return:

A1,A4,A5

But I'm not sure in what format

Thank you so much

I am trying to calculate a percentage change between two ratios.

I am getting a #VALUE! error for some but not all of my cells. For instance:

83:76 38:27 #VALUE! whereas I get

11:17 29:26 (61.66%) . In tracing the error Excel says the cell being evaluated contains a constant. I don't understand this. Can someone shed some light on this?

Thanks

Tom.

Hello:

I just typed a formula in a cell and it displays =A4+J13 (the same formula
calculates fine in a different cell), instead of claculating the value. Both
A4 and J13 contain numeric data. I try Ctrl+~ to toggle and chk if full
formula mode was ON? but that did not help either.

I also tried looking for feedback through the formula evaluation utility in
Excel, no help there either, it says cell being evaluated contains a constant?

Your help is appreciated

Cells C8 and L8 represent entry and exit dates. I used the formula
=(L8-G8)/7 to find the number of weeks between the dates. This works when
there are dates in cells C8 ands L8. However, when the cells do not contain
dates, I get the VALVE error reading in the cell designated to show rthe
number of weeks. How can I modify the formula to address instances when the
cells will be left empty?

Thanks

As you can see in the title i would like to delete all cells except those that contain a "&" symbol. for instance if i had these cells of info
A B C D E
1 dog mouse
2 cat dog&cat
3 hat&house hat
4 Car house car&dog
5 paper

I would like to make a macro so I can delete the cells i dont want and look like this

A B C D E
1
2 dog&cat
3 hat&house
4 car&dog
5

any help is appreciated. thanks

Hi

I need to know how to select different cells to be used in a formula, but i
don't know what is the separator that i need to use to this, see the example
bellow

Cells that i need to include on my formula:

A1
C5
BH32

Thanks.

how can i select all the cells with same color on a sheet if there are
multipale colors by vba code

If column width is 8.43, what should the row height be to make a square?


No luck finding an answer? You could always try Google.