1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to extract data from a sheet as soon as i click the combo box?

Discussion in 'Ask an Excel Question' started by SONJOE JOSEPH, May 15, 2017.

  1. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    158
    Dear All Excel Experts,

    In the working file there are two sheets named "LIVE PRICE" and "BSM". In the "Live Price" sheet as soon as i click the combo box and select any of the items say NIFTY17MAY9200PE it will have to extract the Delta, Gamma, Theta,Vega & Rho values of this particular NIFTY17MAY9200PE from the "BSM" sheet to the "Live Price" sheet were i have entered 4 columns named Delta, Gamma, Theta, Vega & Rho. In this particular case the values are: Delta =-0.07, Gamma = 0.001, Theta = -1.09, Vega = 0.58 & Rho = -0.15.

    Please help me how this can be achived. The working file is attached along with this message.

    Regards,

    Sonjoe Joseph

    Attached Files:

  2. PCosta87

    PCosta87 Well-Known Member

    Messages:
    808
    Hi,

    This should be simple enough, although a little more info is required.
    From what is visible in the file attached, it seems you need to look out for two things when searching the values: in this case 9200 and PE.
    My question then:
    1) What should be returned if you selected "NIFTY17MAY9650CE"

    I am assuming that PE should return DeltaPO and CE DeltaCO and so on... but need confirmation on that.
    Plus, when the number part doesn't have an exact match in "BSM", should it return the highest value smaller than the lookup value or the smallest value higher than the lookup value?

    Thanks
  3. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    158
    Dear Costa,

    Thank You for showing me the error, So now i have updated the file and do kindly look into it.

    File attached

    Regards,

    Sonjoe Joseph

    Attached Files:

  4. PCosta87

    PCosta87 Well-Known Member

    Messages:
    808
    I see,

    Something like this perhaps (refer to attachment)

    Hope this helps.

    Attached Files:

  5. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    158
    Wonderful PCosta i checked its working fine. Let me do myself incase any doubts i will get back to you.

    Regards,

    Sonjoe Joseph
  6. PCosta87

    PCosta87 Well-Known Member

    Messages:
    808
    You are welcome ;)
  7. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    158
    Dear Friend Pcosta,

    Even though the formula is working perfectly fine there is a slight bug when copying the formula down. Just find the screen shot were u will find the error from AD47 to AH50. On the up ur not seeing the error since i was just half way mark doing the working based on your formula. Then finally Bosco has advised to me to point the error while copying the formula.

    Hope u will get back to me with the updated formula.

    Regards,


    Sonjoe Joseph.

    Attached Files:

  8. PCosta87

    PCosta87 Well-Known Member

    Messages:
    808
    Hi,

    Can you upload the file with the error so I can take a look?
    Testing here, in the sample provided, it seems to be working:
    1.JPG

    EDIT: Most likely you made some mistake when adapting the formula (probably something to do with absolute/relative references).
  9. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    158
    Dear PCosta,

    I did the necessary corrections in the formula and now its working fine. Thank You for ur prompt support.

    Regards,

    Sonjoe Joseph.
  10. PCosta87

    PCosta87 Well-Known Member

    Messages:
    808
    I'm glad it is working now :)
  11. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    158
    Dear PCosta,

    I have another doubt. In the attached excel file in "Sheet 1" you have the live prices were it will be updated from the trading terminal. In "Sheet 2" in the symbol list i have selected the selected strike prices and you will find the BR and AR marked in yellow. As soon as i click any strike price say 9100CE the Bid Rate (BR) and (Ask Rate) has to come in the respective rows and here the updation of the price should not take place when i select any strike price from the symbol list.

    Can this be done Pcosta. Do help me out in solving this problem.

    Thanking You,

    Regards,

    Sonjoe Joseph.
  12. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    158
    Sorry forgot to attach the file

    Attached Files:

    • PC.xlsx
      File size:
      27.2 KB
      Views:
      2
  13. PCosta87

    PCosta87 Well-Known Member

    Messages:
    808
    Hi,

    There must be something wrong with the file you uploaded... this is all I get:
    1.JPG

    Is it supposed to be like this?
  14. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    158
    Dear Pcosta,

    I will attach another file once I reach home.

    Regards,

    Sonjoe Joseph
  15. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    158
    Dear PCosta,

    I'm just attaching another file this is just a copy and paste data from my trading terminal. The Sheet1 prices will change as per the trading terminal. In Sheet 2 i have added 3 combo boxes. As soon as i do the selection in the combo boxes the Bid Rate (BR) and Ask Rate (AR) for the respective strike prices has to come in the related fields. Once the selection is done the price updation should not take place in the respective fields even though the live prices will get updated in sheet 1 from the trading terminal.

    Please help me to solve this query.

    Regards,

    Sonjoe Joseph.

    Attached Files:

    • PC.xlsx
      File size:
      15.1 KB
      Views:
      2
  16. PCosta87

    PCosta87 Well-Known Member

    Messages:
    808
    Hi,

    I had to make a couple of changes in order to make it work.
    Once you select a value from on of the drop-down menus, the respective fields highlighted in yellow will have their formulas converted to values. Any future update will no longer affect these.

    I'm not sure this is exactly what you want but give it a go a let me know.

    Attached Files:

    • PC.xlsm
      File size:
      20.1 KB
      Views:
      7
    Thomas Kuriakose likes this.
  17. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    158
    Thank you Pcosta for ur prompt response. I will check it and will further update any changes to be made.

    Regards,

    Sonjoe Joseph
  18. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    158
    Dear PCosta,

    Your formula seems to be working fine without a combo box. The main intention of doing all this things is to reduce typing and at the same time i would like to take note of the effect of pricing. So taking this point forward on "Sheet 2" i have added a combo box and the respective cells from C8 to C11 have been lined to the respective cells in B8 to B11. You can see the no's which are marked in yellow. Can this no's can be converted into text. Another idea is when i click the combo box the same text should come in C8 to C11. Once this is done...then when i apply your formula it will come correct.

    Just waiting your feedback on this issue and working file attached along with this message.

    Regards,

    Sonjoe Joseph

    Attached Files:

    • PC.xlsm
      File size:
      21.9 KB
      Views:
      2
  19. PCosta87

    PCosta87 Well-Known Member

    Messages:
    808
    Hi,

    If you look closely you will notice that, although I didn't use a Combo Box "per se", there is validation in B3:B5 that fetch the data from the source in "Sheet1".
    This means you can select the entry just as you do with your Combo Box, you don't need to type anything here.

    I did this because you needed the formula to be converted to values so it didn't update if the source values were to change in Sheet1. For this I wrote a Worksheet_Change event that does that when you select something from the drop-down menus. The thing is that this will only work with the validation as the event only occurs when you change the cells manually, which doesn't happen when you use the Combo Box.

    The formula isn't the problem here... the problem is making it so it doesn't update after pulling the values from Sheet1.
    Thomas Kuriakose likes this.
  20. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    158
    Dear PCosta,

    Just tell me i want to change the strike price of the B3 cell to NIFTY17MAY9300PE. I'm not able to find any Combo Box in ur file attached. Just see the screen shot. If there was a combo box i would have just clicked on it. I have seen the vba code given in the worksheet change. Will this code affect my other sheets that too i want to know?

    Regards,

    Sonjoe Joseph.

    Attached Files:

    • scr.png
      scr.png
      File size:
      252.1 KB
      Views:
      3
  21. PCosta87

    PCosta87 Well-Known Member

    Messages:
    808
    Wait, that is odd... you should have this:
    1.gif

    Not sure what is going on :confused:
    Please double check if Data Validation isn't working on your end (as above).
    Thomas Kuriakose likes this.
  22. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    158
    Dear Pcosta,

    Well i tried to do as shown in the pictorial presentation but it seems not working from my end. Tell me what to do to enable the combo box.

    Regards,

    Sonjoe Joseph.
  23. PCosta87

    PCosta87 Well-Known Member

    Messages:
    808
    Hi,

    What version of Excel are you using?
    I never came across a situation like this, and I can't seem to find a similar problem online, much less a fix for it o_O

    At this point I'm just guessing but perhaps there is something wrong with your installation or some settings I'm not aware of.

    Is it possible for you to try the file in a different machine and see if it is working?
  24. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    158
    I'm using MS Office Excel 2007. I'm using a corrupted version of windows and office. Hope there is no fix for this right. Do u have teamviewer so that u can directly check.

    Regards,

    Sonjoe Joseph.
  25. PCosta87

    PCosta87 Well-Known Member

    Messages:
    808
    Please check your inbox.
    Thanks

Share This Page