Solved

Functions not working in xlsm (Excel 2010)

Posted on 2012-03-29
275 Views
Hi, I've been developing this attached spreadsheet with the help of a few experts here. I've expanded upon it and am running into a couple of simple but puzzling problems.

Q1. How come Cell A1 and A2 are not computing??

A1 =COUNTA(A6:A23)
A2 =SUM(E6:E31)

I must have entered a "mode" in Excel but don't know what it is. The cells literally display the above formulas instead of computing the values.

Q2. in Column P and Q I have conditional formatting. How come only column P is highlighted? P and Q should both be highlighted as the range applies to both columns.

Thanks and best regards, Lyteck
Field-validation-test.xlsm
0
Question by:lyteck
• 2
• 2

LVL 43

Assisted Solution

Saqib Husain, Syed earned 400 total points
ID: 37783515
Press ctrl-`

it is the backwards apostrophe. On my keyboard it is below the Esc key next to 1
0

Author Comment

ID: 37783555
Ok that did it! Thanks a lot.

Any idea perhaps on the formatting of column Q? (my second question)
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 400 total points
ID: 37783590
Sorry, missed that part.

Change the formula to

=AND(IF((\$D6="Y"),TRUE),COUNTIF(P6:Q6,"<>")<>2)

\$ sign with D
0

Author Closing Comment

ID: 37783735
Excellent and straight to the point. Thanks for your speed. Best, Lyteck
0

Featured Post

Suggested Solutions

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…