Excel 2010 Can you show the formula (as seen in the Formula Bar) in certain cells?

[PWD]

Good morning, we have spreadsheets with patient numbers, some of whom we share responsibilty with other hospitals. At the moment my boss just types the "formula" in a series of cells and then adds them up at the end of the column. Apart from this being extrememly frustrating (for me!) it can also lead to errors. For example we could have 6 + 1 + (3 + 5*0.5) + (2 + 1 * 0.5) + 4, etc. There are only about 10 rows involved but as these are related to our performance, I believe it critical that the figures are 100% accurate and that the best way of achieving that would be to take out the human element. Everone would still need to see this breakdown of the figures but I'd like them to be able to be calculated automatically in the 'Totals' row. I know I watched too much Star Trek as a youth so this is probaly just a fantasy wish, but I'd be grateful to know if it is doable.

Many thanks,
D€$

 

[strongm]

Not quite sure I completely understand what you are asking fro, but in (partial) response to the Subject of this post: In your Excel worksheet, go to the Formulas tab > Formula Auditing group and click the Show Formulas button. This answer is partial, because it shows the formulae in all cells that have one, rather than simply in selected cells.

 

[PWD]

Thanks strongm. Yes I figured that was possible but what I'd like to be able to do is to show the formulae for only particular cells but that the results would be visible everywhere else. At the moment the cells are, effectively, just Text and my boss adds them up manually at the end of the month. The ½ patients are those whose care we share with another hospital. In this example we have 5 shared in one Specialty and 1 shared in another. We have to see this breakdown 'at a glance' so as to know how/where to investigate if we fail to meet performance targets.

6
1
3 + 5x0.5
2 + 1x0.5
4
_________
19
=========

Many thanks,
D€$

 

[combo]

You can use helper cell with formula =FORMULATEXT(A1).
You can use it in data validation if you like to block boss, custom formula =LEFT(FORMULATEXT(A1),4)="=SUM". This will block your example, but still =SUM(2,3,0.5*3) will be possible.


combo

 

[PWD]

Hi combo, apart from the fact that this isn't available in Excel 2010, I think that something like this would confuse my boss too much! OK, so he's not as bad as Dilbert's boss - but you get the gist! Thanks anyway! I'll give up on this idea and find something to laminate instead.

Many thanks,
D€$

 

[SkipVought]

The whole thing, all 10 elements in 10 rows, can be layed out on a spreadsheet. Keep in mind that the 1/2 factor is a result of the number of hospitals that an employee shares his/her time with. Today it's 2. Tomorrow, or for Fred, it could be 3 or 4, who knows. So that's a "factor" or data element to plug in to a named range cell (1/NbrHosp). I imagine that each of the 10 rows is some specific category rating.

It can ALL be done by simply plugging in numbers that the formulas-that-change-not, calculate off of.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

I frequently give a hint in excel using data validation, just entry message without any limitations.

combo