create new tab taking data from one column

Anonymous
2019-08-26T15:07:20+00:00

Hi

I have  a spreadsheet which has many tabs at the bottom for different companies. Sheet1 is the master where all info is recorded on a daily basis, I run the macro and it copies the data to the relevant company tab, all that is ok.

Now I have been asked that a new page is created with just the contents of one column selected. So for instance column G needs to be selected from the master sheet and a new sheet created called MS,Aldi Separation and any entries that have figures to be copied to a new sheet. The list below was done manually by copy and paste from the master sheet to a new sheet added called MSaldi separation

No. Invoice <br><br>Date Company MSAldi Separation
6.0 Year to 30th June 2015
* 30/06/2014 Forty Shillings 11,026.33
6.4 25/07/2014 40 Shillings 3,567.50
TOTAL 14,593.83 14,593.83
5.0 Year to 30th June 2014
5.9 28/02/2014 Bidwells 1,665.48
5.12 31/03/2014 Bidwells 1,798.40
5.14 30/04/2014 Bidwells 2,480.25
5.16 31/05/2014 Bidwells 7,762.62
5.18 11/06/2014 Bidwells 7,200.00
5.21 30/06/2014 Bidwells 8,173.20
6.0 Year to 30th June 2015
6.5 31/07/2014 Bidwells 4,158.45
6.22 31/01/2015 Bidwells 585.00
8.0 Year to 30th June 2017
8.14 28/02/2017 Bidwells 9,899.00
8.23 30/04/2017 Bidwells 3,725.00
8.25 31/05/2017 Bidwells 3,641.91
8.31 30/06/2017 Bidwells 1,493.43
9.0 Year to 30th June 2018
9.5 31/08/2017 Bidwells 575.00
9.6 31/07/2017 Bidwells 870.00
9.10 30/09/2017 Bidwells 533.50
9.0 Year to 30th June 2018
9.27 30/11/2017 Bidwells 350.00
9.38 31/01/2018 Bidwells 1,647.00
9.0 Year to 30th June 2018
9.46 28/02/2018 Bidwells 1,192.00
9.47 31/03/2018 Bidwells 1,416.00
10.00 Year to 30th June 2019
10.60 31/10/2018 Bidwells 200.00
TOTAL 59,366.24

I hope I have made my self clear, I look forward to receiving some help.

Regards

Stephen

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2019-08-30T00:10:44+00:00

Interesting. You were able to do it all in a Table.

I see you cleaned up his company data.

I would do 2 tweaks to your layout.

First I would put all the slicers at the top of sheet, ie move the "Fiscal year" slicer

Second, I would add a "Company" slicer.

Questions:

#1. what is the "Multi Acc Invoice"? Where did this column come from? I think I see it now, but is it really necessary?

#2. Why do some rows have various styles applied to them?

#3. Why the unreadable text color for most of the Fiscal year entries?

Stephen:

Do you need / want the amounts to be totaled?

Data entry is somewhat similar, but you need a separate row for each account type. This is a better format, actually the required format, for creating a pivot table.

D'oh! (head smack!) My mistake. I should have done this too (thanks for the reminder and inspiration Jeovany!) ...  So here is what his better formatted data looks like in a pivot

No spaces!

Notice that I put the "Designated Account" and "Fiscal Year" columns into the "Filter" area. This added the option to filter the pivot table in the upper right corner of the pivot. So you can filter by the specific account you wanted, and I picked one year, but if you need, you could also select all years.

I think this new pivot gives  you what you need.

(Yes, I am pivot crazy.)

Here is another example using the same pivot. I've filtered for "All" accounts to and one specific year, 2017. It includes a total for all accounts that year

Here is a link to my tweak of Jeovany's file

https://1drv.ms/x/s!Am8lVyUzjKfpoCf8_-gzab5ruAz0?e=xk0cgJ

Was this answer helpful?

1 person found this answer helpful.
0 comments

55 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-08-31T08:59:18+00:00

    Rohn

    Thank you again, I think the inputter would like it as simple as possible. I run Excel 2010 but the inputter has the latest version. My version I do have slicers.

    one quick question is, why is your file, saved as a macro enabled, when I cannot find any code?

    Could i ask you to supply a link to creating drop down lists and PowerQuery/Get & Transform feature.

    Is there an easy way to change the invoice number back to mine? and also the entries to not show multi a/c invoice (back to the inputters method?).

    My aim is to make the entire procedure as easy as possible for the inputter (81 year old man!) and then to create pivot tables and then lock them so they cannot be messed out with, so all they have to do, is enter the data and then maybe click the relevant tab to print out the reports they want. I thought maybe a table of contents for them.

    Regards

    Stephen

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-08-31T08:55:16+00:00

    My File, (Jeovany file) don't have any Pivot Table, because I did not make them.

    I leaved that to you, for you to practice and learn.

    But I did create slicers for a quick manipulation of your data.

    Now if you notice, with the Data Table in the way is now,

    If you just filter by Acc "M&S/Aldi/Retail" and Company "Bidwells" you will get the same report you were looking for in this post,

    With No Pivot table Involved

    Just different layout. Take a look at this picture.

    **Rows highlighted in yellow are Invoices that has more than one Designated Account

    ***Here shows only the ones that belong to  Acc "M&S/Aldi/Retail",

        a total of 20 Invoices that sum  =59,366.24 

     Regards

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-08-31T08:36:23+00:00

    Yes, PivotTables are a reporting tool. They normally create summary reports with many types of calculations, much more than just SUMs / totals.

    When used with slicers, they are very dynamic. Most of the use I've seen them designed for is online.  But yes, you can print them, the PivotTable reports, if you wish. I haven't seen any mention of any particular problem printing them.

    I gave you links to articles on doing drop down lists earlier. I can give you more. They are pretty simple once you are aware of the concept.

    Yes, Jeovany's example only contained a "normal" Excel Table with slicers ( a problem if you are using 2010). I think it seems to give at least some of the results you want, but the form is not what your users are used to so I don't know if they would be happy using it for data entry (a bit too much of a change for them).  As I said before, his table is an "unpivoted" version of your All Data table. Jeovany said he transformed your data using macros. I would have done it using the PowerQuery/Get & Transform feature.  So you could keep your original format for users to do data entry.  If you like the information that table gives you, you could keep it, and still use it to feed into a pivot table like I made.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-08-31T07:51:40+00:00

    Hi

    I think yesterday I was suffering from Excel blindness! Excel 2010 does support slicers. Jeovanny's file once downloaded did not have any the pivot table, however yours did.

    I now understand the purpose of the lists, still have to work out how to create a drop down list.

    Am I right in guessing, that a pivot table is just to manipulate the data, is it here that we print it out or is there a better option for printing out reports as and when required?

    Regards

    Stephen

    Was this answer helpful?

    0 comments