Posts by vickyho1008

    Re: Clear & Replace Validation List Based On Value Chosen From Another

    yes, it works for allowing anyvalue to input to sub-strategy column once "Other" is chosen for Main Strategy, but the code refers everything to INDIRECT($D2), so, only EquityHedge's sub-strategy shows up everytime... it should be e.g. if D12 = 'Other" then INDIRECT($D12), not INDIRECT($D2)[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]it's OK, I have fixed it...change the line to be....

    xlBetween, Formula1:="=INDIRECT(" & Target.Cells.Address & ")"

    then it works, thanks[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]it's OK, I have fixed it...change the line to be....

    xlBetween, Formula1:="=INDIRECT(" & Target.Cells.Address & ")"

    then it works, thanks

    please see attached spreadsheet, in sheet 1, yellow columns are for restricted cells and Sheet 2 contains the lists for such columns' validation range.

    Sub-Strategy is dependent on the choice made from Main-Strategy, e.g. if Main-startegy = EquityHedge, then the choices for sub-strategy can only choose Long Biased, Low Beta & opportunistic. But I also have a "Other" category under Main Strategy, Sheet 2 cell G1, if this is chosen then I would like to remove the data validation in the sub-strategy column, (i.e. u can input anything for sub-strategy) how can I do this? thanks

    Re: Calculate Redemption Date

    I have sorted the semi-annually and annually code out, but really dunno what to do with weekly, as dunno how to detect the end of week (like every friday), does excel have that function?

    Re: Calculate Redemption Date

    your solution is exactly what I want, but what will the code be for weekly, semi-annually (occurs every 30/6 and 31/12) and annually (occurs every 31/12). thx

    the formula only works for monthly not quarterly. result at I22 should be 31/12/2007 not 30/11/2007..

    Re: Calculate Redemption Date

    G14 will be 17/08/2007, because it's weekly redemption and 3 days notice, if we put in redemption notice today,3 days later will be next week, hence exit on end of next week... also latest date to redeem this fund for end of next week's redemption will be on next Tuesday

    btw, I have written a formula, well partially
    =IF(MOD(MONTH(TODAY()+E5), 3)=0, EOMONTH(TODAY()+E5, 0),???? )
    but don't know how to complete it... but this is only for quarterly redemption, would be it better to write in macro so =redem(B3, C3, E3)

    the purpose of doing this is to find out if we redeem today when we will get our money back.

    if a fund has monthly/Quarterly redemption frequency then which means u can get your money back every month/quarter end. since the fund also imposes some advance notice date which means u have to inform then fund that u wish to redeem it 30/45/90 days in advance, if it's Monthly 30 days then u can't get your money back end of august , it has to be end of september cos u didn't meet the 30 days requrement

    same logic for quarterly redemption, if the fund imposes quarterly 45 days, then if u put in the redemption request today u can exit (get your money back) end of september, if it's 90 days notice, then exit date will be end of december

    quarterly redemption occurs 31/3, 30/6, 30/9, 31/12 every yr
    monthly redmeption occurs end of every month
    weekly, end of every week
    for daily one.. we can just count the advance notice, such as G7, =today()+C7

    pls see attached spreadsheet, columns A-E are given data, col B shows the redemption frequency, col C shows the advanced notice period and col E shows the our first investment date. I would like to display the result in one column showing the next redemption date..

    e.g. for Fund 1, it's redemption term is quarterly 30 days notice, which means.. next redemption period will be 30/09/2007 (get money back) and we have to give the redmeption notice latest on 31 Aug 2007 becos of 30 days advance notice to exit the fund

    e.g. for Fund 2, it's monthly 90 days notice, which means if we put in a redemption notice today then 90 days from now on will be 8 november, hence can get money back on 30 November 07.

    What formula shall I use to display
    - next available redemption date
    - exit date (get money back)

    many thanks

    Re: Auto Update All Formula With Latest Column

    what will be the VBA solution? so that I don't need to use name range to detect first and last column, because I have some other columns before the first month and also after the last month... therefore I think VBA is better?

    I have the month in row 3.. so like to detect the latest month in row 3..

    I have the monthly data stores column by column, e.g. Column B for Jan 07, Col C for Feb 07 etcetc, so every month I have a new column added to the end

    then I have some statistical analysis table which calculates such as annual return, standard deviation etcetc

    How can I make a button which I can click then it can update to replace the formular with the latest column?

    for example, =CORREL(B2:B10, G2:G10), col B contains Feb 07 data and G contains June 07, let's say I add another column for July 07 which will be column H and I would like by clicking the button then all formula with col G will be replaced by col H?

    many thx

    Re: Dynamic Pie Chart With No Zeros

    many thanks... but I need to run the macro everytime when a number is added or removed.. can u convert it like cleaning up the pie chart code? to list the code in Module 1 and run the sub...

    Re: Dynamic Pie Chart With No Zeros

    I have sorted it out myself, I name the chart as "PieChart", then replaced the line ActiveSheet.ChartObjects("Chart 2")Activate, with ActiveSheet.ChartObjects("PieChart")Activate

    it works...


    Re: Dynamic Pie Chart With No Zeros

    I prefer to have the 1st solution, many thanks, exactly what I wanted, but the problem is that I have many workbooks, one for each portfolio, thus, there will be one pie chart for each workbook. I have copied and pasted the Private Sub Worksheet_Change codes into each sheet's VB, but when it calls to fun the CleanUPActiveChartLabels it can only run sheet 1's chart, but others.. because there's a line "ActiveSheet.ChartObjects("Chart 2")Activate"