create new tab taking data from one column

Hi

I have downloaded your revised file.

1 If I want to delete a line for instance in case of error, can I just highlight the row and press delete?

2 Is there a way to ensure that the user presses tab at the ned of an entry and not press enter so it will expand the pivot table?

3 What is the purpose of the tab called lists? do we need to do anything with it?

4 in report mode, how do I get it to list just ms/aldi/retail entries and group together all entries for the companies and give a sub total?

Thank you again for all your help.

Regards

Stephen

Yes,

I got the file. I'm working on it.

I'll let you know when ready.

#1

If I go to All Data, at the top is a list of some transactions

#2

then below that are all the other transactions, which are not in the order they were originally entered.

#3

Line 236 which was a line I entered before your modifications, doesn't appear anywhere?

2019 Aug 26	Brown	100.00							100.00

If i go to report or lists, Brown is not recorded anywhere, I have no idea which line the inputter would enter a new line of info.

#4

On the report page you have fiscal year 2018 & 2019, how do you add all the previous years? also how do i select all entries for one particular column and not show anything else? again no idea how pivot tables work or how to use them

I've uploaded a new example. I've left the original one so you can practice on it to recreate what I've done in the new one. Here is the new version:

https://1drv.ms/x/s!Am8lVyUzjKfpoCaL4T1a7Fulw5_i?e=rrE7Oc

Yes, using Tables, PivotTables, Slicers and any other related features will require some learning on your part and the part of the users.  But once they learn a few simple features and functions their jobs will be easier.  For example, by converting the "total" column a sum of the other columns, the user can use it as a cross check on their data entry. They can compare the displayed calculated value to their original source material. If the numbers do not match, then they know to recheck their data entry on the other columns. That is just one of the learning points.   Training them how to sort the input table could make it easier to research source data for the pivot if there are apparent errors in company summaries.

Yes, I know trying to teach some users is next to impossible. Like leading a horse to water ... you can't make them "drink"... But if you show them the advantages of the newer approach you should be able to convince most, if not all.

Yes, you also have to learn some more about pivot tables. But the automation that they introduce is truely amazing. If you take a look at some of the webinars I have links to you will see the additional things you can do with pivots. You will be a "superstar".  If your company has a training budget, the webinars are introductions to online courses that are well worth the time and money.  They can do a MUCH better job of teaching you about PivotTables than I can, including hundreds or more likely thousands of features and functions I don't know about myself.

You could add a "learn" tab with simple instructions for the users to enable them to use the new functionality to their best advantage. It could include links to more in-depth learning/training resources. Either something you put together specific to your needs, or to some of the links I can provide you.

I'm guessing you are asking about my example

#1

I cut your data down, so it was easier for me to work with.  I just included 2 years of data during development. I picked those 2 years since they included rows that had the amounts split between several accounts.

#2

All of the data is still there, as you noticed at the bottom.  Since it is separated by blank lines it is not included in the pivot, yet.  I took the data from the early years, copied it, and then pasted it at the very end of the data.

#3 #4

Simply delete the blank lines in the data to add it to the input table, then right click anywhere inside of the Pivottable,  and select "Refresh".

I already told you how to do data entry, but again, you have multiple options.

#A

You can simply add the data to the blank row immediately below the table. The table will extend to include that row. Like normal, press <tab> to move to next cell in row. At the end of the row in the table, pressing <tab> will insert a new row and move you to the start of the row.

After adding the data to the bottom of the table, although it is not necessary for the pivot, you can sort the table to place it "in order" for verification against your input source.

The sort you would use is simply date.

The sort I used was Fiscal Year end,  then company name then date, that way all of the relevant data is sorted together

#B

Or you can insert a blank row in the middle of the table, ie use <CTL><SHF><+> then enter data

#C

Or you can copy the data from another spreadsheet and simply paste it to the bottom of the existing table, then use the pivot table refresh to include it in the pivot.

When using the slicer(s) you can clear the current selections multiple ways:

• click on any other single entry to select it
• in the top right corner of the slicer box there is a funnel and "x", the "x" turns red when a selection is made. click on the red x to clear the selection
• right click the slicer and select "clear slicer" in the context menu

If you are interested in learning more about tables and pivottables, I can provide links to resources that will get you started.

Hi

I have just got back from work and I am starting to look at it. If I go to All Data, at the top is a list of some transactions and then below that are all the other transactions, which are not in the order they were originally entered.

Line 236 which was a line I entered before your modifications, doesn't appear anywhere?

If i go to report or lists, Brown is not recorded anywhere, I have no idea which line the inputter would enter a new line of info.

On the report page you have fiscal year 2018 & 2019, how do you add all the previous years? also how do i select all entries for one particular column and not show anything else? again no idea how pivot tables work or how to use them

I appreciate what you have done, however it is no good if no one knows how to use any of the functions.

As i stated at the beginning, The master sheet needs to stay as it is, for the inputter to just enter the data, so any ideas you think will achieve the end goal, I am more than willing to listen to you.

Regards

Stephen