The drop down boxes all have links to this spreasheet wherer the data is housed.
'Statistics Month'!$A$2:$A$49 A2-A49 has months and years.... some have Year Total eg. Year 2012
Each drop down then has the data set with the index being D5
=INDEX('Statistics Month'!$I$2:$I$49,$D$5) (B to K)
Cell Link $M$2 - All the cell inks are differnet on different spreadsheets
I have for each drop down box what is listed below as an example.
ListIndex is Dec-12 therefore next month will be Jan-12 I will want to run a macro to change all the drop down boxes to Jan-13 and also to Year 2013. I can run this macro every 1st of the month to change all the drop down boxes. What I have at present is .listindex which I would have to change 24 to 25 for each section of the macro. I could do this manually by the time I have updated the macro for each list index. I want to be able to change the value if this is the correct term from 24 being Dec-12 to asking the dropdown box to go to Dec-12 or Jan-12 which would be Now() Format mmm-yy or year 2013 which would be yyyy.
Two examples are listed below. I have a total of approx 16 dropdown boxes maninly mmm-yy
Sheets("Dashboard").DropDowns("Drop Down 3213").ListIndex = 24
Sheets("Dashboard2").DropDowns("Drop Down 3214").ListIndex = 25
Also the crop downn box controls the data selected on each spreadheet which is a part edited copy of the data I store on the data sheet.
e.g. =INDEX('Statistics Month'!$B$2:$B$79,$D$5)
All the data shown for each month also has a chart linked to it. Hence when I run my dropdown macro it will show the correct month (this month) the data will also show for this month and the chart will show for this month. all the data collect from the Stats Data sheets which is hidden and updated each day.
I have searched for the answer and tried numerous ways and I can not find how to change .listindex to the display of Oct-12, Dec-12 or Jan-13
What Can I use instead of .listindex to reference the display of the index and also do I need to use : ' (Format(Now, "mmm-yy"))
What A final puxxle for me to have...Many thanks if you can solve it.
You have already helped in shorting my prgrma at present for the months listed above.