+ Reply to Thread
Results 1 to 4 of 4

Reporting on updating data

  1. #1
    Registered User
    Join Date
    06-20-2015
    Location
    UK
    MS-Off Ver
    2007
    Posts
    7

    Post Reporting on updating data

    Good afternoon all.

    Noob here (breaking the rule and making post#1 a request for help!)

    I have an excel spreadsheet which currently has 3 tabs.
    1 is active, 2 is not active and 3 is completed.

    The spreadsheet was set up like this to easily monitor the numbers in the three different stages (before I learnt more about excel and could've left it all in one?).

    This information is reported on monthly in great detail, and "reviewed" daily/ weekly.

    I want to create sheet 4 which will be a combination of 1,2 and 3 and then use this for reporting. Others may use this sheet, so it's better that it doesn't edit the data in the other three sheets. This will involve reports on % of current status, time lapsed, total number between 2 dates etc.

    All data starts on sheet 1, worked on and tracked for updates, and then moved to either 2 or 3. At the stage in which it is to be moved across, it turns either green or red, for completed or not active. At present, the colour is merely conditional formatting, but the information is then cut and paste into one of the other sheets. At present, they are NOT set up as tables (I have since learned the error of my ways and will look to format as a table shortly). Can this be done manually?

    Also, the reoprting dates are not 01/one month - 31/one month, it's split based on the third week of every month as a cut off period. Is there any way the graphs can display this without creating a calculation and another table?

    I am sure I've not made myself perfectly clear, but would love if someone could assist with this matter. It is currently taking me 1-2 days per month to report on this information. I've started to automate it, however this would be the final hurdle to make it automated and extremely helpful.

    Kind regards,
    Michael

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Reporting on updating data

    Design recommendations:

    1) Go back to a single sheet where all of these items coexist. For now, just create a new sheet called MASTER and copy all the data in.

    2) Sort the consolidated data by DATE, this will "mingle" the results, but that's OK. We're creating a database for reference.

    3) In this table you must have a column for STATUS

    ACTIVE
    NOT-ACTIVE
    COMPLETED

    Every row in this new table must have a status. This is in lieu of having an entirely separate sheet, unnecessary.

    4) Click on the TITLE row across the top of the data and turn on the DATA > FILTER. Drop down arrows will appear in each header. Your Autofilter is now ready to use.

    5) Use the drop down in the STATUS column to view ACTIVE only by checking only that option. You are now looking at what WAS your "ACTIVE" sheet, without that sheet actually existing. You can edit rows, add info change the status on any row, etc. Just like you used to do on the separate sheet. You can print it.

    Then you can change the filter to NON-ACTIVE and voila! The Autofilter makes multiple sheets unnecessary plumbing.

    =================
    6a) I believe base on your last question that you will need to add a field to your master database that is designed to "flag" which period each row goes into based on your business calendar.

    You may need a simple lookup calendar for this. Create a sheet called CALENDAR that has two columns:

    A = Start date for each period / year
    B = Name of this special period / year

    So in A2 for instance you might list the date for the first period that exists in your data... which happens to be period 1 of 2012. so maybe that start date is:

    A2: 1/1/2012
    B2: P1-2012

    If period 2 actually starts only 20 days later, the next entry would be:

    A3: 1/21/2012
    B3: P2-2012

    Continue the table downward way off into the future, listing the FIRST day of each period/year and the name of that period/year.

    You now have a lookup table. You can hide this sheet if you wish.


    6b) Back in your MASTER add a new column that has the ability to determine the period/year for you. Let's assume the relevant date is in cell D2 and the new field will be M2, use this formula:

    M2: =IF(D2=0, "", VLOOKUP(D2, Calendar!A:B, 2, 0))

    Copy that down and all the relevant periods will be determined.

    =============
    7) You can now use normal Pivot Table reporting against this database to give you statistics from the ONE Master any way you wish, including collecting stats into periods based on the new field in operation from step 6 above.



    It's a bit of work to fix what you've created, but the END RESULT will be very user-friendly and creating reports will be a breeze once you start playing with Pivot tools, or even if you just create a manual dashboard using COUNTIFS() and SUMIFS().

  3. #3
    Registered User
    Join Date
    06-20-2015
    Location
    UK
    MS-Off Ver
    2007
    Posts
    7

    Re: Reporting on updating data

    Comments below:

    1) Go back to a single sheet where all of these items coexist. For now, just create a new sheet called MASTER and copy all the data in.
    DONE - all back in one sheet and will use filters accordingly - it's not that complicated for someone else to adopt.

    2) Sort the consolidated data by DATE, this will "mingle" the results, but that's OK. We're creating a database for reference.
    Agreed - done.

    3) In this table you must have a column for STATUS

    ACTIVE
    NOT-ACTIVE
    COMPLETED

    Every row in this new table must have a status. This is in lieu of having an entirely separate sheet, unnecessary.
    Data already had this one, so done!

    4) Click on the TITLE row across the top of the data and turn on the DATA > FILTER. Drop down arrows will appear in each header. Your Autofilter is now ready to use.
    I'm used to using filters - but thanks!!

    5) Use the drop down in the STATUS column to view ACTIVE only by checking only that option. You are now looking at what WAS your "ACTIVE" sheet, without that sheet actually existing. You can edit rows, add info change the status on any row, etc. Just like you used to do on the separate sheet. You can print it.
    Thanks!

    Then you can change the filter to NON-ACTIVE and voila! The Autofilter makes multiple sheets unnecessary plumbing.
    Thanks

    =================
    6a) I believe base on your last question that you will need to add a field to your master database that is designed to "flag" which period each row goes into based on your business calendar.

    You may need a simple lookup calendar for this. Create a sheet called CALENDAR that has two columns:

    A = Start date for each period / year
    B = Name of this special period / year

    So in A2 for instance you might list the date for the first period that exists in your data... which happens to be period 1 of 2012. so maybe that start date is:

    A2: 1/1/2012
    B2: P1-2012

    If period 2 actually starts only 20 days later, the next entry would be:

    A3: 1/21/2012
    B3: P2-2012

    Continue the table downward way off into the future, listing the FIRST day of each period/year and the name of that period/year.

    You now have a lookup table. You can hide this sheet if you wish.


    6b) Back in your MASTER add a new column that has the ability to determine the period/year for you. Let's assume the relevant date is in cell D2 and the new field will be M2, use this formula:

    M2: =IF(D2=0, "", VLOOKUP(D2, Calendar!A:B, 2, 0))

    Copy that down and all the relevant periods will be determined.

    Done - sort of, used an if statement and just calculated it as totals, rather than giving the answers straight away. Same end result though. Thanks!

    =============
    7) You can now use normal Pivot Table reporting against this database to give you statistics from the ONE Master any way you wish, including collecting stats into periods based on the new field in operation from step 6 above.

    I could do with learning more on Pivot Tables - I seem to be producing the tables of the data I want and then go on from there... same concept, just a longer way of getting to the same end result!

    It's a bit of work to fix what you've created, but the END RESULT will be very user-friendly and creating reports will be a breeze once you start playing with Pivot tools, or even if you just create a manual dashboard using COUNTIFS() and SUMIFS().[/QUOTE]

    I have countifs and sumifs and row(s) and index match - most importantly, I have named ranges! I learnt the hardway!

    Great comments - now to head on over to the Pivot section.

    Pivottt.....Pivott...

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Reporting on updating data

    Hehe, glad I could help at least a little...

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Replies: 0
    Last Post: 02-04-2015, 04:24 AM
  2. Replies: 1
    Last Post: 12-14-2013, 11:34 PM
  3. Complex VBA Form (Admin Console) & Reporting tool, Updating sheet(s) sending email etc
    By MisterTRIM in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-17-2013, 06:10 AM
  4. Reporting using data
    By Deanobey in forum Excel General
    Replies: 0
    Last Post: 04-15-2009, 07:57 AM
  5. Reporting using data
    By Deanobey in forum Excel General
    Replies: 6
    Last Post: 04-13-2009, 04:46 PM

Tags for this Thread

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