Drop down that gives store numbers, Can we get store names in Alphabetical order ?

[melvin]

Gday All,

Happy new Year. I use this report to drop on Area managers which gives me the list of stores that fall under the AM's.

I have been asked if I can get the store names in an alphabetical order ? Would anyone be able to assist me on this.

Thanks guys.

Melv.

 

[David Evans]

It appears that you are using a sledgehammer to crack a walnut. That is why it won't be really straightforward to sort your stores alphabetically.
I think you'd be best advised to simplify all of your data into one Excel Table (yes use that Table feature - you will learn to love Tables in Excel) and then use Pivot Tables to produce the required results. The current model has a lot of redundancy and it's a touch convoluted. The file you uploaded is also referencing an external file on your computer, but I suspect that is the name of the file prior to you sanitizing it for posting - always a good idea!

The Table you need is simply the Consolidated sheet with a couple of extra columns for Week No, Sales Budget/Wage Budget and likewise for Actual amounts. The variance and % are simply calculated fields.

Currently, I think you're using the horizontal axis to layout the budget weeks which precludes you from using Excel's built-in Pivot Table capabilities to their maximum effort. I think you've emptied that area for confidentiality.

The Area Manager view would be very simply created from a basic Pivot and filtering in/out the Week nos you want to show - you won't have to perform the formula gymnastics and your output will be cleaner and better formatted as Pivot Tables can handle everything you're attempting and more -

 

[melvin]

Thanks David,

I have displayed only 1 week as an example, As I have this file at a quarter view , I wouldn't put 13 weeks in horizontal axis. I have only put budget and actual, however I have few other scenarios in my columns.

I will take your suggestion on board to using pivots, but with the amount of data I am using for this report, that would be another mess. I am still keen on knowing if anyone thinks if it is possible to get the store names for the attached file, in an alphabetical order.

Thanks,

Melv.

 

[NARAYANK991]

Hi ,

If the data in your table in the Consolidated tab is sorted on column F ( Store Name ) , then with a small change to the formulae , the output will be sorted store name-wise.

Narayan

 

[melvin]

Thanks mate, Appreciate it.

Melv.

 

[melvin]

Gday All,

I have been using the formula provided by Narayan which has been great.

I was trying to achieve something similar, but doesn't quite give me the required answer. I have explained my query in the file. Appreciate your assistance.

Melv

 

[David Evans]

Hi Melvin -

I *think* this is what you're looking for but forgive me if I've misunderstood you.

http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/

 

[melvin]

Thanks for your suggestion David.

Any takers based on the formula built in ?
IFERROR(INDEX(Sheet1!$B$5:$E$157,MATCH(SMALL(IF(Sheet1!$C$5:$C$157=$D$3,Sheet1!$E$5:$E$157),ROW(A2)),Sheet1!$E$5:$E$157,0),3),"")

 

[bosco_yip]

Thanks for your suggestion David.

Any takers based on the formula built in ?
=IFERROR(INDEX(Sheet1!$B$5:$E$157,MATCH(SMALL(IF(Sheet1!$C$5:$C$157=$D$3,Sheet1!$E$5:$E$157),ROW(A2)),Sheet1!$E$5:$E$157,0),3),"")

Change the above formula as per highlighted and copy down :

=IFERROR(INDEX(Sheet1!$B$5:$E$157,MATCH(SMALL(IF(Sheet1!$C$5:$C$157=$D$3,Sheet1!$E$5:$E$157),ROW(A1)),Sheet1!$E$5:$E$157,0),3),"")

Regards

 

[melvin]

perfect thanks mate.