dataexchage

  • Hello Everone


    This Is Prashant. I Am Having An Excel Sheet , Sheet 1 Is Named As Sales And Sheet 2 Is A Master File For Curreny And Rate. In Sheet 1
    There Is A Drop Down List In Which Currencies Are There , I Want An Macro So That When I Select The Curreny , For Eg. When I Select Eur It Should Convert All The Sales Detail As Eur, When I Select Usd It Shoul Convert All The Details To Usd, Etc. The Values Of Inr, Usd, Gbp Are All Given In Sheet 2 , I Want To Link In Such A Way That Every If There Is Change In Currency Value Upwards Or Downwards , Automaticaly The Value In Sheet Also Should Change On Daily Basis.


    By Default All The Above Figure Are In Gbp.


    Please Suggest An Excel Formula Or Macro. The Attachment Is A Very Small One Where Only Few Data Is Available , I Want To Run The Macro Or Formula Through No Of Sheets.

  • Re: dataexchage


    Hi Prashant,


    I have assumed that you receive the Sales and Other Services values in GBP and that the table in MASTER FILE would calculate values in currencies other than GBP by multiplying the base value by the GBP value in the table and dividing by the selected currency value in the table.


    Either a formula approach or a macro will need separate areas to hold the original values and the calculation results, so I have created a table to hold Month, Sales and Other Services. I have used range names in the formulas, so I have made the ranges dynamic, so that the range covered by them expands as you add more data.


    I hope the attached helps.


    Regards,
    Batman.

  • Re: dataexchage


    Thank You For Ur Help My Firend.


    However This Is Only The Preliminary Stage ,that U Taught Me, But Dont Want It In This Way.whati Want Is When Ever I Enter Figure In Column F5:i5 It Should Pick It As Gbp, And Whenever I Change The Select Currency As Eur Or Usd Whatever In That Case It Should Get Converted In That Currency.now In This Case It Is Picking Figure From B2:b5 That Is If I Delete Figure I Will Not Get Any Value. So I Dont Want A1:c5,i Dont Want That ,the Formula Should Pick Value From There.


    Please See The Attached Worksheet For More Information.


    Thanks For Ur Help

  • Re: dataexchage


    Prashant,


    If I understand you correctly, you will enter the data in GBP in cells F5:I6 and a separate table, which you currently show in cells F17:I18, will show the same values converted to the selected currency.


    I have therefore deleted the new table in columns A:C and changed the formulas accordingly. As you have your own currency selection field in cell H34, you might need to change the formula from


    =ROUND((F5/VLOOKUP($F$1,Conversion,2,FALSE)),2)


    to


    =ROUND((F5/VLOOKUP($H$34,Conversion,2,FALSE)),2)


    but at the moment your drop-down doesn't have any values in it. Please see the attached.


    Hope this helps.


    Regards,
    Batman.

  • Re: dataexchage


    Prashant,


    Does the example in Sheet3 work? In the top section the region enters their values and selects the currency they enter in from the drop-down in A3. The second section is then used to convert the values entered to whichever currency you select in A9.


    Hope this helps.


    Regards,
    Batman.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!