have a cell give warning

[billy boy]

I have a spread sheet with lots of data. The top row of each column looks for
the max number in that column. Is there a way for excel to warn me when
entering data that the number I just entered was max in that column. That
would really help me out. Thanks

 

[RagDyeR]

One way would be to keep this top row always in view by using "Freeze
Panes".

That way you could immediately see it display there as you enter that max
number.

Another option might be to use "Conditional Formatting" on all the rows, so
that the max entry would perhaps turn red as it was entered.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I have a spread sheet with lots of data. The top row of each column looks
for
the max number in that column. Is there a way for excel to warn me when
entering data that the number I just entered was max in that column. That
would really help me out. Thanks

 

[Ron Coderre]

Try this:

For an input area of A1:A100
Select that range, with A1 as the active cell

Data>Data Validation
Allow: Custom
Formula: =A1<>MAX($A$1:$A$100)
Error Alert: Information
(enter the message you want displayed)
Click [OK]

That will display the message whenever the new amount is greater than or
equal to the maximum value in Cells A1:A100.

Does that help?

***********
Regards,
Ron

 

[David Billigmeier]

There are many ways, here is 1:

Select your range (in this case i'm assuming A1:A40), and go to
Format->Conditional Formatting->Check 'Formula Is', then enter this formula:

=A1=MAX($A$1:$A$40)

Make sure (in this case that is) that the active cell is A1. Then choose
whichever formatting you would like.

 

[billy boy]

Ron,
Where do I put the formula?

Try this:

For an input area of A1:A100
Select that range, with A1 as the active cell

Data>Data Validation
Allow: Custom
Formula: =A1<>MAX($A$1:$A$100)
Error Alert: Information
(enter the message you want displayed)
Click [OK]

That will display the message whenever the new amount is greater than or
equal to the maximum value in Cells A1:A100.

Does that help?

***********
Regards,
Ron

I have a spread sheet with lots of data. The top row of each column looks for
the max number in that column. Is there a way for excel to warn me when
entering data that the number I just entered was max in that column. That
would really help me out. Thanks

 

[billy boy]

RagDyer,
Thanks for your help.
Could you tell me how to conditional format. Thats what I would like it to
do, turn red or something.

 

[Ron Coderre]

That formula goes in the formula field of the Data Validation setup window.

Does that help?

***********
Regards,
Ron

Ron,
Where do I put the formula?

Try this:

For an input area of A1:A100
Select that range, with A1 as the active cell

Data>Data Validation
Allow: Custom
Formula: =A1<>MAX($A$1:$A$100)
Error Alert: Information
(enter the message you want displayed)
Click [OK]

That will display the message whenever the new amount is greater than or
equal to the maximum value in Cells A1:A100.

Does that help?

***********
Regards,
Ron

I have a spread sheet with lots of data. The top row of each column looks for
the max number in that column. Is there a way for excel to warn me when
entering data that the number I just entered was max in that column. That
would really help me out. Thanks

 

[David Billigmeier]

billy boy... look at my post for how to set up conditional formatting. You
can format the cell to show any color you want.

 

[billy boy]

Dave, that worked perfect, but now I'm trying to do the same thng but with
cells i9 thru i39 and i305.
Is that possible?

 

[billy boy]

Ron,
Thanks, it works perfect and I was wonder what the formula would be for
cells like a1 thru a20 then skip a few cells then a30 thru a40?
Thanks again

That formula goes in the formula field of the Data Validation setup window.

Does that help?

***********
Regards,
Ron

Ron,
Where do I put the formula?

Try this:

For an input area of A1:A100
Select that range, with A1 as the active cell

Data>Data Validation
Allow: Custom
Formula: =A1<>MAX($A$1:$A$100)
Error Alert: Information
(enter the message you want displayed)
Click [OK]

That will display the message whenever the new amount is greater than or
equal to the maximum value in Cells A1:A100.

Does that help?

***********
Regards,
Ron


:

I have a spread sheet with lots of data. The top row of each column looks for
the max number in that column. Is there a way for excel to warn me when
entering data that the number I just entered was max in that column. That
would really help me out. Thanks

 

[Bernard Liengme]

Select I9:I39, hold CTRTL and click on I305 - that's how to select
non-contiguous ranges
Use conditional format formula =I9=MAX(I9:I39, I305) - MAX can take up to
30 arguments
best wishes

 

[Ron Coderre]

Try this in the Data Validation setup window:

Formula: =(A1<MAX($A$1:$A$20,$A$30:$A$40))

Does that do what you want?

***********
Regards,
Ron

Ron,
Thanks, it works perfect and I was wonder what the formula would be for
cells like a1 thru a20 then skip a few cells then a30 thru a40?
Thanks again

That formula goes in the formula field of the Data Validation setup window.

Does that help?

***********
Regards,
Ron

Ron,
Where do I put the formula?

:

Try this:

For an input area of A1:A100
Select that range, with A1 as the active cell

Data>Data Validation
Allow: Custom
Formula: =A1<>MAX($A$1:$A$100)
Error Alert: Information
(enter the message you want displayed)
Click [OK]

That will display the message whenever the new amount is greater than or
equal to the maximum value in Cells A1:A100.

Does that help?

***********
Regards,
Ron


:

I have a spread sheet with lots of data. The top row of each column looks for
the max number in that column. Is there a way for excel to warn me when
entering data that the number I just entered was max in that column. That
would really help me out. Thanks