create new tab taking data from one column

Hi

I have realised that the drop down lists are on the input sheet columns R & T, can you explain why they also have a separate tab called lists? is there a reason and I guess you add/edit/delete the ones on the input sheet?

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 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.

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

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

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

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