New to forum.. Want to do an advanced sorting option keeping row data together.

[Big_D_in_Va]

I'm new to the forum here, so if this is asked somewhere else, let me know. Thanks for any help!

I am trying to figure out how to keep data on the same lines connected when a sort is done.

This is complicated because the first half of sheet is CC'd from another sheet and second half of sheet is hard numbers and mathmatical formulas.

For example here is sample of data on Sheet 2
A1 = =Sheet1$A1, B1 = =Sheet1$B1, ..... AA1 = 1, BB1 = 2, CC = =(BB1-AA1)

I want to be able to sort Sheet 1 and have the hard data on Sheet 2 (AA1, BB1, CC1 etc) to stick with the copied data on sheet 2 (A1, B1, C1 etc)

I know I could a Vlookup, but that would get very large. Any other ideas?

[oeldere]

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

[Big_D_in_Va]

What I want is to be able to sort Column A on Sheet 1 and have the data on Sheet 2 columns D,E, & F stay with its sorted copied data on Sheet 2 columns A, B, C.

[oeldere]

use sheet 2 column A, B and C also as imput sheet.

Then you can use sheet 1 for presentaties (e.g. with a pivot table).

I made the pivot table in this case on sheet 2.

[Big_D_in_Va]

I thought of that. But I would have the same sorting problem on sheet 1. I made an adjustment to my sample sheet and re-attached it so you can see it.

[Big_D_in_Va]

I cannot put everything on one sheet because some information needs to be compartmentalized.

[oeldere]

I cannot put everything on one sheet because some information needs to be compartmentalized.

yes you can, and it is a good idea using excel, to have all information (data) on 1 sheet.

It makes it a lot easier to analyze.

[Big_D_in_Va]

I cannot put everything on one sheet because some information needs to be compartmentalized.

yes you can, and it is a good idea using excel, to have all information (data) on 1 sheet.

It makes it a lot easier to analyze.

Regardless, its not and we don't want it on the same sheet. Is there a way to tie the hard data to the copied data when its sorted?

[Simon3]

Does this help? See sheets 3 and 4. If it does I'll explain how I did it.

Should mention you need to use the slicer boxes to filter.

Simon3 - SampleBook.xlsx

[Big_D_in_Va]

I like that, but I am trying to avoid pivot tables as well. I'm trying to find a way to just use the two tables and just keep the hard data stuck to the copied cells.

[Simon3]

I combined all the data on sheet2 so I could split the data into the views you had set up. In your real workbook is there a place where all of this information is combined?

If the data is a changing number of rows i don't think you will be able use just formulas because there isn't a formula to add or delete rows (as far as I know). You could look into powerpivot to create a relationship between the data but you will need a matching unique ID column in each table.

Pivot tables are likely to be your best bet though since you can create relationships between the slicers and multiple tables, hold ctrl when clicking to select multiple filters, select the columns you want to display, etc.

[Big_D_in_Va]

Ok. I'll look at that closer.

No, there is no place that all data is in the same place. In my real workbook, just as the sample, there is seperate data on both pages that ties to the copied data. But to compartmentalize some of the information and simplify the viewing, we want to keep the sheets seperate.