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

Hi Still playing around and hopefully learning at the same time, I have one question, is there a way to protect the pivot table so the inputter cannot access it or modify it? Regards Stephen

The last example file I provided you with was based on one Jeovany provided. He was using macros, I did not look for them, I did nothing to change the file format. Actually, I didn't even notice they were the macro file format. Here are some articles about drop down lists Add a drop-down list to an Excel cell https://www.techrepublic.com/blog/microsoft-office/how-to-add-a-drop-down-list-to-an-excel-cell/ June 11, 2008 Susan Harkins Drop-down lists can greatly facilitate data entry. Here's a look at how to use Excel's data validation feature to create handy lists within your worksheets. ET MR Validation.docx **Create A Drop-Down List Of Possible Input Values** **** http://spreadsheetpage.com/index.php/tip/create_a_drop_down_list_of_possible_input_values/ ****If you're creating a worksheet that will require user input and you want to minimize data entry errors, use Excel's data validation feature to add a drop-down list. The best part about it is that you don't have to write any macros. Data validation is an excellent way to ensure that a cell entry is of the proper data type (text, number, or date) and within the proper numeric range. The drop-down list produced with the feature appears when a user clicks the cell. Create Drop-down Lists in Cells – Data Validation Lists 2018 08 08 Jon Acampora**** https://www.excelcampus.com/tips/data-validation-drop-down-list/ The complete Excel guide on how to create drop-down lists in cells (data validation lists). Includes keyboard shortcuts to select items, copying drop-downs to other cells, handling invalid inputs, updating lists with new items, and more: How to Create a Drop-down (Data Validation) List Drop-down List from Comma Separated Values Drop-down List from a Range of ValuesShortcut for Selecting from the Drop-down List How to Search the Drop-down List------------ How to Copy the Data Validation List to Other Cells Handling Errors and Invalid Inputs------------- Adding New Data to the Source Range of the List Removing Data Validation from a Cell ET MR Validation.docx **Combo Box KO’s Data Validation** June 5, 2013 Data Validation or Drop Down Lists are one of Excel’s greatest features, but I have one gripe with them, and that is the down arrow is only visible when the cell containing the data validation is selected. This is a pain when you use them in interactive Dashboard Reports because they need to stand out […] This next wiki has links to several articles about PowerQuery and PivotTables. I found them great learning resources. In 2016 MS change the feature name from PowerQuery to "Get & Transform", then in 2019 they went back to PowerQuery ... (go figure). ********************************* . The following Wiki has a collection of links the various “advanced” Excel tools. There links to free webinars, short articles and a few free and paid ebooks. . I like to download the webinar replay recordings for future reference, and download the example workbooks. If you are gung ho, you could add time references into the workbook back to the recorded session to make future cross reference easier to find specific features / instructions (I’ve been doing that). . These free sessions run roughly 1 hour each followed by a short shill for their related courses (which definitely sound worth the price). . Getting Started with “PowerQuery” / “Get & Transform” > Tables > PivotTables > PivotCharts > Dashboards ... PowerBI WIKI: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_o365b/get-started-with-get-transform-tables-pivottables/fea2f31d-7835-4f77-ad34-6fe3e6d07620?tm=1537369871055 **** https://1drv.ms/w/s!Am8lVyUzjKfpoBGWeAh0z876s7ri - Word Doc in OneDrive e wiki- Get & Transform - Data Modeling - Tables - PivotTables - PivotCharts - Dashboards -PowerBI.docx Since Office 2007, MS has introduced several "Power" tools that add a LOT of automation. Unfortunately these tools are rarely talked about, so they are effectively “hidden”. If you are interested in seeing some demonstrations of these features and their automation, the following Wiki has links to several free webinar replays you can view. I've used them as a starting point to learn about these new Power features. Even as a novice level programmer in them, I am a HUGELY impressed by these tools and constantly try to learn new "tricks" using them. PowerQuery (PowerPivot) > Tables > PivotTables > PivotCharts > Dashboards ... PowerBI ****. At the very end of the wiki there are links to free download PDF ebooks about these features. . ********************************* . Here are brief descriptions of the various "Power" tools. Hint : with these “Power” tools, MS has been up to it’s usual games, changing names. For example one tool is called PowerQuery, except in 2016 when they called it “Get & Transform”, yes they returned to PowerQuery in 2019. There are very vague mentions to “PowerPivot” and “Data Modelling” but MS rarely defines them . PowerQuery (Get&Transform) is used to import data from more than 80 different data sources inside and outside of Excel. Then it allows you to "clean" and format the data. PowerQuery records your changes so you can import new data later and it will automagically apply the same manipulations . PowerPivot / Data Modelling Power Pivot is all about analysing data using the familiar PivotTable tool every Excel user should know PowerPivot is a data modeling technology that lets you create “data models”, which is “geek speak” for creating relationships between different input tables, and creating calculation columns. With Power Pivot you can work with VERY large data sets, build extensive relationships, and create complex (or simple) calculations, all in a high-performance environment, and all within the familiar experience of Excel. In a nutshell, it allows you to use multiple data sources. Then you could import, merge and perform analysis on the resulting data. Excel can “natively” only store 1 million rows of data. Well before you hit the 1 million row limit, Excel will start performing very poorly, particularly if you have lots of VLOOKUPs etc. Power Pivot compresses the source data and stores it as a database within the Excel workbook. This means that you can store 10s (even 100s) of millions of rows of data directly in an Excel workbook in a highly compressed and performing way. You can distribute the result to other people without them needing to have access to the original source data. . Table Tables take data in grid form and apply simple row/column formatting. Tables use “structured references” in preference to simple ROW COLUMN (A1) references. In Structured References you use the column names to make the fomulas easier to interpret. When you enter a formula in a Table, it automagically copies the formula to all of the rows in the table, including new rows as they are added to the table. PowerQuery exports/saves data to table format. PivotTables prefer input data to be in table form. . PivotTable does complex automated calculations using a “drag and drop and click” user interface. The calculation and formatting options are simply amazing. In the webinars, the “Pivottable pros” are able to provide examples as fast as they can describe them. With practice you should be able to create “quickie” examples while talking to users. PivotTables can be connected to “Slicers” which give the users a simple, visual, way of filtering PivotTables (and linked PivotCharts) . PivotChart s can be generated from PivotTables to provide a dynamic graphical interpretation of the PrivotTable generated data . Dashboards are “simply” collections of PivotTables and/or PivotCharts that provide the user with dynamic control ie via slicers or filters, of much data in a single compact display. . PowerBI is a new (free) standandalone application that incorporates all of the “PowerTools”. It actually provides more features, and more chart options. MS is very actively working on developing this tool, adding new features in monthly updates. ************************************************ *************************************************************************** ************************************************

Jeovany Hello again When I downloaded your file, it did not include any slicers and if I go to insert slicer, an existing connections box appears, showing no connections found, is this down to dropbox or some other reason? Regards Stephen

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

create new tab taking data from one column

Anonymous

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

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

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

0 comments

55 additional answers

Anonymous

2019-09-01T10:29:43+00:00

Hi

Still playing around and hopefully learning at the same time, I have one question, is there a way to protect the pivot table so the inputter cannot access it or modify it?

Regards

Stephen
Was this answer helpful?

0 comments No comments
Anonymous

2019-08-31T09:43:13+00:00

The last example file I provided you with was based on one Jeovany provided. He was using macros, I did not look for them, I did nothing to change the file format. Actually, I didn't even notice they were the macro file format.

Here are some articles about drop down lists

Add a drop-down list to an Excel cellhttps://www.techrepublic.com/blog/microsoft-office/how-to-add-a-drop-down-list-to-an-excel-cell/   June 11, 2008       Susan Harkins

Drop-down lists can greatly facilitate data entry. Here's a look at how to use Excel's data validation feature to create handy lists within your worksheets.

ET MR Validation.docx

**Create A Drop-Down List Of Possible Input Values******http://spreadsheetpage.com/index.php/tip/create_a_drop_down_list_of_possible_input_values/****If you're creating a worksheet that will require user input and you want to minimize data entry errors, use Excel's data validation feature to add a drop-down list. The best part about it is that you don't have to write any macros. Data validation is an excellent way to ensure that a cell entry is of the proper data type (text, number, or date) and within the proper numeric range. The drop-down list produced with the feature appears when a user clicks the cell.

Create Drop-down Lists in Cells – Data Validation Lists 2018 08 08 Jon Acampora****https://www.excelcampus.com/tips/data-validation-drop-down-list/

The complete Excel guide on how to create drop-down lists in cells (data validation lists). Includes keyboard shortcuts to select items, copying drop-downs to other cells, handling invalid inputs, updating lists with new items, and more:

How to Create a Drop-down (Data Validation) List Drop-down List from Comma Separated Values

Drop-down List from a Range of ValuesShortcut for Selecting from the Drop-down List

How to Search the Drop-down List------------         How to Copy the Data Validation List to Other Cells

Handling Errors and Invalid Inputs-------------        Adding New Data to the Source Range of the List

Removing Data Validation from a Cell

ET MR Validation.docx

**Combo Box KO’s Data Validation**        June 5, 2013

Data Validation or Drop Down Lists are one of Excel’s greatest features, but I have one gripe with them, and that is the down arrow is only visible when the cell containing the data validation is selected. This is a pain when you use them in interactive Dashboard Reports because they need to stand out […]

This next wiki has links to several articles about PowerQuery and PivotTables. I found them great learning resources.

In 2016 MS change the feature name from PowerQuery to "Get & Transform", then in 2019 they went back to PowerQuery ... (go figure).

********************************* .

The following Wiki has a collection of links the various “advanced” Excel tools. There links to free webinars, short articles and a few free and paid ebooks.

.

I like to download the webinar replay recordings for future reference, and download the example workbooks. If you are gung ho, you could add time references into the workbook back to the recorded session to make future cross reference easier to find specific features / instructions (I’ve been doing that).

.

These free sessions run roughly 1 hour each followed by a short shill for their related courses (which definitely sound worth the price).

.

Getting Started with “PowerQuery” / “Get & Transform” > Tables > PivotTables > PivotCharts > Dashboards ... PowerBI WIKI:   https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_o365b/get-started-with-get-transform-tables-pivottables/fea2f31d-7835-4f77-ad34-6fe3e6d07620?tm=1537369871055****

https://1drv.ms/w/s!Am8lVyUzjKfpoBGWeAh0z876s7ri - Word Doc in OneDrive

e wiki- Get & Transform - Data Modeling - Tables - PivotTables - PivotCharts - Dashboards -PowerBI.docx

Since Office 2007, MS has introduced several "Power" tools that add a LOT of automation. Unfortunately these tools are rarely talked about, so they are effectively “hidden”. If you are interested in seeing some demonstrations of these features and their automation, the following Wiki has links to several free webinar replays you can view. I've used them as a starting point to learn about these new Power features. Even as a novice level programmer in them, I am a HUGELY impressed by these tools and constantly try to learn new "tricks" using them.

PowerQuery (PowerPivot) > Tables > PivotTables > PivotCharts > Dashboards ... PowerBI ****.

At the very end of the wiki there are links to free download PDF ebooks about these features.

.

********************************* .

Here are brief descriptions of the various "Power" tools.

Hint: with these “Power” tools, MS has been up to it’s usual games, changing names. For example one tool is called PowerQuery, except in 2016 when they called it “Get & Transform”, yes they returned to PowerQuery in 2019. There are very vague mentions to “PowerPivot” and “Data Modelling” but MS rarely defines them

.

PowerQuery (Get&Transform) is used to import data from more than 80 different data sources inside and outside of Excel. Then it allows you to "clean" and format the data. PowerQuery records your changes so you can import new data later and it will automagically apply the same manipulations

.

PowerPivot / Data Modelling

Power Pivot is all about analysing data using the familiar PivotTable tool every Excel user should know

PowerPivot is a data modeling technology that lets you create “data models”, which is “geek speak” for creating relationships between different input tables, and creating calculation columns. With Power Pivot you can work with VERY large data sets, build extensive relationships, and create complex (or simple) calculations, all in a high-performance environment, and all within the familiar experience of Excel. In a nutshell, it allows you to use multiple data sources. Then you could import, merge and perform analysis on the resulting data. Excel can “natively” only store 1 million rows of data. Well before you hit the 1 million row limit, Excel will start performing very poorly, particularly if you have lots of VLOOKUPs etc. Power Pivot compresses the source data and stores it as a database within the Excel workbook. This means that you can store 10s (even 100s) of millions of rows of data directly in an Excel workbook in a highly compressed and performing way. You can distribute the result to other people without them needing to have access to the original source data.

.

Table

Tables take data in grid form and apply simple row/column formatting. Tables use “structured references” in preference to simple ROW COLUMN (A1) references. In Structured References you use the column names to make the fomulas easier to interpret. When you enter a formula in a Table, it automagically copies the formula to all of the rows in the table, including new rows as they are added to the table. PowerQuery exports/saves data to table format. PivotTables prefer input data to be in table form.

.

PivotTable does complex automated calculations using a “drag and drop and click” user interface. The calculation and formatting options are simply amazing. In the webinars, the “Pivottable pros” are able to provide examples as fast as they can describe them. With practice you should be able to create “quickie” examples while talking to users. PivotTables can be connected to “Slicers” which give the users a simple, visual, way of filtering PivotTables (and linked PivotCharts)

.

PivotCharts can be generated from PivotTables to provide a dynamic graphical interpretation of the PrivotTable generated data

.

Dashboards are “simply” collections of PivotTables and/or PivotCharts that provide the user with dynamic control ie via slicers or filters, of much data in a single compact display.

.

PowerBI is a new (free) standandalone application that incorporates all of the “PowerTools”. It actually provides more features, and more chart options. MS is very actively working on developing this tool, adding new features in monthly updates.

************************************************

***************************************************************************

************************************************
Was this answer helpful?

0 comments No comments
Anonymous

2019-08-31T09:12:31+00:00

Jeovany

Hello again

When I downloaded your file, it did not include any slicers and if I go to insert slicer, an existing connections box appears, showing no connections found, is this down to dropbox or some other reason?

Regards

Stephen
Was this answer helpful?

0 comments No comments
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 No comments