Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Defining Ranges (xp)

    I tried finding an answer to my problem by searching previous posts but was not successful. Attached is a sheet with a simplified version of what I am looking to do. In a nutshell:

    I have a number of areas dist. centers (1-67) that need to be summarized into regions, company definitions, etc. The number of centers will change in the future, i.e. new columns will be added. Currently, I know I am using a very inefficient way of calculating the contents of these columns, eg sum(D1:F1,J1:M1, etc). I would like to define the columns that refer to a particular region and then use those defined names in the formulas. I tried to define the above as a range and then use offset but I guess I did not know enough to make it work. Any help in finding a solution would, as always, be greatly appreciated.

    Thanks,

    Altin
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Defining Ranges (xp)

    I'd organize the raw data differently, in a database-like table:

    <table border=1><td>Month</td><td>Region</td><td>Area</td><td>Sales</td><td>Jan-07</td><td>Region 1</td><td>Area 1</td><td>25</td><td>Jan-07</td><td>Region 1</td><td>Area 2</td><td>30</td><td>Jan-07</td><td>Region 1</td><td>Area 3</td><td>35</td><td>Jan-07</td><td>Region 2</td><td>Area 4</td><td>40</td><td></td><td></td><td></td><td></td><td>Aug-07</td><td>Region 3</td><td>Area 10</td><td>70</td></table>
    You can then use a pivot table to summarize the data in different ways.

  3. #3
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defining Ranges (xp)

    I understand that using a pivot table would make a lot of sense for the example I sent over. However, currently it is not feasible to rearrange everything (There are 40 workbooks, with a LOT of information already there). Also, the data is currently coming in from an external data source (company intranet) and I assume that is why this is set up this way. Any chance of improving on what currently is...

    Thanks in advance,

    Altin

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Defining Ranges (xp)

    Enter the following formula in cell O10:
    <code>
    =SUMPRODUCT($B10:$M10*($B$8:$M$8=O$8))
    </code>
    Fill right to column Q, then fill down to row 17.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •