Yes.My original data source already is different data sets, that's why the both tables are totally not connected to each other.

The real data on my project is 25 country(orders),87386 rows vs 41 country(parts),19005 rows. They have many different criterias are not similar too. For example, country Brazil in orders table,having customer A,B,C but in parts table may have only customer D or customer A,C.

I can not make any changes to the raw file to make them similar data.

Just wonder if this is the case, which is the suitable method to solve the issue? Probably the merging method is not working for this case?

If can work with the slicer it would be a perfect way, as i really wish everything can be done by slicer. But for my case so complicated, not sure how? 

Do appreciate again if you help. 🙂

If there is no relationship between the tables, then what are we talking about 🙂 ? the Parts/Orders division does not make sense in this case.

You can still merge them, and create pivot tables to display the Net Amounts for parts and orders, the pivot tables will have the same source ( the merged table) and they can be controlled with the same slicers.

There are no relationship between the tables, but the users want to compare the results of parts table and the orders table for the 4 categories(Customer Name, Country, Platform and Quarter) even though they are not connected to each other. They want to analyse during the period the percentage of the certain product by Parts/Orders. 

You mean not make sense is it this case? Happened with Parts table have Customer A, but Orders table do not have Customer A? Then for this row data value is empty.Each of a table having the data that another table don't have.

You can Append table2 data to table 1 data, check the attached file. In each query, I added a custom column to indicate the Data Source (orders-Parts),

then in the pivot tables I added a report filter to show in the first pivot table only data from Orders, and in the second pivot only data from Parts. This way, they have the same pivot cache and they can be controlled with the same slicers.

For your formulas, all you have to use is GetPivotData formula, see this article: excel-getpivotdata-function

Hi Catalin,

Thanks for suggesting the append method for slicer part.

GetPivotData is fix column right? but my rows and columns might change position accordingly bases on the data option selected.It will not get the correct result also.

Did you checked the formula from cell G2?

There are no cell references other than $A$17 and $A$5, and these are the pivot tables first cells (a pivot table location is determined by its first cell position)

=GETPIVOTDATA("Net Amt USD - Acct Rate",$A$17,"Order Line Trans Quarter","2015 Q 1","Platform","AXO ")

The code for the Platform is typed into formula (same for Quarter), you can replace it with a cell reference, if you need to. But all these are explained in the link I sent you, I have the strong feeling that I'm talking to myself, you did not read the article from the link I sent about GETPIVOTDATA, right? 🙂

Take your time to analize the links I sent, all you need to know is there.

Cheers,

Catalin

Hi Catalin,

I did read the article, and tried with the example, that time i was not so understand what i am doing, i do not notice that i did not change the position name, and keep assume that was not working for my case, really so bad 🙁 so sorry~

Now i able to made it work by changing the two variables changing according by correct cell reference. Really tons of thank you for this formula, after many rounds of hard work, finally i know how to use this formula well, really glad that this issue solved, been struggling for this so long time, and it just solved by this simple formula only 🙂

Before come to an end, and it almost to an end, i am still having a question on refreshing data issue, 

How to refresh pivot table in a protected sheet?Other than manually unprotect, refresh and re-protect. I saw some of people using VBA and assign macro to the button.But i do not want to create a button to let users refresh them. I will refresh on my side and send them the file. Which method can make it other than VBA?

Many thanks again!

Unfortunately, you have to use vba for refresh on protected sheets, or manually unprotect before refresh.

Hi Catalin,

I tried to put below code on vba  “ThisWorkbook” page, but do not know how to assign the macro.Must assign this macro to a button?

Can i make it like when opening the tab of worksheet, then the pivot tables on the active sheet will auto refresh by the macro code? 

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim pt As PivotTable, bPiv As Boolean

If Sh.Type <> xlWorksheet Then Exit Sub

For Each pt In Me.PivotTables
Me.Protect Password:="1234", UserInterfaceOnly:=True
pt.RefreshTable
Next pt

End Sub

The code you posted is just protecting the sheet, without unprotecting the sheet before refresh. More, the code is protecting the sheet before refreshing the pivot :), that is weird. The corect sequence is unprotecting the sheet, refresh all pivot tables, then protect the sheet back. Try this:

Hi Catalin,

The code i just copied from other's online solution, do not know the logic was not correct 😛 Thanks for your code.

1)As i know if want to assign a macro have to use Sub Functionname()? If using Private Sub can assign? Many times i saw a private sub function code but i do not know how to use it.

2)Locate vba code inside "ThisWorkbook" means it will run the macro in every sheets when i open the file? If i put in module it should be ok also right?

3)And is it a must to assign to a button in order to run this macro?So far i only know to assign macro to a button on module page.

I do not want to allow users to click the refresh button. I am thinking to assign macro to a button on a new sheet and make the worksheet hidden, do you think is a proper way to make it?

Seeking for your opinion, thanks!

You should add the code to the personal.xlsb workbook, and add an icon to your toolbar, with that code, this way the code will be available only on your computer.

Here is a link that will teach you how to do this: create-a-personal-macro-workbook-personal-xlsb

The code should be:

Wow..bravo function! It works like charm.Very nice tutorial, i learned a lot throughout your teaching and those links you provided has upgraded my skills at my work pace.

Really really thousand of thanks to you Catalin,for your patient to guide me along the way to complete my project well now,do appreciate your helps very much, after this i am interested to join your online course, but kind of budget issue, i will consider to take the course in future. 🙂

Once again, thank you very much! I will recommend online training hub to others 🙂

May you always be well and happy 🙂

Best Regards,

Hooi Thin

You're welcome 🙂

Hi Catalin,

I am back to here again to seek for your help as i am facing the issue for the GETPIVOTDATA that you teached me last time.

Last time my case was using the formula which grab the row label and column label to get the correct position with the data.

for example =GETPIVOTDATA("Net Amt USD - Acct Rate",$A$17,"Order Line Trans Quarter","2015 Q 1","Platform","AXO ")

But now i am doing slightly complicated ,after the filtering, the column label i still can get the quarter name ,but row label it was not fixed, as it have thousand customers and it always change accordingly.

For this case, how i going to grab the correct value of the position? If using cell reference it can get the correct value but appear with the position issue(please refer to my attachment)

Appreciate if you can help me again.Thanks!

Regards,

Hooi Thin