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

NewUzer

New Member
Joined
Nov 5, 2020
Messages
8
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

NewUzer

New Member
Joined
Nov 5, 2020
Messages
8
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
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-Adata 1
Pro-Bdata 2
Pro-Cdata 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-APro-BPro-C
other data aother data bother 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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

New Member
Joined
Nov 5, 2020
Messages
8
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
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-APro-B
maybe unique "EntryID"? If that is necessary?
IDDateAmountPriceIDDateAmountPrice
E00042020/10/26282.0datadatadatadata
E00042020/10/19182.0
E00042020/9/28182.0
E00042020/9/10182.0
E00042020/8/31282.0
E00042020/8/6282.0
E00042020/6/162082.0
E00042020/6/9182.0
E00042020/6/5282.0
K18032020/6/52475.0
E00042020/6/4182.0
E00042020/2/26182.0
K06122020/2/62475.0
K18032019/7/251075.0
E00042019/6/10082.0
K11552019/5/221075.0
E00042019/5/6178.0
K36122019/5/6166.0
E00042019/4/12178.0
E00042019/3/14178.0
K06122019/2/212475.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.)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,261
Messages
5,600,587
Members
414,391
Latest member
Pandurang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top