Drop Down list and supporting functionality

[Ecel Dumbo]

HI Dear Friends,

I stumbled upon a file I was working on when I had to create a Drop Down list with additional set of functionalities. I have attached my sample work with what I need, any help would be greatly appreciated

I tried to strip down as much information as possible to upload on the website portal but not working hence posted it on a link

http://speedy.sh/4zQfm/UTILITIES-P-L-DEMO.xlsx

I have listed out what i would need in the spreadhseet

• Drop Down list in Cell AB 1- To show YTD Actuals, FY Actuals, Q1 Actuals,Monthly Actuals
YTD Actuals means to pull values from Jan to Dec, this is what the current formula is doing
FY Actuals means to pull values from Jul 14 to June 15
Q1 Actuals means to pull values from Jan-15 to Mar-15
Monthly actuals means to pull values for each month as shown in Cell AB2
The action o f all the above items should be seen in Column AB only
• How to make drop list button visible without clicking on the cell
• Any change in formula required as current formula slowing down sheet. This is a demo sheet and I have more similar categories to add like Water, electrcity etc
I have used name list and this still seems to slow down my data as I work on my original file

 

[Nebu]

Hi:

The link you have given is not allowing me download the file could you please upload the file directly.

Thanks

 

[Ecel Dumbo]

Hi Nebu, SInce the file is just over 2 mb, cannot upload here, so could you please try this link
http://www.filedropper.com/utilitiespl-demo

 

[Ecel Dumbo]

Or please try this link
http://s000.tinyupload.com/?file_id=26208801267199384356

 

[Nebu]

Hi:

Find the attached. I have done it for the year 2015, I had to delete the formulas in column O to get the FY total for 2014-15 , Note the second filter will come in to play only if you select Monthly actuals. This is the first pass you will have to think about including other quarter totals. Since you had mentioned it only Q1 i have just done it for Q1.

Thanks

 

[Ecel Dumbo]

Thank you very much Nebu for the time you had put in. I was going through your working. I would prefer the two drop down list to function together.

Example- When you select "FY Actual" and when you select "Feb-15"- it should give me the values from Jul 14 till Feb 15, and when I select "Mar-15", it should give me values from Jul 14 till Mar-15.

When I select "YTD Actual" and select "Feb-15", it should give me values from Jan-15 to Feb-15 and when i select "Mar-15", it should give me values from Jan-15 to Mar-15

 

[Ecel Dumbo]

Also I will need to keep the formula as it is in Column "O", as I will add the same formula you had placed in column AB on to column O, when i add old years data-2011, 2012 etc

 

[Ecel Dumbo]

Any help please

 

[Ecel Dumbo]

Any help please

 

[Nebu]

Hi:

Find the attached, I have made necessary changes.

BTW, I live in Australia, because of timing difference I may not be able to respond to your posts immediately. You will have to be a bit more patient

Thanks

 

[Ecel Dumbo]

Sorry Nebu. Didnt mean to push you. I am going through the file now. This is exactly what i wanted. Thanks a lot. I am going through the file to understand how you did it. I am in Australia too. G'day

 

[Ecel Dumbo]

HI Nebu, HOw do make the date in the combo drop box appear in the same format as mmm-yy. I tried doing this on my own as the drop down list is showing me dates in their "4353" format

 

[Ecel Dumbo]

HI Nebu, in addition to my above request, could you kindly tell me how have you linked choose function to the combo box. I am trying to apply your workings to my larger file thanks

 

[Ecel Dumbo]

Hi Nebu. I figured solutions for all my questions above.
I think I have got confused myself going in to complicated areas. Hence I have simplified my combo box list, however i need to update my CHOOSE function and other related functions as mentioned in the attached spreadsheet. When you get time, please advise

 

[Nebu]

Hi:

I have fixed it, find the attached.

Thanks

 

[Ecel Dumbo]

Thanks heaps Nebu.Exactly what i wanted