using a checkbox for a conditional function

[Brac]

Not sure if this is the best way, but what I want to do is have a pricing
form for quotations and output the pricing information to the appropriate
cells with either a US or CDN pricing result.

I haev all the information correct in my workbook, but I find when I want to
change the result, I edit one formula and then drag the rest to reference the
different currency.

What I want to do is put a couple of check boxes or just one if need be in
the first sheet so that if it's a foreign price, you check the box. The
following sheet will then say "=IF(QTY cell is blank, put nothing,put down
the qty*price)" But now I want to add more conditions so that the part that
says "put down the price" is based on a checkbox or option button and tells
the function (if that box is selected, use the price in CELLA, otherwise use
the price from CELLB).

I hope this is clear and any help obviously would be awesome. Thanks in
advance!

[Biff]

Hi!

You could use just a single checkbox that means use the "foreign price" and
the default would be USD.

When you use a checkbox (from the FORMS toolbar) there is an option to link
the checkbox to a cell. When you do that the cell returns the "status" of
the checkbox, when it's checked the status is TRUE and when it's not checked
the status is FALSE.

Then you can write your formula like this:

=IF(QTY cell is blank,leave blank,IF(checkbox linked cell is TRUE,use this
price,use default price))

Biff

"Brac" <[email protected]> wrote in message
news:[email protected]...
> Not sure if this is the best way, but what I want to do is have a pricing
> form for quotations and output the pricing information to the appropriate
> cells with either a US or CDN pricing result.
>
> I haev all the information correct in my workbook, but I find when I want
> to
> change the result, I edit one formula and then drag the rest to reference
> the
> different currency.
>
> What I want to do is put a couple of check boxes or just one if need be in
> the first sheet so that if it's a foreign price, you check the box. The
> following sheet will then say "=IF(QTY cell is blank, put nothing,put down
> the qty*price)" But now I want to add more conditions so that the part
> that
> says "put down the price" is based on a checkbox or option button and
> tells
> the function (if that box is selected, use the price in CELLA, otherwise
> use
> the price from CELLB).
>
> I hope this is clear and any help obviously would be awesome. Thanks in
> advance!

[Wolmot]

This is the exact issue I was looking for but I can't change the link option
on the properties table for the checkbox. Is ther another way to Link the
control to the cell?

"Brac" wrote:

> Not sure if this is the best way, but what I want to do is have a pricing
> form for quotations and output the pricing information to the appropriate
> cells with either a US or CDN pricing result.
>
> I haev all the information correct in my workbook, but I find when I want to
> change the result, I edit one formula and then drag the rest to reference the
> different currency.
>
> What I want to do is put a couple of check boxes or just one if need be in
> the first sheet so that if it's a foreign price, you check the box. The
> following sheet will then say "=IF(QTY cell is blank, put nothing,put down
> the qty*price)" But now I want to add more conditions so that the part that
> says "put down the price" is based on a checkbox or option button and tells
> the function (if that box is selected, use the price in CELLA, otherwise use
> the price from CELLB).
>
> I hope this is clear and any help obviously would be awesome. Thanks in
> advance!

[SteveG]

If you are using a Form control, then you should be able to right mouse click on the checkbox, select Format control and when the format box appears, select the Control tab. There is a section there to link the form to a cell.

If you are using an ActiveX control, you need to be in Design Mode to change the link. Go to View/Toolbars/Visual Basic and select it. There if you put your mouse pointer over each of the toolbar options, one will be Design Mode. Select this. When you right mouse click on your control, the menu should show Properties, View Code etc... Go to properties and in the Linked Cell option, change the cell reference manually. You can not select the cell using your mouse pointer. When you are done making your edits, select the Design Mode icon again to make the control active.


HTH

Steve

[Wolmot]

That did it, Thanks. I was trying to put a True False in instead of the cell
location.

"SteveG" wrote:

>
> If you are using a Form control, then you should be able to right mouse
> click on the checkbox, select Format control and when the format box
> appears, select the Control tab. There is a section there to link the
> form to a cell.
>
> If you are using an ActiveX control, you need to be in Design Mode to
> change the link. Go to View/Toolbars/Visual Basic and select it.
> There if you put your mouse pointer over each of the toolbar options,
> one will be Design Mode. Select this. When you right mouse click on
> your control, the menu should show Properties, View Code etc... Go to
> properties and in the Linked Cell option, change the cell reference
> manually. You can not select the cell using your mouse pointer. When
> you are done making your edits, select the Design Mode icon again to
> make the control active.
>
>
> HTH
>
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=466935
>
>