A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Setting up and Configuration
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Creating a check mark box



 
 
Thread Tools Display Modes
  #11  
Old April 20th, 2005, 06:30 PM
MarthaSue
external usenet poster
 
Posts: n/a
Default

Hi Dave,

1.U37

2.u37

3. u37

4.=IF(A37TRUE,"",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 . . . .


"Dave Peterson" wrote:

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 he

#1.

#2.

#3.

#4.



MarthaSue wrote:

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. . ..

"MarthaSue" wrote:

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.

"MarthaSue" wrote:

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?

"Dave Peterson" wrote:

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(a37true,"",375)

But I don't see how this fits in your Total amount????




MarthaSue wrote:

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(a37true,"",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

"Dave Peterson" wrote:

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(a1true,"",sum(b:b))
(don't put this formula in Column B--or adjust the range:
=if(a1true,"",sum(b2:B9999))



MarthaSue wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

  #12  
Old April 20th, 2005, 10:46 PM
Dave Peterson
external usenet poster
 
Posts: n/a
Default

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.



MarthaSue wrote:

Hi Dave,

1.U37

2.u37

3. u37

4.=IF(A37TRUE,"",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 . . . .

"Dave Peterson" wrote:

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 he

#1.

#2.

#3.

#4.



MarthaSue wrote:

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. . ..

"MarthaSue" wrote:

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.

"MarthaSue" wrote:

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?

"Dave Peterson" wrote:

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(a37true,"",375)

But I don't see how this fits in your Total amount????




MarthaSue wrote:

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(a37true,"",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

"Dave Peterson" wrote:

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(a1true,"",sum(b:b))
(don't put this formula in Column B--or adjust the range:
=if(a1true,"",sum(b2:B9999))



MarthaSue wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #13  
Old April 22nd, 2005, 06:50 PM
MarthaSue
external usenet poster
 
Posts: n/a
Default

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.

"Dave Peterson" wrote:

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.



MarthaSue wrote:

Hi Dave,

1.U37

2.u37

3. u37

4.=IF(A37TRUE,"",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 . . . .

"Dave Peterson" wrote:

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 he

#1.

#2.

#3.

#4.



MarthaSue wrote:

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. . ..

"MarthaSue" wrote:

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.

"MarthaSue" wrote:

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?

"Dave Peterson" wrote:

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(a37true,"",375)

But I don't see how this fits in your Total amount????




MarthaSue wrote:

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(a37true,"",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

"Dave Peterson" wrote:

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(a1true,"",sum(b:b))
(don't put this formula in Column B--or adjust the range:
=if(a1true,"",sum(b2:B9999))



MarthaSue wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

  #14  
Old April 22nd, 2005, 08:05 PM
Dave Peterson
external usenet poster
 
Posts: n/a
Default

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(a2true,"",375)

Then to get the sum, you can just use:

=sum(u2:u36)

(36 is the last row with that formula.)


MarthaSue wrote:

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.

"Dave Peterson" wrote:

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.



MarthaSue wrote:

Hi Dave,

1.U37

2.u37

3. u37

4.=IF(A37TRUE,"",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 . . . .

"Dave Peterson" wrote:

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 he

#1.

#2.

#3.

#4.



MarthaSue wrote:

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. . ..

"MarthaSue" wrote:

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.

"MarthaSue" wrote:

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?

"Dave Peterson" wrote:

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(a37true,"",375)

But I don't see how this fits in your Total amount????




MarthaSue wrote:

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(a37true,"",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

"Dave Peterson" wrote:

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(a1true,"",sum(b:b))
(don't put this formula in Column B--or adjust the range:
=if(a1true,"",sum(b2:B9999))



MarthaSue wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #15  
Old April 25th, 2005, 06:39 PM
MarthaSue
external usenet poster
 
Posts: n/a
Default

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

"Dave Peterson" wrote:

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(a2true,"",375)

Then to get the sum, you can just use:

=sum(u2:u36)

(36 is the last row with that formula.)


MarthaSue wrote:

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.

"Dave Peterson" wrote:

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.



MarthaSue wrote:

Hi Dave,

1.U37

2.u37

3. u37

4.=IF(A37TRUE,"",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 . . . .

"Dave Peterson" wrote:

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 he

#1.

#2.

#3.

#4.



MarthaSue wrote:

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. . ..

"MarthaSue" wrote:

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.

"MarthaSue" wrote:

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?

"Dave Peterson" wrote:

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(a37true,"",375)

But I don't see how this fits in your Total amount????




MarthaSue wrote:

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(a37true,"",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

"Dave Peterson" wrote:

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(a1true,"",sum(b:b))
(don't put this formula in Column B--or adjust the range:
=if(a1true,"",sum(b2:B9999))



MarthaSue wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

  #16  
Old April 25th, 2005, 06:53 PM
Dave Peterson
external usenet poster
 
Posts: n/a
Default

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(a37true,"",375)

Then just use:
=sum(U:U)
or whatever rows you need
=sum(u2:U44)
????




MarthaSue wrote:

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

"Dave Peterson" wrote:

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(a2true,"",375)

Then to get the sum, you can just use:

=sum(u2:u36)

(36 is the last row with that formula.)


MarthaSue wrote:

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.

"Dave Peterson" wrote:

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.



MarthaSue wrote:

Hi Dave,

1.U37

2.u37

3. u37

4.=IF(A37TRUE,"",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 . . . .

"Dave Peterson" wrote:

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 he

#1.

#2.

#3.

#4.



MarthaSue wrote:

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. . ..

"MarthaSue" wrote:

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.

"MarthaSue" wrote:

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?

"Dave Peterson" wrote:

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(a37true,"",375)

But I don't see how this fits in your Total amount????




MarthaSue wrote:

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(a37true,"",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

"Dave Peterson" wrote:

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(a1true,"",sum(b:b))
(don't put this formula in Column B--or adjust the range:
=if(a1true,"",sum(b2:B9999))



MarthaSue wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #17  
Old April 27th, 2005, 09:06 PM
MarthaSue
external usenet poster
 
Posts: n/a
Default

Hey Dave, please ignore my last entry as I think I have figured it out . . .
Thanks for all your help!!!

"MarthaSue" wrote:

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. . . . .


"Dave Peterson" wrote:

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(a37true,"",375)

Then just use:
=sum(U:U)
or whatever rows you need
=sum(u2:U44)
????




MarthaSue wrote:

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

"Dave Peterson" wrote:

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(a2true,"",375)

Then to get the sum, you can just use:

=sum(u2:u36)

(36 is the last row with that formula.)


MarthaSue wrote:

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.

"Dave Peterson" wrote:

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.



MarthaSue wrote:

Hi Dave,

1.U37

2.u37

3. u37

4.=IF(A37TRUE,"",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 . . . .

"Dave Peterson" wrote:

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 he

#1.

#2.

#3.

#4.



MarthaSue wrote:

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. . ..

"MarthaSue" wrote:

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.

"MarthaSue" wrote:

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?

"Dave Peterson" wrote:

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(a37true,"",375)

But I don't see how this fits in your Total amount????




MarthaSue wrote:

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(a37true,"",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

"Dave Peterson" wrote:

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(a1true,"",sum(b:b))
(don't put this formula in Column B--or adjust the range:
=if(a1true,"",sum(b2:B9999))



MarthaSue wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

  #18  
Old April 27th, 2005, 09:49 PM
MarthaSue
external usenet poster
 
Posts: n/a
Default

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. . . . .


"Dave Peterson" wrote:

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(a37true,"",375)

Then just use:
=sum(U:U)
or whatever rows you need
=sum(u2:U44)
????




MarthaSue wrote:

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

"Dave Peterson" wrote:

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(a2true,"",375)

Then to get the sum, you can just use:

=sum(u2:u36)

(36 is the last row with that formula.)


MarthaSue wrote:

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.

"Dave Peterson" wrote:

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.



MarthaSue wrote:

Hi Dave,

1.U37

2.u37

3. u37

4.=IF(A37TRUE,"",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 . . . .

"Dave Peterson" wrote:

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 he

#1.

#2.

#3.

#4.



MarthaSue wrote:

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. . ..

"MarthaSue" wrote:

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.

"MarthaSue" wrote:

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?

"Dave Peterson" wrote:

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(a37true,"",375)

But I don't see how this fits in your Total amount????




MarthaSue wrote:

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(a37true,"",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

"Dave Peterson" wrote:

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(a1true,"",sum(b:b))
(don't put this formula in Column B--or adjust the range:
=if(a1true,"",sum(b2:B9999))



MarthaSue wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

  #19  
Old April 28th, 2005, 12:31 AM
Dave Peterson
external usenet poster
 
Posts: n/a
Default

Instead of this formula:
=if(a37=true,375,"")
use:
=if(a37=true,375,0)

but format that cell using that $ icon on the toolbar.

MarthaSue wrote:

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. . . . .

"Dave Peterson" wrote:

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(a37true,"",375)

Then just use:
=sum(U:U)
or whatever rows you need
=sum(u2:U44)
????




MarthaSue wrote:

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

"Dave Peterson" wrote:

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(a2true,"",375)

Then to get the sum, you can just use:

=sum(u2:u36)

(36 is the last row with that formula.)


MarthaSue wrote:

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.

"Dave Peterson" wrote:

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.



MarthaSue wrote:

Hi Dave,

1.U37

2.u37

3. u37

4.=IF(A37TRUE,"",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 . . . .

"Dave Peterson" wrote:

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 he

#1.

#2.

#3.

#4.



MarthaSue wrote:

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. . ..

"MarthaSue" wrote:

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.

"MarthaSue" wrote:

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?

"Dave Peterson" wrote:

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(a37true,"",375)

But I don't see how this fits in your Total amount????




MarthaSue wrote:

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(a37true,"",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

"Dave Peterson" wrote:

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(a1true,"",sum(b:b))
(don't put this formula in Column B--or adjust the range:
=if(a1true,"",sum(b2:B9999))



MarthaSue wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i make check mark boxes in mircosoft word//?? need some help .. General Discussion 1 January 12th, 2005 04:02 AM
Spell Check, on Word 2002; Other Languages June New Users 5 December 14th, 2004 03:31 PM
File Check Out System Emily Database Design 3 September 29th, 2004 05:25 PM
Adding a box and a check mark in Word 97 Rick New Users 2 June 18th, 2004 11:08 PM


All times are GMT +1. The time now is 08:41 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.