+ Reply to Thread
Results 1 to 17 of 17

Generate a report on second sheet from a filtered values from the first sheet

  1. #1
    Registered User
    Join Date
    06-23-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    70

    Generate a report on second sheet from a filtered values from the first sheet

    The table on Sheet 1 is being used a record system for history of each lifting equipment we mobilized to our client for rental. The Door No. (Column A) is a name for each equipment. The Date Mobilized is the date when equipment left our premises and started working on site and Date Demobilized is the date when equipment went back to our yard.

    To explain my requirement, refer to the first 7 rows indicates that the Door No. ATC0005. It was mobilized 7 times but demobilized 6 times. Meaning that the equipment is currently deployed because the last mobilization dated 01-Jul-13 doesnt have an corresponding data of Demobilization. My first requirement is to have a filter where in it will show the list of currently mobilized equipment. In that case I just go to Date Demobilized and filter it by only showing the blanks.

    My second requirement is to know how to filter to only show the available equipment. I was able to analyze it this way: For example we take a look at the history of FLD0001, it has 5 Mobilization Date and 5 Demobilization Date. So i need to know a function to filter a Door No. that has the same number of Mobilization Date and Demobilization Date. My first solution is to just filter the Date Demobilized to not show blanks hence it will show all equipment that has the same number of Date Mob. and Date Demob. But my exact requirement is to have a real time report on a separate sheet (refer to Sheet 2).

    Sheet 2 is report for equipment status. Under Equipment (Column A) are the list of equipment. Using the data available on Sheet 1, the cells under Client (Column B) should be filled up either "Available" or "Deployed".


    Lastly, for all Deployed equipment only the Client Name should also be retrieved on the report on Sheet 2.

    filter values.xlsx

  2. #2
    Registered User
    Join Date
    06-23-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    70

    Re: Generate a report on second sheet from a filtered values from the first sheet

    bump no response

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Generate a report on second sheet from a filtered values from the first sheet

    Hi miss chloe,

    See if a pivot table answer works for you. See the attached. You can change the customer filter to see what they have.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    06-23-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    70

    Re: Generate a report on second sheet from a filtered values from the first sheet

    Thank you for your response. I was able to determine base on the pivot table which equipment is available or deployed by returning the value of cells iN column H and Column I if they are equal or not. I think, if we follow this path it is possible to accomplish my exact requirements.

    First, remove and incorporate the exact pivot table you made on Sheet 2 so that it will be used as my report.

    Second, if a certain equipment returns the value of Deployed as the status, it should retrieve the Client Name. The Client Name that will show up is determined by the certain row on Sheet 1 that doesnt have a date demobilized. Example is Row 8: the Door No is ATC0008, it doesnt have a Date Demobilized, so the Client Name that will show up on the report on Sheet 2 is "E".

    If its no bother, I want to know exactly how the pivot table was made, specifically base on my scenaro. If in case you can provide me the steps, teach me on how to make this on a separate sheet. I think upon learning how to do a pivot table myself, i will be able to understand and can solve my own requirements.

  5. #5
    Registered User
    Join Date
    06-23-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    70

    Re: Generate a report on second sheet from a filtered values from the first sheet

    Just an update i was able to recreate the pivot table myself, and i did it on Sheet 2. All i need now is to accomplish my 2nd requirement. to determine the Client Name of each deployed equipment.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Generate a report on second sheet from a filtered values from the first sheet

    Hi miss chloe,

    I'm thinking a second pivot table that has the equipment number as the FILTER and then the rows are Date and Customer name would do what you want. You might also try to only show the MAX date of each equipment number, thus showing who had it last or has it still.

  7. #7
    Registered User
    Join Date
    06-23-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    70

    Re: Generate a report on second sheet from a filtered values from the first sheet

    I think that will work. We just to determine the recent date from the list of Date Mobilized under one equipment then it will return the value of Client, but i don't know how to do that. Using the same workbook you uploaded, can show how me for example the ATC0005 to retrieve the Client "E" on cell J6. Because the ATC0005 equipment has the recent mobilization date of 15-Jun-13.

  8. #8
    Registered User
    Join Date
    06-23-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    70

    Re: Generate a report on second sheet from a filtered values from the first sheet

    Hi, I was able to encounter some error for the pivot table. It is given that a certain Door No., for example ATC0005, has unequal value of Date Mobilized and Date Mobilized hence it should be deployed. But when we go row #8 to put a date value on cell D8 or the Date Demobilized, Date Mob. and Date Demob. should have equal value, but you will notice on the pivot table on the right the Count of Date Demobilized for ATC0005 remains 6, however we should expect that it will return 7.

  9. #9
    Registered User
    Join Date
    06-23-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    70

    Re: Generate a report on second sheet from a filtered values from the first sheet

    bump no response

  10. #10
    Registered User
    Join Date
    06-23-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    70

    Re: Generate a report on second sheet from a filtered values from the first sheet

    bump no response

  11. #11
    Registered User
    Join Date
    06-23-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    70

    Re: Generate a report on second sheet from a filtered values from the first sheet

    bump no response

  12. #12
    Registered User
    Join Date
    06-23-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    70

    Re: Generate a report on second sheet from a filtered values from the first sheet

    bump no response

  13. #13
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Generate a report on second sheet from a filtered values from the first sheet

    Hi,

    When you add a new row of data or change the data in a table, the pivot table does NOT reflect the change until you REFRESH the Pivot Table. If you add a new row to the data and want the pivot table to use this new row in its calculations you also need to change the rows that the pivot table is using.

    If you want the pivot table to always use new rows of data you need to learn about Dynamic Named Ranges that grow automatically. Then you also need to do some VBA tricks to have the Pivot table refresh whenever data is added or changes.

    I have an example of this but it will take some digging to understand it. It has a Dynamic Named Range (DNR) of data. Then this name is used in the range where the Pivot's data comes from. Then there is an Event Macro that refreshes the Pivot Table is anything changes. See the attached....

  14. #14
    Registered User
    Join Date
    06-23-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    70

    Re: Generate a report on second sheet from a filtered values from the first sheet

    Good day MarvinP, thank you for giving me some materials to teach myself about pivot table. Apparently i need to search some other learning materials about pivot tables for beginners like me because upon checking the files, it might take me time to understand. On the other hand, i was able to simplify my requirements and i think it will only need a formula.

    Using the same worksheet - the formula should be on Column E and will return only 2 values: Deployed or Available.

    A. Deployed is returned by determing if a certain cell under Date Demobilized is blank.

    B. Available is return by determining 2 conditions using AND statement.

    1. Get the list of Door No. that has the same value, for example ATC0010 (Row 25 - 28), it needs to determine the cell which has the recent date of Date Mobilized, in that case its C28.
    2. Then the adjacent cell or D28 should not be equal to blank. Result: The cell E28 should return Available.

    I know the formula how to get Deployed.

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


    I just need to have that formula modified to get the Available.

  15. #15
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Generate a report on second sheet from a filtered values from the first sheet

    Hi,

    Could you attach the current worksheet so we can see what solution you have now and get one for the Available?

  16. #16
    Registered User
    Join Date
    06-23-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    70

    Re: Generate a report on second sheet from a filtered values from the first sheet

    Please refer to this file. This one is updated.
    Rental Inventory Tracking (2).xlsx
    Attached Files Attached Files
    Last edited by miss_chloe; 12-14-2013 at 05:19 AM. Reason: Updating the uploaded file

  17. #17
    Registered User
    Join Date
    06-23-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    70

    Re: Generate a report on second sheet from a filtered values from the first sheet

    bump no response

+ 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. Returning the values of filtered items on another sheet
    By tangcla in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 11-11-2013, 10:46 PM
  2. Generate Summary Report in a tabular form from the Input Sheet
    By excelhelp18 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-30-2012, 12:52 PM
  3. Filter sheet then assign values into array based on this filtered sheet
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-06-2011, 10:32 AM
  4. Populating a report sheet based on imported values on another sheet
    By th3spankst3r in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-05-2010, 06:19 PM
  5. Paste Values To Filtered Sheet
    By LB79 in forum Excel General
    Replies: 0
    Last Post: 10-31-2008, 07:47 AM

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