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 where i had stored the above file is not working, please try either of these. thanks.

http://s000.tinyupload.com/?file_id=...01267199384356

or

http://www.filedropper.com/utilitiespl-demo

I manage to partly solve my questions . But i though i would simply this further. I would still need help from someone to tackle through the use of Combo box and choose function. Simplified file now attached


http://s000.tinyupload.com/?file_id=...80475819715826

Any help greatly appreciated

Drop Down list and supporting functionality
 

Hi,

Am Wed, 2 Sep 2015 10:19:22 +0100 schrieb Excel Dumbo:

http://s000.tinyupload.com/?file_id=...80475819715826

your problem is not really clear.
If you choose "Monthly Actuals" and "Jan 15" which range should be
calculated?
Can you post some examples?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi CLaus,

Example- When you select "Monthly Actual" and when you select "Feb-15"- it should give me the values for Feb 15 only, and when I select "Mar-15", it should give me values of 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

Hope I am clear

Drop Down list and supporting functionality
 

Hi,

Am Wed, 2 Sep 2015 22:44:54 +0100 schrieb Excel Dumbo:

Example- When you select "Monthly Actual" and when you select "Feb-15"-
it should give me the values for Feb 15 only, and when I select
"Mar-15", it should give me values of 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

you don't need formulas in BU1 and BV1. You can calculate at once in
AB5 with:
=IF($AB$1="YTD Actuals",SUMPRODUCT(--(MONTH($P$2:$AA$2)<=$BT$2),P5:AA5),SUMPRODUCT(--(MONTH($P$2:$AA$2)=$BT$2),P5:AA5))
and copy the formula down.

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Drop Down list and supporting functionality
 

Hi,

Am Thu, 3 Sep 2015 08:02:03 +0200 schrieb Claus Busch:

AB5 with:
=IF($AB$1="YTD Actuals",SUMPRODUCT(--(MONTH($P$2:$AA$2)<=$BT$2),P5:AA5),SUMPRODUCT(--(MONTH($P$2:$AA$2)=$BT$2),P5:AA5))

better:
=IF($AB$1="YTD Actuals",SUMPRODUCT(--(MONTH($P$2:$AA$2)<=$BT$2),P5:AA5),INDEX(P5:AA5,,$ BT$2))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks heaps CLaus. THis is exactly what i wanted. Thanks heaps