Have sorting/filtering of formatted table apply to range on other sheet doable?

[NewUzer]

Hi,

I would like to ask whether it is doable to have a formatted Excel table on one sheet, a normal range (or maybe another formatted table as well if that is better??) on another sheet and have the sorting/filtering and any editing of the formatted table on the first sheet dynamically apply to the range on the second sheet?

Here is an example of what I mean:


Sheet 1 Sheet 2
Pro-A data 1 Pro-A Pro-B Pro-C
Pro-B data 2 data a data b data c
Pro-C data 3

Then e.g. sorting Sheet 1 formatted table

Sheet 1 Sheet 2
Pro-C data 3 Pro-C Pro-A Pro-B
Pro-A data 1 data c data a data b
Pro-B data 2


Another example:


Sheet 1 Sheet 2
Pro-A data 1 Pro-A Pro-B Pro-C
Pro-B data 2 data a data b data c
Pro-C data 3

Then adding (bold font) or inserting (italic font) new row

Sheet 1 Sheet 2
Pro-A data 1 Pro-A Pro-B Pro-D Pro-C Pro-E
Pro-B data 2 data a data b data d data c data e
Pro-D data 4
Pro-C data 3
Pro-E data 5


Both table and range have their own data which is different. Later on I use both together, and they physically cannot be in one table (would be hundreds of columns or more, so not feasible at all). The mentioned above should be dynamic, so that one day Sheet 1 is sorted, another day it is filtered, maybe some new rows are inserted, some are deleted, etc. Just normal work with a table. And all the while the range on Sheet 2 moves dynamically in sync.

Please note, Sheet 2 looks like this in detail (about 4-5 excel columns for each Product):

Pro-A Pro-B Pro-C

data a1 data a2 data a3 data a4 data a5 data b1 data b2 data b3 data b4 data b5 data c1 data c2 data c3 data c4 data c5


May I ask if something like this is possible?

 

[MARK858]

Nope, not understanding by your images what links the data in sheet 2 to the headers in sheet 1 so they move together.
The statement below is not clear, you need to give more detail (in words) on your exact requirement

and the data in Sheet 2 just moves with it.

 

[NewUzer]

Nope, not understanding by your images what links the data in sheet 2 to the headers in sheet 1 so they move together.
The statement below is not clear, you need to give more detail (in words) on your exact requirement

Sure. Sorry for the confusion!

The table in Sheet 1 has one product (e.g. Pro-A, Pro-B, etc.) for each row. Further to the right are more columns with data. So you would get something like this:

Sheet 1
Pro-A	data 1
Pro-B	data 2
Pro-C	data 3

Sheet 2 on the other hand has many ranges (if they better be tables, then that is okay as well, I atm don't know, so keeping them as ranges), which share the same Product (e.g. Sheet1Pro-A = Sheet2Pro-A). Each product is a column header. Different data is entered in the many rows below it. This looks like that:

Sheet 2
Pro-A	Pro-B	Pro-C
other data a	other data b	other data c

The link between both of these Sheets is the product name. To summarize, 2 sets of data for 1 product on 2 different sheets. When the 1 product moves on Sheet 1, then it and its data should move on Sheet 2 correspondingly, in other words, reflect the order of Sheet 1.

 

[MARK858]

reflect the appearance of Sheet 1.

But in one sheet the product name is in columns and in the other it is in rows so how can it reflect the appearance?

 

[NewUzer]

But in one sheet the product name is in columns and in the other it is in rows so how can it reflect the appearance?

En, that is exactly why I am stuck. Since it is not possible to bring them together, different data. But I need both to conduct product analysis.

Let me explain the different data sets:
Sheet 1:
Each product has an ID, ABC-Position, amount per box, storage location, etc. All stuff that has to be entered only once per row.

Sheet 2:
Each product has many many rows of sales data, like customer id, sales amount, sales price, etc. for a range of at least 2 years. So that will be hundreds if not thousands of rows. Clearly in this case it is not advised to try and enter those as columns on Sheet 1, let alone because of the issue with copying data into Excel alone.

Now you know the crux why I find it so difficult to solve this. I thought I surely can't be the first person to try and summarize all product & sales data in Excel, can I? Thus I figured there may be solutions out there. I thought about having different workbooks for different analysis tasks, but since I am dealing with about 600-1000 products at the minimun I only want to enter the data once into Excel.

Maybe have a vba which creates tables with dynamic names equal to the product name? And then per vba change the order, delete, sort, filter, insert, etc. on a horizontally basis.

Pro-A				Pro-B
maybe unique "EntryID"? If that is necessary?
ID	Date	Amount	Price	ID	Date	Amount	Price
E0004	2020/10/26	2	82.0	data	data	data	data
E0004	2020/10/19	1	82.0
E0004	2020/9/28	1	82.0
E0004	2020/9/10	1	82.0
E0004	2020/8/31	2	82.0
E0004	2020/8/6	2	82.0
E0004	2020/6/16	20	82.0
E0004	2020/6/9	1	82.0
E0004	2020/6/5	2	82.0
K1803	2020/6/5	24	75.0
E0004	2020/6/4	1	82.0
E0004	2020/2/26	1	82.0
K0612	2020/2/6	24	75.0
K1803	2019/7/25	10	75.0
E0004	2019/6/10	0	82.0
K1155	2019/5/22	10	75.0
E0004	2019/5/6	1	78.0
K3612	2019/5/6	1	66.0
E0004	2019/4/12	1	78.0
E0004	2019/3/14	1	78.0
K0612	2019/2/21	24	75.0
this table has the name "tbl_input_Pro-A"				the next table would be "tbl_input_Pro-B"
				each "block' may have 4 or 5 columns,
				maybe more in the future

My point why it should be synced/linked is that after I have entered both types of data in both Sheet 1 and Sheet 2, and I will most likely filter my Sheet 1 formatted table or edit it. And then it quickly becomes an arduous task to find the relevant source data on Sheet 2 (e.g. when I entered Pro-B it was on position 2, speak Row 2 on Sheet 1 and Column 2 on Sheet 2, but after filtering it has become position 45, so it is in Row 45 on Sheet 1, but still in Column 2 on Sheet 2, and that is not useful.)