| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
have a cell give warning
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
have a cell give warning
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! ================================================== === "billy boy" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
have a cell give warning
Try this:
For an input area of A1:A100 Select that range, with A1 as the active cell DataData 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 "billy boy" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
have a cell give warning
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. -- Regards, Dave "billy boy" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
have a cell give warning
Ron,
Where do I put the formula? "Ron Coderre" wrote: Try this: For an input area of A1:A100 Select that range, with A1 as the active cell DataData 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 "billy boy" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
have a cell give warning
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. "RagDyeR" wrote: 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! ================================================== === "billy boy" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
have a cell give warning
That formula goes in the formula field of the Data Validation setup window.
Does that help? *********** Regards, Ron "billy boy" wrote: Ron, Where do I put the formula? "Ron Coderre" wrote: Try this: For an input area of A1:A100 Select that range, with A1 as the active cell DataData 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 "billy boy" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
have a cell give warning
billy boy... look at my post for how to set up conditional formatting. You
can format the cell to show any color you want. -- Regards, Dave "billy boy" wrote: 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. "RagDyeR" wrote: 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! ================================================== === "billy boy" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
have a cell give warning
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? "David Billigmeier" wrote: 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. -- Regards, Dave "billy boy" wrote: 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
have a cell give warning
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 "Ron Coderre" wrote: That formula goes in the formula field of the Data Validation setup window. Does that help? *********** Regards, Ron "billy boy" wrote: Ron, Where do I put the formula? "Ron Coderre" wrote: Try this: For an input area of A1:A100 Select that range, with A1 as the active cell DataData 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 "billy boy" wrote: 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
have a cell give warning
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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "billy boy" wrote in message ... 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? "David Billigmeier" wrote: 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. -- Regards, Dave "billy boy" wrote: 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
have a cell give warning
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 "billy boy" wrote: 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 "Ron Coderre" wrote: That formula goes in the formula field of the Data Validation setup window. Does that help? *********** Regards, Ron "billy boy" wrote: Ron, Where do I put the formula? "Ron Coderre" wrote: Try this: For an input area of A1:A100 Select that range, with A1 as the active cell DataData 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 "billy boy" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions | |||
| Transfer Cell Formatting for linked cells | Excel Discussion (Misc queries) | |||
| What is the warning triangle in a cell and how do I get rid of it. | Excel Discussion (Misc queries) | |||
| Possible Lookup Table | Excel Worksheet Functions | |||
| how to give cell reference using Combo Boxes in Excel? | Excel Discussion (Misc queries) | |||
Linear Mode
