 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Setup / April 2005Creating a check mark box
I would like to create a check mark box in my excel document so that I can click on it and a check mark will appear. I would then like to create a formula that would add a dollar amount in the total column if the checkmark is present. Could someone please let me know how I can go about doing this? Show the Forms toolbar (View|toolbars|Forms) You'll see a checkbox on that toolbar. Click on it and draw your checkbox in the cell you want. Now right click on that checkbox and select Format Control Then on the Control Tab, assign a nice Cell link (I'll use A1). (if you protect that sheet, make sure that cell link is unlocked.) Now use a formula that checks the value in that cell link: =if(a1<>true,"",sum(b:b)) (don't put this formula in Column B--or adjust the range: =if(a1<>true,"",sum(b2:B9999)) > I would like to create a check mark box in my excel document so that I can > click on it and a check mark will appear. I would then like to create a > formula that would add a dollar amount in the total column if the checkmark > is present. Could someone please let me know how I can go about doing this?  SignatureDave Peterson Hi Dave, thanks for the quick response. I am still having trouble, please forgive me, as I am learning as I go. When i am trying to do the cell link, I am getting an error "The text you entered is not a valid reference or defined name". I wrote =if(a37<>true,"",then(U37=$375.00). If the box is checked then I want Excel to enter the dollar amount (375.00) in the total amount. I am creating an electronic ticket. Please let me know if you can help me. I appreciate your time, Sharlene > Show the Forms toolbar (View|toolbars|Forms) > [quoted text clipped - 14 lines] > > formula that would add a dollar amount in the total column if the checkmark > > is present. Could someone please let me know how I can go about doing this? First, a formula can only return a value to that cell that holds the function. So you'd have to put this in U37: =if(a37<>true,"",375) But I don't see how this fits in your Total amount???? > Hi Dave, thanks for the quick response. I am still having trouble, please > forgive me, as I am learning as I go. When i am trying to do the cell link, I [quoted text clipped - 29 lines] > > > > Dave Peterson SignatureDave Peterson Sorry I don't think I explained myself properly. I am creating an electronic field ticket. In this case, if an employee had used this product, they would check the box in a37, and that would put the $375. amount in the total column on the right hand side of the ticket column. If the product is being used, the box is not checked, and the $375 value won't be put in the total column. I hope this makes sense. Thanks for all your help Dave, I really appreciate it. Do you visit this site often, I would love to pick your brain about some other stuff, if the need arose? > First, a formula can only return a value to that cell that holds the function. > [quoted text clipped - 36 lines] > > > > > > Dave Peterson I'm hear lots of times. But if you post, you'll get a lot of responses. I still think that formula would work--unless I'm missing something. This is equivalent, but I find it easier to read: =if(a37=true,375,"") or =if(a37=true,375,0) This formula does go into one of the cells in the total column. > Sorry I don't think I explained myself properly. I am creating an electronic > field ticket. In this case, if an employee had used this product, they would [quoted text clipped - 49 lines] > > > > Dave Peterson SignatureDave Peterson New problem. Check box works initially, but if I click on the box to remove the checkmark it gives me "False" in the total column. Then if I click on it again to give me a check mark it gives me "True" instead of my value ($375.00). Please help. > Sorry I don't think I explained myself properly. I am creating an electronic > field ticket. In this case, if an employee had used this product, they would [quoted text clipped - 45 lines] > > > > > > > > Dave Peterson Is your linked cell in the range of stuff you want to add? If yes, move that linked cell out of the way. If no, post back the formula you use, the cell that its in and the linked cell's address (all three). > New problem. Check box works initially, but if I click on the box to remove > the checkmark it gives me "False" in the total column. Then if I click on it [quoted text clipped - 54 lines] > > > > > > Dave Peterson SignatureDave Peterson Sorry Dave, I'm not sure I'm understanding what you mean. How do I know if it is in the range that I want to add? How do I move the linked cell?How do I post back the formula and link all three? Sorry I'm not following your reply. . .. > New problem. Check box works initially, but if I click on the box to remove > the checkmark it gives me "False" in the total column. Then if I click on it [quoted text clipped - 50 lines] > > > > > > > > > > Dave Peterson When you put the checkbox on the worksheet, you assigned a linked cell. #1. What was the address of that linked cell? When you wrote the formula in the worksheet, you typed it into a cell. #2. What was the address of that cell? When you wrote the formula to total your numbers, you wrote a formula in a cell? #3. What was the address of that cell that contained the formula? #4. And what was the formula you used? (You can copy the formula from the formula bar and paste into your response.) Your responses here: #1. #2. #3. #4. > Sorry Dave, I'm not sure I'm understanding what you mean. How do I know if > it is in the range that I want to add? How do I move the linked cell?How do [quoted text clipped - 59 lines] > > > > > > > > Dave Peterson SignatureDave Peterson Hi Dave, 1.U37 2.u37 3. u37 4.=IF(A37<>TRUE,"",375). But when I uncheck the cell (A37), it says FALSE in U37. When I click on U37 to read the formula, it is no longer there, it just shows FALSE in the formula bar. If I click on A37 (checkmark), U37 then changes from FALSE to TRUE, but still my formula is erased. I hope this makes sense to you Dave . . . . > When you put the checkbox on the worksheet, you assigned a linked cell. > #1. What was the address of that linked cell? [quoted text clipped - 80 lines] > > > > > > > > > > Dave Peterson You won't want to put that formula in U37. That's the cell that the checkbox uses. You'll want to use another cell (column V????) to hold the formula. > Hi Dave, > [quoted text clipped - 98 lines] > > > > Dave Peterson SignatureDave Peterson Hi Dave, That makes sense now that you mention that, but I am unsure of how to write in my formula that I want the total to end up in U37. I am currently using " if(a37=<>true,"",375). Sorry so many questions, I appreciate your help Dave. > You won't want to put that formula in U37. That's the cell that the checkbox > uses. You'll want to use another cell (column V????) to hold the formula. [quoted text clipped - 101 lines] > > > > > > Dave Peterson I'm kind of confused. I was thinking that you had multiple checkboxes that you'd look at. Do you have a single checkbox that overrides all the values or do you have multiple checkboxes that you have individual formulas (and those contribute to the sum)? If you have multiple checkboxes, you can put this kind of formula in column U: (U2 and copy down (headers in row 1???)) =if(a2<>true,"",375) Then to get the sum, you can just use: =sum(u2:u36) (36 is the last row with that formula.) > Hi Dave, > [quoted text clipped - 111 lines] > > > > Dave Peterson SignatureDave Peterson Hi Dave, I am making an electronic field ticket, I do have more than one check box, but not for the entire ticket. Some things will always be charged out, so that was fairly easy to do, but some things are only charged out if they are used. So that's where the check boxes came in. If the product/service was used, the check mark box is checked and then I wanted a value to automatically go in the subtotal column. So . . . A37 = check mark box with a cell link to U37 U37 = an entry in my subtotal column for the entire ticket, want this cell to either read $375.00 (if checked) and $0.00 (if unchecked).Was putting in the formula -->if(a37=<>true,"",375) in U37 but you mentioned that I should put this in another cell. My question is "Should my formula have U37 referenced in it so that my values end up in U37?" I'm not sure if I answered your questions, let me know if you need more information > I'm kind of confused. I was thinking that you had multiple checkboxes that > you'd look at. [quoted text clipped - 130 lines] > > > > > > Dave Peterson I think I'd do this. Put the checkbox in A37 But link that checkbox to Cell A37 give A37 a custom format: format|cell|number tab|custom|;;; (just 3 semicolons--so the cell looks empty) Then in U37, put this formula: =if(a37=true,375,"") Which is equivalent to the old formula. =if(a37<>true,"",375) Then just use: =sum(U:U) or whatever rows you need =sum(u2:U44) ???? > Hi Dave, > [quoted text clipped - 152 lines] > > > > Dave Peterson SignatureDave Peterson Dave you are amazing, it works like a charm!!!! I would like to tweak it a little though, is there anyway that I can make the cell show a " $ - ", (dash) if the box is unchecked. . . . . > I think I'd do this. > [quoted text clipped - 169 lines] > > > > > > Dave Peterson Instead of this formula: =if(a37=true,375,"") use: =if(a37=true,375,0) but format that cell using that $ icon on the toolbar. > Dave you are amazing, it works like a charm!!!! I would like to tweak it a > little though, is there anyway that I can make the cell show a " $ - ", [quoted text clipped - 177 lines] > > > > Dave Peterson SignatureDave Peterson
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.