Ok this is a little hard to explain. I have a sheet with calculations on
it, and three radio buttons to click in order to change the values in
the cells (and result in a different answer "Selling Price"). Problem
is, on another sheet I have a list of "Products", with a row for each
type of product. For example:

First sheet is for Product A, made of wood, metal and plastic (radio
buttons 1, 2 and 3). Second sheet has a list of prices for Product A
wood, Product A metal and Product A plastic. Then there's a third sheet
for Product B wood, metal, plastic and so on..

I can't find a way to link the 3 different selling prices in the first
sheet to the list in the second sheet properly, as they end up in the
same cell and are only changed by the radio buttons. I've sort of been
able to separate them out, except the issue is that if you click on
option 1, then change a variable, it doesn't update the result in the
second sheet until you click on option 1 again.

I've spent forever on this and can't find a solution.. this is the last
resort!

Anyone have any ideas? Hopefully I've explained it clearly enough..

Thanks so much.


--
sneakyzeal
------------------------------------------------------------------------
sneakyzeal's Profile: http://www.excelforum.com/member.php...o&userid=26978
View this thread: http://www.excelforum.com/showthread...hreadid=401901

Can't conceptualise this sorry - maybe bung up a cut-down version of th
spreadsheet?

It sounds like there's a small error somewhere to me.

First try an event sub though - make the sheet recalculate afte
changing the radio buttons

--
gearo
-----------------------------------------------------------------------
gearoi's Profile: http://www.excelforum.com/member.php...fo&userid=2657
View this thread: http://www.excelforum.com/showthread.php?threadid=40190

Hi Gearoi

I've done a very brief dummy file up with a couple of notes.. hopefully
you'll understand what I mean. It's a bit of a messed up problem due to
bad design!

Any ideas are hugely appreciated.

Kind regards


+-------------------------------------------------------------------+
|Filename: Book1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3781 |
+-------------------------------------------------------------------+

--
sneakyzeal
------------------------------------------------------------------------
sneakyzeal's Profile: http://www.excelforum.com/member.php...o&userid=26978
View this thread: http://www.excelforum.com/showthread...hreadid=401901

Your entries in H10:H12 are entered by the macro's associated with the
option buttons. If you change anything on the sheet the macro doesn't fire.
Really an easier way would be to have each option button when clicked, throw
a 1, 2 or 3 (or some meaningful number, cost or whatever) into one cell on
the sheet, then have formulas in H10:H12 calculate everything for you based
on profit margin, wood type etc. Otherwise, you will have to create
variables for everything and then use a change event to do all this for you.

Jon

"sneakyzeal" wrote
in message ...

Ok this is a little hard to explain. I have a sheet with calculations on
it, and three radio buttons to click in order to change the values in
the cells (and result in a different answer "Selling Price"). Problem
is, on another sheet I have a list of "Products", with a row for each
type of product. For example:

First sheet is for Product A, made of wood, metal and plastic (radio
buttons 1, 2 and 3). Second sheet has a list of prices for Product A
wood, Product A metal and Product A plastic. Then there's a third sheet
for Product B wood, metal, plastic and so on..

I can't find a way to link the 3 different selling prices in the first
sheet to the list in the second sheet properly, as they end up in the
same cell and are only changed by the radio buttons. I've sort of been
able to separate them out, except the issue is that if you click on
option 1, then change a variable, it doesn't update the result in the
second sheet until you click on option 1 again.

I've spent forever on this and can't find a solution.. this is the last
resort!

Anyone have any ideas? Hopefully I've explained it clearly enough..

Thanks so much.


--
sneakyzeal
------------------------------------------------------------------------
sneakyzeal's Profile:
http://www.excelforum.com/member.php...o&userid=26978
View this thread: http://www.excelforum.com/showthread...hreadid=401901

Hmm.. I'm not entirely sure what you mean Jon, it sounds like a lot o
redoing what I've already done. I've found an alternative which isn'
quite perfect, but hopefully my lecturers won't catch me out ;)

Thanks anyway though for your help

--
sneakyzea
-----------------------------------------------------------------------
sneakyzeal's Profile: http://www.excelforum.com/member.php...fo&userid=2697
View this thread: http://www.excelforum.com/showthread.php?threadid=40190

Actually I missed the H28 entry. This is exactly what I was talking about in
the last post. So, what you need to do is not paste the Value of B6 into the
h11:h13 which is what you are doing, but paste the formula itself so that it
evaluates

for your Macro BCBeechTotal use:
Worksheets("Bookcase - BC940").Range("H11").Formula = "=B6"

Follow the same idea for the other 2 macros

This seems kind of redundant though, Why not just have the formulas already
in those cells? (that is what I meant in my previous post)

How are you putting the 1,2, and 3 into H28? I can't seem to find it.

Jon


"sneakyzeal" wrote
in message ...

Hmm.. I'm not entirely sure what you mean Jon, it sounds like a lot of
redoing what I've already done. I've found an alternative which isn't
quite perfect, but hopefully my lecturers won't catch me out ;)

Thanks anyway though for your help.


--
sneakyzeal
------------------------------------------------------------------------
sneakyzeal's Profile:
http://www.excelforum.com/member.php...o&userid=26978
View this thread: http://www.excelforum.com/showthread...hreadid=401901

I tried what you suggested.. if you paste the formula itself and not the
value, the numbers in all three cells become the same each time you
click a different option (when I want the different result from
clicking each buttons showing in H11,12,13).

H28 is a reference point for those option buttons (Format Control
Control), so 1 is returned if the first one is selected, 2 for the
second.. etc. Other cells in the sheet (which I didn't include in that
dummy file) use it in an If statement to decide what values should be
used in calculations that reach the value in B6. For example: A column
has multiple rows with "if beech (H28=1) is selected then cell x should
be $5, if rimu (H28=3), x should be $7" and so on. Then B1 links to the
total of that column, which in turn affects B6.

Hope that makes sense!


--
sneakyzeal
------------------------------------------------------------------------
sneakyzeal's Profile: http://www.excelforum.com/member.php...o&userid=26978
View this thread: http://www.excelforum.com/showthread...hreadid=401901

I think you will then need 3 columns of data to calculate for each wood
type, profit margin etc. The way you have it now all the calculations are
done with the result in B6. Every time that button a button is clicked that
column varies.

Do you really need the total for each type in a separate row or could you
have one cell that changes the wood type depending on the button chosen,
with the appropriate price in the cell to the right of it?

Jon
"sneakyzeal" wrote
in message ...

I tried what you suggested.. if you paste the formula itself and not the
value, the numbers in all three cells become the same each time you
click a different option (when I want the different result from
clicking each buttons showing in H11,12,13).

H28 is a reference point for those option buttons (Format Control
Control), so 1 is returned if the first one is selected, 2 for the
second.. etc. Other cells in the sheet (which I didn't include in that
dummy file) use it in an If statement to decide what values should be
used in calculations that reach the value in B6. For example: A column
has multiple rows with "if beech (H28=1) is selected then cell x should
be $5, if rimu (H28=3), x should be $7" and so on. Then B1 links to the
total of that column, which in turn affects B6.

Hope that makes sense!


--
sneakyzeal
------------------------------------------------------------------------
sneakyzeal's Profile:
http://www.excelforum.com/member.php...o&userid=26978
View this thread: http://www.excelforum.com/showthread...hreadid=401901