Set different prices with checkbox

[zplugger]

Is there a way to set different prices for different people using same list? The example
explain's better,maybe this can't be done.Any ideas on how to do this would be appreciated.


Thanks Z

[humdingaling]

if you only want one option available you should use option button not a check box

since you were using both forms and activex i have rejigged your file to be forms only

if your going to use forms you dont need xlsm
if your going to use Activex then xlsm is required and more coding also required

let me know if you want to go the other way with ActiveX
this can be done..but more coding would be required

also while i was there i redid your DV so you dont have that long list of empty spaces
i put the formula for the named range on products sheet as well

[zplugger]

Very nice work humdingaling,my main book is all active X buttons so if possible can we change the example to Active X when
you get time. Trying to understand where the formula's are for the lookup,still learning excel and like to know how different
things work.I like the option button idea better then checkboxes,I should be able to set a certain button on workbook open for daily use. Again
Thank you for your time on this,the forum has so many good people for helping others.

Z

[zplugger]

Trying to understand this code, I think its a array,what do I do if I add another name and prices ?

[humdingaling]

not sure which formula
if its this one

Please Login or Register  to view this content.

the first {} is for the number that option button generates
the second {} is the corresponding name
as long as you have the same amount in first and second it should work

the other formula

Please Login or Register  to view this content.

its a index/match combination
http://www.randomwok.com/excel/how-to-use-index-match/
this is how i learnt how to use index-match

now after all that....if activeX solution i wouldn't use the lookup formula
ill revert later with the activex version

[humdingaling]

solution using ActiveX instead

i put in a cmd button to trigger...because well i couldn't think of a way to trigger it without putting code into every single option button

i updated the index match formula to be 2 way match

[zplugger]

Thanks
Still want to learn this process too, add another name but can't get it to work. Must not be adding the array right?. Here is the got I added for riley?

Please Login or Register  to view this content.

[humdingaling]

that formula works when i plug that formula into I14

[zplugger]

He is what I have, just not working,maybe you can see what I did wrong. Thanks

[humdingaling]

the index array needs to be made bigger to encompass the newly added columns

Please Login or Register  to view this content.

Products A1:G125 should be enough for your 6 options
increase the column to increase more options

someone wrote one something about 2d index/match not too long ago on this forum
http://www.excelforum.com/tips-and-t...al-lookup.html

have a look check it out

[zplugger]

I'm little slow but after couple minutes I got it. Thank you so much for the examples,will study to learn them better. Have great night,Thanks

[humdingaling]

sure thing not a problem

Cheers
Hum

[zplugger]

Ran into a Big problem adapting humdingaling code to my workbook. All his code works perfect for what I asked,forgot in my workbook I have a price override cell.
Here is my two codes,if needed I can post updated workbook.Can I get new code to have the override in L19?

This was my old code

Please Login or Register  to view this content.

This is humdingaling new lookup

Please Login or Register  to view this content.

Thanks for any suggestions

[zplugger]

Not sure if best way but it works.

Please Login or Register  to view this content.

[humdingaling]

thats probably how i would do it as well
if it works in that form then i would just take it

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[zplugger]

Thanks humdingaling
Just can't find way to have workbook open to a default optionbutton.Can set workbook open to set optionbutton1 to true but
that does not run update code.
Z

[humdingaling]

hmmm you are asking for two things at are actually linked

how are your option buttons updating at the moment?
are you using a command button?