Feb 01 2020 08:51 AM - edited Feb 05 2020 09:32 AM
Hi everyone,
I am stuck in the different results from the same dataset in tables.
For example, when I filtered "Country" to "Australia", the other table showed “Japan", and the relevant tables didn't show corresponding dat
Seems they are not connected, but I don't know how to fix it.
Can anyone help? Thank you!
Feb 02 2020 07:16 AM
@Janice08 It's difficult to judge from a picture, but it seems that you just filtered the first table on "Australia" and "Shipped" and it hides many rows on that basis. The first row displayed is 14. In your second table that row happens to be related to "Japan".
Otherwise, upload a sample of your file so that we can have a closer look at it.
Feb 02 2020 09:57 AM
Feb 05 2020 09:32 AM
Hi,
Thank you for your reply. I just uploaded the file, could you please take a look at it? Thank you.
Feb 05 2020 09:33 AM
Feb 05 2020 11:31 AM
It's exactly what I expected. When you filter Australia and Shipped in the first table you simply hide the rows that are not Australia and Shipped. Then the rows in the other tables are also hidden. I guess you can make the summaries you want with pivot tables as demonstrated in the attached workbook.
Feb 05 2020 12:55 PM
This is unlikely to be of any immediate value to you but Office 365 has (or will soon have) a FILTER function. I have never been attracted to playing 'peek-a-boo' with data for precisely the reason you have found; that is, hiding rows messes up any other content on the sheet.
The FILTER function allows one to select a subset of data from a data table and display it anywhere one chooses without affected the source sheet.
[For those who are confused by the expression 'peek-a-boo', it is an infant game in which people or objects are hidden from a baby to amuse it when they reappear. It is part of cognitive development to realise that hidden objects still exist]
Feb 05 2020 01:50 PM
One from Microsoft is still available https://www.microsoft.com/en-us/p/peekaboo/9nblggh0cwlq?source=lp&activetab=pivot:overviewtab
Feb 05 2020 04:14 PM
Wow! And I was impressed by your knowledge of Excel.
That pales into insignificance compared to your ability to find this within the Microsoft portfolio!