+ Reply to Thread
Results 1 to 15 of 15

Altered Views of Standard Report

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Altered Views of Standard Report

    The attached report is a summary of a report I share each week. The "Consolidated" tab has all the information I share, but my audience wants separate views, one for Strategy and Position Function Title, one by Region and Position Function Title, and one without Strategy or Region, showing only Position Function Title. Finally, to populate the "FY14 Running History" tab I need the totals just by region, without Position Function Title. In the past I've done this by creating various tabs with the different views, and my boss has just asked me to add the Strategy in, and to make the "Running History" filterable by Strategy.

    I'd like to be able to filter my view in a single tab. I could do that with a Pivot, but my audience isn't particularly Excel savvy, so need to make it very simple for them. Also, I don't know how I'd configure my calculations at the bottom of the "Consolidated" tab to work with changing columns in a pivot table. I'd appreciate any help I can get on this.

    Thanks,
    John
    Attached Files Attached Files

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Altered Views of Standard Report

    Perhaps Pivot tables could help you. These are hastily prepared reports but may give you an idea of how you want to proceed.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Altered Views of Standard Report

    newdoverman,
    I appreciate the effort, but your solution is basically what I'm already doing, then adding my calculated fields. My desire here is to get away from the multiple tabs approach while still using my calculated fields (located at the bottom of the "Consolidated" tab).

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Altered Views of Standard Report

    Why not filter in place or use the Advanced Filter to create the reports that you want.?

    The Advanced filter will allow you to have the columns in any order that you want and use more sophisticated filtering.

  5. #5
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Altered Views of Standard Report

    I fear I'm not explaining myself correctly. Your question "Why not filter in place or use the Advanced Filter to create the reports that you want.?" shows the disconnect. I don't want "Reports", I want one report tab, where my users (non-Excel savvy) can filter the view to see only the information they need to. What complicates it is that I have formulas that utilize the data in the view, and don't know how to keep those going with changing views, and also don't know how to make the options for changing views easy for my audience.

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Altered Views of Standard Report

    Are you saying that you want to distribute the workbook to different users and allow them to only produce various reports with data that they have need of and access to?

    If that is the case, I don't think that Excel is the best application to deliver that. A database would be better for that.

  7. #7
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Altered Views of Standard Report

    Not exactly. My data is fixed. I can use a pivot table (one method) to show whatever subset of the data I want to show, but want to make that part easy for my users. I also have formulas outside the pivot that calculate values in the Pivot. I don't know how to get the formulas keep up with the changes to the pivot table.

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Altered Views of Standard Report

    If you have relative references in your formulae, these should not be affected in the way a Pivot table is presented.

  9. #9
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Altered Views of Standard Report

    Please explain. If my formula in C1 is something like "Subtotal(9,G2:G50)", where G is my Authorized positions count, and my user takes out the region field, so G in the pivot moves to F, how do I provide a relative reference to allow my formula to keep up with the new column?

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Altered Views of Standard Report

    SUBTOTAL requires that the data being subtotalled, be kept together. You would have to use some other function like SUMPRODUCT, SUMIF or SUMIFS that don't require that the rows be kept together. If you have several subtotal ranges and you sort, then the ranges get messed up because they are no longer together. I suspect that your subtotal is really a TOTAL but I don't know for sure as your example doesn't show what exactly you are doing.

    A SUBTOTAL formula with several ranges would look like this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Altered Views of Standard Report

    Actually, if on the "Consolidated" tab you go down to row 700 you'll see my formulas. If my data in the rows above 700 was done by a pivot, as different selections are made the columns the pivot data reside in would change. That's where my stumbling block is.

  12. #12
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Altered Views of Standard Report

    SUBTOTAL is the wrong function to use. Use the SUM function. Replace all the SUBTOTAL formulae with the SUM as in the following example.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The calculations done in a Pivot Table are done in the Pivot Table and not the source data. A Pivot Table doesn't use the grand totals at the bottom at it creates its own Grand Totals.

    You can make many different Pivot Tables from the source data and the source data is not changed at all by the actions in the Pivot Table.

  13. #13
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Altered Views of Standard Report

    You're right on the Subtotal formulas; if I went with a Pivot I could easily do away with those. But in Cell G702 I calculate the vacancy rate. How would I be able to do with a variable pivot table?

  14. #14
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Altered Views of Standard Report

    Ok, I added a vacancy rate calculation to each row of the Consolidated tab. This was the same calculation that you have at the bottom of that sheet (formula amended to show correctly). There is a discrepancy between what is shown at the bottom using column totals vs using the average of the row amounts for each area. This is due to how - values are handled over the row vs how they are handled as a part of the total. Manual calculations on random rows suggest that the row calculation is more accurate.

    A new Pivot table on TAB 5 (red) includes this new column and is formatted as a percentage. The overall average vacancy rate varies due to the way that the blank cells are being used in the calculations. Sometimes, the - sign messes up counts and that makes for errors in percentages. I didn't check your data for TEXT numbers so that may be part of the problem also.

    When making a Pivot table, be sure to not include your totals at the bottom of the data.

    I think that you have enough now to be able to produce what you want with a bit of experimentation.
    Attached Files Attached Files

  15. #15
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Altered Views of Standard Report

    Nope, still doesn't get me there. The average of the vacancy rates isn't a real world solution. For my target audience, they'll need to look at either: the whole State, a specific Strategy, or a specific Region, either with or without Position Function level. So the vacancy rate calculated at the bottom needs to be the vacancy rate for whatever subset they're looking at. That's why the subtotal formulas were there, to only look at the visible cells, as the vacancy rate is calculated by the subset.

    I think rather than go the pivot table approach I'll simply put the subtotals at the top, and allow my users to simply filter. The details will be below, and the subtotals will be above. In that way i think I can keep everything in one view, and still give them the views they want to see. I'll post my completed report when I finish.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Once entered cant be altered
    By moley165 in forum Excel General
    Replies: 1
    Last Post: 06-21-2013, 07:01 AM
  2. [SOLVED] Expert help- design macro to format non-standard excel report
    By ZiadzExcel in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-12-2012, 02:29 PM
  3. Create a report with data from cells, and standard text.
    By cristian.ene in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-03-2011, 07:05 AM
  4. Number entry altered
    By daguerrotype in forum Excel General
    Replies: 1
    Last Post: 01-03-2011, 08:49 AM
  5. altered color palette
    By Rob van Veen in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-16-2009, 06:46 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1