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

I have a spreadsheet which keeps all clients' records generated from a software, see attached file for example. One company may have more than 1 contacts, how can I auto-hide row 3, 6 & 7. Because when I print the file, I only want to see one row per company. thanks

Re: Double Sumif

excellent, thanks so much

see attached spreadsheet, how could I set a formular to look for e.g. the weighting of "Equity LS" strategy with "High" beta etc.. to fill in the table on the right.. thx

Re: Calculate Redemption Date

it works, perfect, thanks for all of you so much!

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

GeorgS
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

ByTheCringe2
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

Re: Calculate Redemption Date

OK, I have manually entered the expected result in the new column as attached

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

what is the VBA way?

Re: Dynamic Pie Chart With No Zeros

that checkbox is checked by default, see attached sample file. Becos there are formulas embedded in the cells, here I copied and pasted as value from my file.. empty cells indicate zero values. How can I remove the blank rows in the bar chart?
thanks

Re: Dynamic Pie Chart With No Zeros

by the way, is this clean up code only work on pie chart? as I have tried on bar chart, then it doesn't work..I mean zero value doesn't hide

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...

thanks

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"

Please see the attached spreadsheet, I would like to create a dynamic pie chart shows the monthly strategy breakdown (one month at a time), but not showing the zero value and also a drop down box to be able to choose which month's strategy breakdown to display on the chart.

thanks