Jump to content


Photo

Auto populate and more

pivot table

  • Please log in to reply
3 replies to this topic

#1 Nessa

Nessa

    Member

  • Members
  • PipPip
  • 10 posts
  • LocationSheffield

Posted 29 November 2013 - 12:03 PM

Hi, help required please.  :wub:

 

1.     I have a workbook of chemicals that is continuously being added to (Columns A to P).  I have a work "sheet 1" with all the data.  What I would like to do is to automatically copy columns A, B, C, D and P from "sheet 1"  to "sheet2".

 

2.     Column P is a column of risk categories score, I would then like  "sheet 2" to sort from highest no to lowest.

 

3.     In "sheet 1" can I have the sum total of each chemical category risk  in column H auto populate column P?

 

I hope I have explained myself properly.

 

Thank you in advance for any help :D



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 29 November 2013 - 02:47 PM

Hi Nessa,

 

I'm not 100% clear on what you're trying to do - it would help if you could attach some example data (you can do this by clicking More Reply Options when replying).

 

From what I can gather, you just need a sorted report in "sheet 2" based upon your data and sorted by risk category.

 

I have created some example data to walk you through this process:

 

chemicals1.png

 

In order to summarize and sort this data in a separate table, you'll need to create a pivot table.  If you're not familiar with pivot tables, I strongly suggest taking a look at the lessons in Expert Skills Session Five: Pivot Tables.

 

To create your pivot table, follow these steps:

  1. Select your data and click Insert > Pivot Table.

    chemicals2.png
     
  2. Click OK on the dialog that appears.
     
  3. Select all of the fields that you want to appear in your pivot table. I have selected all of them, but you may want to exclude some of them if they're not needed.

    chemicals3.png
     
  4. Right-click in the Sum of Risk Category column and click Sort > Sort Largest to Smallest.

    chemicals4.png
     
  5. Rename the columns and apply a style if you wish.

    chemicals5.png

If you have any trouble following these steps, you can find more detailed instructions for creating pivot tables in Expert Skills Lesson 5-1: Create a pivot table.

 

The only drawback to this approach is that the pivot table will not automatically update itself when new data is added to your 'main' data table.  In order to update the pivot table, you must right-click on it and click Refresh from the shortcut menu.

 

I am not sure if this covers everything that you are trying to do, but hopefully it is a useful first step.  I have attached my example spreadsheet.

 

Attached File  Chemicals.xlsx   114.31KB   253 downloads

chemicals6.png

 

If you need any more assistance, please reply with more details of what you need.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 Nessa

Nessa

    Member

  • Members
  • PipPip
  • 10 posts
  • LocationSheffield

Posted 02 December 2013 - 08:29 AM

Morning

 

Thank you for you help.  I (hopefully) have attached a section of file that I am working on.  I am wanting to copy columns a,b,c,d and p onto sheet 2 automatically as data is entered in sheet 1.

 

Once it is on sheet 2 for it to sort on the matrix score figure (highest first).

 

Also, on sheet 1 for the sum of the matrix score (column I) to be automatically entered into column P on sheet 1.

 

Sorry, I know this is a big ask.

 

Regards

Nessa :)



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 02 December 2013 - 04:57 PM

Hi Nessa,

 

I'm sorry, but your attachment doesn't seem to have come through.  You have to click Attach This File before a file will be attached to your post.

 

I still think a pivot table will work for your first requirement, but I will need to see your example before I can understand the second task.

 

Please try again to attach the file and I will take a look.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users