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?

 

[NewUzer]

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]

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]

Couldn't edit my post or fix it in any other way. Thus I prepared all the examples in correct formatting as a picture:

 

[MARK858]

Couldn't edit my post or fix it in any other way

See post #3

 

[NewUzer]

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]

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]

Got you! Went ahead and create such a usable table:

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
Please note, Sheet 2 looks like this in detail (about 4-5 excel columns for each Product):
Pro-A					Pro-B					etc…
data a1	data a2	data a3	data a4	data a5	data b1	data b2	data b3	data b4	data b5	etc…

 

[MARK858]

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

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
	A	B
1
2	Pro-A	data 1
3	Pro-B	data 2
4	Pro-D	data 4
5	Pro-C	data 3
6	Pro-E	data 5
Sheet1

into

Book2
	A	B	C	D	E
1
2	Pro-A	Pro-B	Pro-D	Pro-C	Pro-E
3	data 1	data 2	data 4	data 3	data 5
Sheet2

and

Book2
	A	B
1
2	Pro-C	data 3
3	Pro-A	data 1
4	Pro-B	data 2
Sheet1

into

Book2
	A	B	C	D	E
1
2	Pro-C	Pro-A	Pro-B
3	data 3	data 1	data 2
Sheet2

 

[NewUzer]

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.