Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
only allow one cell inputed for a range of cells
I am making a spread sheet to be used for data entry. the data entered is an
"x" with in a column. But I only want to allow one cell from f13:j13 to be entered Ex f13 is unsat g13 in below ave h13 is average and so on so by puting an "x" in f13 it totals down and I don't want a double entry within the range of f13:j13 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
only allow one cell inputed for a range of cells
Hi Brian,
Try Data Validation. Select F13:J13 xl2007 version: Data tab on ribbon-Data tools block-Data Validation pre xl2007 versions: Menu item Data-Validation- In allow field: Select Custom Uncheck Ignore blanks In Formula field: =COUNTA($F$13:$J$13)<2 This validation does not confirm that an X has been entered but it only allows one of the cells to have an entry. Regards, OssieMac "Brian" wrote: I am making a spread sheet to be used for data entry. the data entered is an "x" with in a column. But I only want to allow one cell from f13:j13 to be entered Ex f13 is unsat g13 in below ave h13 is average and so on so by puting an "x" in f13 it totals down and I don't want a double entry within the range of f13:j13 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
only allow one cell inputed for a range of cells
Hi Brian,
Did a bit more work on your request and the following will validate for only one X in the range and the user can only enter X or blank. However, it is not case sensitive and x and X are valid entries. Select range of cells for Conditional format (F13:J13) Select Data validation as follows:- xl2007 version: Ribbon tab Data-Data Validation (In Data Tools block) Pre xl2007 versions: Menu item Data-Validation. In the Data Validation dialog box:- Allow field: Select Custom Ignore blank: Uncheck Formula field: =AND(COUNTA($F$13:$J$13)<2,OR(F13="X",F13="")) The following is optional:- Select Input Message tab Input Message field: Enter X in one cell only for range G13:J13 Select Error Alert tab Error Message field: Only X is valid and only in one field in range G13:J13 Click OK to exit. Some notes on entering validation formulas:- Formula must evaluate to true to permit entry of data. Validation Formula is entered as if entering the formula for one cell only (the Active cell of the selected range.). The Active cell of the range is the one which is bound by the thick border but the interior is not highlighted like the rest. The range reference used in the above formula like G13:J13 above needs to be entered in absolute format. (That is $G$13:$J$13) otherwise as Excel copies them to the adjacent cells, the reference will change to H13:K13, I13:L13 etc. (However, depending on the actual validation formula, it might be that a range is meant to change such as testing the previous 4 cells progressively across the page.) Testing the value of individual cells across the range like in the above formula where it is testing if the cell is X or blank, the cell reference is in G13 format so that it DOES CHANGE to H13, I13 etc as Excel copies it across the range. Regards, OssieMac "Brian" wrote: I am making a spread sheet to be used for data entry. the data entered is an "x" with in a column. But I only want to allow one cell from f13:j13 to be entered Ex f13 is unsat g13 in below ave h13 is average and so on so by puting an "x" in f13 it totals down and I don't want a double entry within the range of f13:j13 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
only allow one cell inputed for a range of cells
Thanks alot. That hit the nail on the head. I've been racking my brains.
"OssieMac" wrote: Hi Brian, Did a bit more work on your request and the following will validate for only one X in the range and the user can only enter X or blank. However, it is not case sensitive and x and X are valid entries. Select range of cells for Conditional format (F13:J13) Select Data validation as follows:- xl2007 version: Ribbon tab Data-Data Validation (In Data Tools block) Pre xl2007 versions: Menu item Data-Validation. In the Data Validation dialog box:- Allow field: Select Custom Ignore blank: Uncheck Formula field: =AND(COUNTA($F$13:$J$13)<2,OR(F13="X",F13="")) The following is optional:- Select Input Message tab Input Message field: Enter X in one cell only for range G13:J13 Select Error Alert tab Error Message field: Only X is valid and only in one field in range G13:J13 Click OK to exit. Some notes on entering validation formulas:- Formula must evaluate to true to permit entry of data. Validation Formula is entered as if entering the formula for one cell only (the Active cell of the selected range.). The Active cell of the range is the one which is bound by the thick border but the interior is not highlighted like the rest. The range reference used in the above formula like G13:J13 above needs to be entered in absolute format. (That is $G$13:$J$13) otherwise as Excel copies them to the adjacent cells, the reference will change to H13:K13, I13:L13 etc. (However, depending on the actual validation formula, it might be that a range is meant to change such as testing the previous 4 cells progressively across the page.) Testing the value of individual cells across the range like in the above formula where it is testing if the cell is X or blank, the cell reference is in G13 format so that it DOES CHANGE to H13, I13 etc as Excel copies it across the range. Regards, OssieMac "Brian" wrote: I am making a spread sheet to be used for data entry. the data entered is an "x" with in a column. But I only want to allow one cell from f13:j13 to be entered Ex f13 is unsat g13 in below ave h13 is average and so on so by puting an "x" in f13 it totals down and I don't want a double entry within the range of f13:j13 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
only allow one cell inputed for a range of cells
one more thing off this subject. I've got calculations adding up different
cells on the page giving a final grade. But I need the grade to be no lower than 70. here is my cell formula. =IF(SUM(F19,Q19,F38,Q38,(F49:J49))0,SUM(F19,Q19,F 38,Q38,(F49:J49)),("")) "OssieMac" wrote: Hi Brian, Try Data Validation. Select F13:J13 xl2007 version: Data tab on ribbon-Data tools block-Data Validation pre xl2007 versions: Menu item Data-Validation- In allow field: Select Custom Uncheck Ignore blanks In Formula field: =COUNTA($F$13:$J$13)<2 This validation does not confirm that an X has been entered but it only allows one of the cells to have an entry. Regards, OssieMac "Brian" wrote: I am making a spread sheet to be used for data entry. the data entered is an "x" with in a column. But I only want to allow one cell from f13:j13 to be entered Ex f13 is unsat g13 in below ave h13 is average and so on so by puting an "x" in f13 it totals down and I don't want a double entry within the range of f13:j13 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
only allow one cell inputed for a range of cells
Hi Brian,
Pleased that I have helped so far and will try to help with you new question. I see that you only want to sum it if it is greater than zero. However, what do you want to do if the sum is less than 70. Like if it adds up to 68 do you want to insert 70 instead? I am going to bed now so will look at your reply in the morning. Regards, OssieMac "Brian" wrote: one more thing off this subject. I've got calculations adding up different cells on the page giving a final grade. But I need the grade to be no lower than 70. here is my cell formula. =IF(SUM(F19,Q19,F38,Q38,(F49:J49))0,SUM(F19,Q19,F 38,Q38,(F49:J49)),("")) "OssieMac" wrote: Hi Brian, Try Data Validation. Select F13:J13 xl2007 version: Data tab on ribbon-Data tools block-Data Validation pre xl2007 versions: Menu item Data-Validation- In allow field: Select Custom Uncheck Ignore blanks In Formula field: =COUNTA($F$13:$J$13)<2 This validation does not confirm that an X has been entered but it only allows one of the cells to have an entry. Regards, OssieMac "Brian" wrote: I am making a spread sheet to be used for data entry. the data entered is an "x" with in a column. But I only want to allow one cell from f13:j13 to be entered Ex f13 is unsat g13 in below ave h13 is average and so on so by puting an "x" in f13 it totals down and I don't want a double entry within the range of f13:j13 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
only allow one cell inputed for a range of cells
If the sum is less than 70 I would like it to still show 70. In the big
picture with grade averages I can only allow the lowest grade to be a 70. Thanks again. "OssieMac" wrote: Hi Brian, Pleased that I have helped so far and will try to help with you new question. I see that you only want to sum it if it is greater than zero. However, what do you want to do if the sum is less than 70. Like if it adds up to 68 do you want to insert 70 instead? I am going to bed now so will look at your reply in the morning. Regards, OssieMac "Brian" wrote: one more thing off this subject. I've got calculations adding up different cells on the page giving a final grade. But I need the grade to be no lower than 70. here is my cell formula. =IF(SUM(F19,Q19,F38,Q38,(F49:J49))0,SUM(F19,Q19,F 38,Q38,(F49:J49)),("")) "OssieMac" wrote: Hi Brian, Try Data Validation. Select F13:J13 xl2007 version: Data tab on ribbon-Data tools block-Data Validation pre xl2007 versions: Menu item Data-Validation- In allow field: Select Custom Uncheck Ignore blanks In Formula field: =COUNTA($F$13:$J$13)<2 This validation does not confirm that an X has been entered but it only allows one of the cells to have an entry. Regards, OssieMac "Brian" wrote: I am making a spread sheet to be used for data entry. the data entered is an "x" with in a column. But I only want to allow one cell from f13:j13 to be entered Ex f13 is unsat g13 in below ave h13 is average and so on so by puting an "x" in f13 it totals down and I don't want a double entry within the range of f13:j13 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
only allow one cell inputed for a range of cells
=MAX(70,SUM(.......))
will return 70 if the sum is less than 70 -- Regards, Peo Sjoblom "Brian" wrote in message ... If the sum is less than 70 I would like it to still show 70. In the big picture with grade averages I can only allow the lowest grade to be a 70. Thanks again. "OssieMac" wrote: Hi Brian, Pleased that I have helped so far and will try to help with you new question. I see that you only want to sum it if it is greater than zero. However, what do you want to do if the sum is less than 70. Like if it adds up to 68 do you want to insert 70 instead? I am going to bed now so will look at your reply in the morning. Regards, OssieMac "Brian" wrote: one more thing off this subject. I've got calculations adding up different cells on the page giving a final grade. But I need the grade to be no lower than 70. here is my cell formula. =IF(SUM(F19,Q19,F38,Q38,(F49:J49))0,SUM(F19,Q19,F 38,Q38,(F49:J49)),("")) "OssieMac" wrote: Hi Brian, Try Data Validation. Select F13:J13 xl2007 version: Data tab on ribbon-Data tools block-Data Validation pre xl2007 versions: Menu item Data-Validation- In allow field: Select Custom Uncheck Ignore blanks In Formula field: =COUNTA($F$13:$J$13)<2 This validation does not confirm that an X has been entered but it only allows one of the cells to have an entry. Regards, OssieMac "Brian" wrote: I am making a spread sheet to be used for data entry. the data entered is an "x" with in a column. But I only want to allow one cell from f13:j13 to be entered Ex f13 is unsat g13 in below ave h13 is average and so on so by puting an "x" in f13 it totals down and I don't want a double entry within the range of f13:j13 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
only allow one cell inputed for a range of cells
Hi again Brian,
Looks like Peo's answer is what you need. However, just as a little more information, you can dispense with some of the brackets in your formula. With Peo's suggestion included your formula should be:- =IF(SUM(F19,Q19,F38,Q38,F49:J49)0,MAX(70,SUM(F19, Q19,F38,Q38,F49:J49)),"") Note that the above is actually one line but has broken during posting. Regards, OssieMac "Peo Sjoblom" wrote: =MAX(70,SUM(.......)) will return 70 if the sum is less than 70 -- Regards, Peo Sjoblom "Brian" wrote in message ... If the sum is less than 70 I would like it to still show 70. In the big picture with grade averages I can only allow the lowest grade to be a 70. Thanks again. "OssieMac" wrote: Hi Brian, Pleased that I have helped so far and will try to help with you new question. I see that you only want to sum it if it is greater than zero. However, what do you want to do if the sum is less than 70. Like if it adds up to 68 do you want to insert 70 instead? I am going to bed now so will look at your reply in the morning. Regards, OssieMac "Brian" wrote: one more thing off this subject. I've got calculations adding up different cells on the page giving a final grade. But I need the grade to be no lower than 70. here is my cell formula. =IF(SUM(F19,Q19,F38,Q38,(F49:J49))0,SUM(F19,Q19,F 38,Q38,(F49:J49)),("")) "OssieMac" wrote: Hi Brian, Try Data Validation. Select F13:J13 xl2007 version: Data tab on ribbon-Data tools block-Data Validation pre xl2007 versions: Menu item Data-Validation- In allow field: Select Custom Uncheck Ignore blanks In Formula field: =COUNTA($F$13:$J$13)<2 This validation does not confirm that an X has been entered but it only allows one of the cells to have an entry. Regards, OssieMac "Brian" wrote: I am making a spread sheet to be used for data entry. the data entered is an "x" with in a column. But I only want to allow one cell from f13:j13 to be entered Ex f13 is unsat g13 in below ave h13 is average and so on so by puting an "x" in f13 it totals down and I don't want a double entry within the range of f13:j13 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to have result of excell formula remian as if inputed | Excel Discussion (Misc queries) | |||
How do I formate cells in excell to show inputed date as 09/13/ | Excel Worksheet Functions | |||
Copy cells into range of cells until cell change | Excel Worksheet Functions | |||
When I open up excel a worksheet that has been inputed opens up w. | Excel Discussion (Misc queries) | |||
VBA: Return Searched Value Inputed by End-User | Excel Worksheet Functions |