Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Sorting Items On Different Worksheets (2000)

    I have 13 worksheets in a workbook corresponding to the 12 months in a year, and a summary sheet. I purchase commodities monthly, sometimes from the same vendor more than once in a given month, but from at least 10 vendors in a month. What I want to do is on the Summary Worksheet, look into the individual monthly worksheets, and sum my purchases by vendor by month. For example, the first section of the Summary Workshhet would consist of the purchases I made in January, with row 1 being the total of all purchases from Vendor A, row 2 the total from Vendor B in January, etc.
    Any help?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sorting Items On Different Worksheets (2000)

    This sounds more like a summary than a sort. Do you mean something like this?

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center valign=bottom>1</td><td align=right valign=bottom></td><td align=right valign=bottom>1/1/2002</td><td align=right valign=bottom>2/1/2002</td><td align=right valign=bottom>3/1/2002</td><td align=center valign=bottom>2</td><td valign=bottom>Vendor A</td><td align=right valign=bottom>0</td><td align=right valign=bottom>20</td><td align=right valign=bottom>0</td><td align=center valign=bottom>3</td><td valign=bottom>Vendor B</td><td align=right valign=bottom>8</td><td align=right valign=bottom>3</td><td align=right valign=bottom>1</td><td align=center valign=bottom>4</td><td valign=bottom>Vendor C</td><td align=right valign=bottom>7</td><td align=right valign=bottom>0</td><td align=right valign=bottom>4</td></table>
    where the monthly sheets are named using three letter month abbreviations, the vendor name is in column A and the quantity is in column B, the cells in Row 1 of the summary are valid Excel dates, the formula for cell B2 is

    =SUMIF(INDIRECT("'"&TEXT(B$2,"mmm")&"'!"&"A:A"),$A 3,INDIRECT("'"&TEXT(B$2,"mmm")&"'!"&"B:B"))

    and is copied throught the table.

    [Edit. I have a habit of using " ' "&"name"&" ' ! " (spaces added for clarity, not actually used) in my indirect references to cover sheet names with spaces and unusual characters. In the above example this isn't necessary]
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sorting Items On Different Worksheets (2000)

    MUST you have 13 worksheets?

    Why don't you combine the monthly ones into one BIG worksheet (add a new column for all with the month and maybe the year).

    You have so many techniques to work with this data set for summary:
    Pivot Tables
    Advanced Filter
    AutoFilter w/ Subtotals
    Count/Sum IF
    D-Functions
    Sum/average with ARRAY formulas
    Etc

    You could do it all with 1 sheet and if you needed a Specialized summary you could create that (based on the one dataset) and use some combobox to select month and do other things.

    You could even keep your previous years in the same dataset and look at more longer term items.

    Steve

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting Items On Different Worksheets (2000)

    I already have them set up that way with volumes of data already entered.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting Items On Different Worksheets (2000)

    I'm sorry, John, I don't follow you. Yes, that is what I mean, but I don't follow what you are trying to do.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sorting Items On Different Worksheets (2000)

    All you have to do is create a new sheet and copy and paste the info into the new sheet one after the other. I assume all the columns are set up the same and all you are adding is a new group (month) at the bottom of the last one. With 12 sheets it shouldn't take that long.

    Just a suggestion: I think it will save you time down the road.

    Editorial Comment: Personally, I don't like to have multiple sheets that are set up identically. I find them redundant and cumbersome. I would rather create a big dataset, and create ONE output/summary sheet or chart that "grabs" the data and formats it. Use pulldowns to choose year and/or month etc. When you have multiple sheets/charts, and you find you want to change the "look" of them (and you will), you must change ALL of them. If you have only ONE, that is the only one you have to change.

    Steve

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sorting Items On Different Worksheets (2000)

    I had a formula error since I was doing that on the fly. Should have been:

    =SUMIF(INDIRECT("'"&TEXT(B$1,"mmm")&"'!"&"A:A"),$A 2,INDIRECT("'"&TEXT(B$1,"mmm")&"'!"&"B:B"))

    What I'm doing is using Sumif, and using indirect to get the applicable month argument for the criteria matches and data. See attachment.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting Items On Different Worksheets (2000)

    What are "A:A" and "B:B" referencing?

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sorting Items On Different Worksheets (2000)

    Steve, are you ever going to permit Private Messsages or post an e-mail address? Handy for a "side chat" at times.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sorting Items On Different Worksheets (2000)

    Entire column A and entire column B, respectively containing the Vendor name and applicable Quantities. In the example, see what

    =("'"&TEXT(B$1,"mmm")&"'!"&"A:A")

    yields, and you'll get the drift.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sorting Items On Different Worksheets (2000)

    That's true if you are running simple data tables, but in more complex situations you may need separate sheets. For example I occasionally get to do pricing exercises by the 40 more populated US states, each of which has it's own permutations, and each of which has different enrollment in several products, then I get to do national weighted average revenue and margin calcs. Neither pivot tables nor data consolidation fit the bill here. To get my summaries I use identical layouts (which is also useful for 3D Find-&-Replace when a national calculation or layout has to change) but then use an intermediate sheet which gathers the data through INDIRECT, then a summary from that sheet with the state and national weighted averages.

    My OTHER personal view is, if there's that much data, shouldn't I be using Access?
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sorting Items On Different Worksheets (2000)

    Done, I hadn't looked at the options since I signed up.
    Steve

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting Items On Different Worksheets (2000)

    John,
    Works like a charm.
    Thanks for your help!
    Jeff

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Sorting Items On Different Worksheets (2000)

    Hi John,
    Re your first point, although what you describe can generally be done Steve's way via the addition of some more columns, I must admit I tend to do it your way! For analysis of insurance programmes by class for example, I tend to have multiple identically-formatted sheets which allow me to adjust rates for classes individually and have the combined effects immediately visible. Having said that, now that I have more time, I do plan to look at doing it Steve's way since changing the formats is a major pain!
    Re your other point, yes I tend to store my data in Access - easier for one-off analysis for a start and most of my data sets wouldn't fit on one worksheet - but my workbooks then extract the data for standard analysis and reports. If you've ever done any work with charts in Access, you'll know why!
    Just my 1.3p worth (not sure of current exchange rates for <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>)
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sorting Items On Different Worksheets (2000)

    More of my 2 cents:
    If I understand your comment: You can use "Access-type" techniques to add a "class" column. Then have a class "lookup Table" in excel which gives the rates. Change the rates in the table will automatically alter the calculations!

    Excel does NOT just have to be a "flat database". With all the match/lookup, etc you can create relational databases in excel (though I admit it is easier in access). I tend to use excel rather than access due to MY "comfort and familiarity" with excel over access. It seems more obvious, especially when I tend to analyze more (charts and stat functions) than true database features (relationships and large datasets, data input forms, etc)

    For example, even though I know access and excel use the IDENTICAL charting program, excel has the user interfaces which make it easier to chart in excel. I don't need the queries and inputs as much as my programs are usually mine: I don't make them for others to use and enter. When I let them use it, it is read-only.

    I don't want to imply that my technique is better than another, much depends on what the "programmer" is familiar with. I know many people think and do things differently (there are many ways to accomplish the same things): I think that whatever works for you is the best way to do it, you are ultimately the one who has to live with it and keep it updated!

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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