• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Drop Down list and supporting functionality

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
 
Hi:

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

Thanks
 
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
 

Attachments

  • UTILITIES P&L.xlsb
    478.5 KB · Views: 0
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
 
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
 
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
 

Attachments

  • UTILITIES P&L.xlsb
    478.4 KB · Views: 1
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
 
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
 
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
 
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
 

Attachments

  • UTILITIES PL xlsb (partly solved).xlsb
    479.4 KB · Views: 0
Hi:

I have fixed it, find the attached.

Thanks
 

Attachments

  • UTILITIES PL xlsb (partly solved).xlsb
    479.4 KB · Views: 2
Back
Top