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?
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

NewUzer

New Member
Joined
Nov 5, 2020
Messages
8
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Sorry, it seems the forum didn't like me adding so much spaces for my examples, now the formatting is all messed up. Please wait a little while I try to fix this.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,827
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
If you want to post an example of a range try using the boards XL2BB addin, The XL2BB icon in the Reply window is a link to the download and instructions or click the XL2BB in my signature block.
 

NewUzer

New Member
Joined
Nov 5, 2020
Messages
8
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Couldn't edit my post or fix it in any other way. Thus I prepared all the examples in correct formatting as a picture:

examples2.jpg
 

NewUzer

New Member
Joined
Nov 5, 2020
Messages
8
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
See post #3

Yeah, thank you, I'll keep that in mind for next time!

Atm, I don't have the rights to edit in the first place it seems (less than 5 posts in this board). Anyways, the examples are just a visualization, there are no formulas. I think this if doable will have to be vba.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,827
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

If you use the Addin people can copy and paste the data into Excel so that they can test, they can't do that with images and so it will normally lower the number of responses that you get as most won't retype the data.

BTW, you don't need to edit. You just do a new reply (you will find once you have permission to edit you only have a 10 minute window anyway so it is quite normal to do it as a reply).
It is only an advantage to do an edit when you only made the one post (the original question), once you (or anyone else) make the first reply it takes it off the Unanswered threads list.
 

NewUzer

New Member
Joined
Nov 5, 2020
Messages
8
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Got you! Went ahead and create such a usable table:

Sheet 1Sheet 2
Pro-Adata 1Pro-APro-BPro-C
Pro-Bdata 2data adata bdata c
Pro-Cdata 3
Then e.g. sorting Sheet 1 formatted table
Sheet 1Sheet 2
Pro-Cdata 3Pro-CPro-APro-B
Pro-Adata 1data cdata adata b
Pro-Bdata 2
Another example:
Sheet 1Sheet 2
Pro-Adata 1Pro-APro-BPro-C
Pro-Bdata 2data adata bdata c
Pro-Cdata 3
Then adding (bold font) or inserting (italic font) new row
Sheet 1Sheet 2
Pro-Adata 1Pro-APro-BPro-DPro-CPro-E
Pro-Bdata 2data adata bdata ddata cdata e
Pro-Ddata 4
Pro-Cdata 3
Pro-Edata 5
Please note, Sheet 2 looks like this in detail (about 4-5 excel columns for each Product):
Pro-APro-Betc…
data a1data a2data a3data a4data a5data b1data b2data b3data b4data b5etc…
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,827
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Not sure about your last table but the others just look like a pastespecial transpose which can be simply done using VBA (although I don't know why the Data part in the cells changes from numbers to letters at the end).

The code below
VBA Code:
Sub NewUzer()
    Sheets("Sheet2").UsedRange.Clear
    Sheets("Sheet1").Range("A2:B" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row).Copy
    Sheets("Sheet2").Range("A2").PasteSpecial Paste:=xlPasteAll, Transpose:=True
    Application.CutCopyMode = False
End Sub
Turns
Book2
AB
1
2Pro-Adata 1
3Pro-Bdata 2
4Pro-Ddata 4
5Pro-Cdata 3
6Pro-Edata 5
Sheet1

into
Book2
ABCDE
1
2Pro-APro-BPro-DPro-CPro-E
3data 1data 2data 4data 3data 5
Sheet2

and
Book2
AB
1
2Pro-Cdata 3
3Pro-Adata 1
4Pro-Bdata 2
Sheet1

into
Book2
ABCDE
1
2Pro-CPro-APro-B
3data 3data 1data 2
Sheet2
 

NewUzer

New Member
Joined
Nov 5, 2020
Messages
8
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Not sure about your last table but the others just look like a pastespecial transpose which can be simply done using VBA (although I don't know why the Data part in the cells changes from numbers to letters at the end).

Many thanks for your code!

There may be a slight misunderstanding, the data in Sheet 1 and Sheet 2 is completely different, that is also why one is named with numbers and the other with letters. These are two types of data, but for the same Product.

So, the Product Name (e.g. Pro-A, Pro-B, etc.) should stay the same, and the data in Sheet 2 just moves with it. That is also why I haven't been able to find a solution yet.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,450
Messages
5,596,221
Members
414,046
Latest member
mbeutler1203

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