Excel forumla - any cell in range over 7,000,000 then add note 1

[Eprice]

Hi Excel Experts,
I am trying to find a way to add a note at the bottom of a worksheet if the value of any cell is over $7,000,000. I don't want to add a formula for each cell if possible, the spreadsheet is large, but use a range instead. Is there a way to say if any cell in 'Range' is over 7,000,000 then show 'Notice' in a cell at the bottom of the page and add an '*' at the beginning of the cell that is over $7,000,000? I am pretty good with Excel but this is a little over my head. Thanks in advance.
Lisa

 

[kjv1611]

What you're wanting to do will require VBA, I think.

I'd suggest posting the thought there if you want to proceed in that direction:
forum707

 

[bluedragon2]

I would not suggest putting an * on a data field, but rather use conditional formatting to high lite the cell/text if it is 7000000 or more. For the notice, you can use a formula to present a message if a field meets your criteria and blank if not.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[MCuthill]

EPrice,

Echoing Blue's comment, placing an asterisk infront of the number seems like a troublesome idea.

The formula for the message is rather simple though:

=IF(MAX(YourRange)>7000000,"Notice","")

Conditional formatting to identify "all" cells over 7,000,000 would be one approach, or you could place a column next to the value range with the follwoing formula to identify the cell which is the max.

=IF(CellInRange=CellwithMAXformula, "*","")

for example, if your "Notice" cell is B101 and the range is B1:B100, A1 may have a formula such as:

=IF(B1=$B$101,"*","")

-- this could then be filled down, or copied and pasted to B2 through B100.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[Eprice]

Thanks Mike, that formula worked. Using an extra column for the '*' is good and if changes are needed to 7,000,000, using edit and replace is an easy fix. Thanks for your help.
Lisa

 

[MCuthill]

You're Welcome Lisa.

As a couple quick notes

- you can also put your threshold in a cell above, or next to the range and either hide the column/row or set the text to the same as the background color. Then reference the threshold in your formula - if it ever changes, you don't even need to find/replace and can just change the value of this cell.

- my formula for the asterisk will only place one asterisk in the column, or rather ONLY next to the maximum value. If you want an asterisk next to all those values over the threshold, the formula would need to be changed to: =IF(B1>7000000,"*","") (or reference the threshold cell, obviously) and filled down the length of the range.

Have a great day!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[SkipVought]

The other thing that I would suggest is to replace the hard coded 7000000 value with a reference to a cell outside of the range that the user can enter the 7000000, or whatever other value it might be in the future.

It is a better design practice to avoid hard coding values in formulas.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MCuthill]

Great Minds Mr. Skip, Great Minds!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."