Keeping Cell data together after being sorted on a separate sheet

[Raindrop_001]

How do you keep a row of cells together when the first cell in the row is being sorted by a sort function on a separate Page. currently, when the NY, PA & OH cells are sorted on page 1, the data next to them is mixed.

 

[cmowla]

Welcome to Mr. Excel!

So is this how you have it? You had the original values in cells B3:D5 in sheet 1. You then went to sheet 2, clicked on cell B3, pressed the =, went back to sheet 1, selected cells B3:D5 and pressed enter . . . to give you the formula in cell B3 in sheet 2 to be:

='1'!B3:D5

If so, you will find that if you recalculate sheet 2 (shift and F9 keys), it will update changes that you did to sheet 1.

If this is what you want, but you don't want to keep doing this manually,

1. Right click on sheet 2's tab at the bottom.
2. Select View Code
3. Copy the following code into that window.
4. Close that window.
5. Save your Excel Workbook as with the file extension .xlsb instead of .xlsx.

Private Sub Worksheet_Activate()
ActiveSheet.Calculate
End Sub

 

[Raindrop_001]

Welcome to Mr. Excel!

So is this how you have it? You had the original values in cells B3:D5 in sheet 1. You then went to sheet 2, clicked on cell B3, pressed the =, went back to sheet 1, selected cells B3:D5 and pressed enter . . . to give you the formula in cell B3 in sheet 2 to be:

='1'!B3:D5

If so, you will find that if you recalculate sheet 2 (shift and F9 keys), it will update changes that you did to sheet 1.

If this is what you want, but you don't want to keep doing this manually,

1. Right click on sheet 2's tab at the bottom.
2. Select View Code
3. Copy the following code into that window.
4. Close that window.
5. Save your Excel Workbook as with the file extension .xlsb instead of .xlsx.

Private Sub Worksheet_Activate()
ActiveSheet.Calculate
End Sub

No, didnt Select B3:D5 on sheet 1.. I Selected the single cell that "NY" Was in. Which was in cell B3 on Sheet 1. Then i clicked B3 on Sheet 2 and typed "=Sheet1!B3" And then PA is in C3. And OH is on D3. etc. Those 3 cells are sorted on Sheet 1. When those are sorted on Sheet 1, it sorts on Sheet 2 as well. However the data to the right of those 3 "NY, PA, and OH" Cells arent sorted on Sheet 2 even though they're sorted on Sheet 1 fine. Im not sure how to ask this question, but it seems like a very simple problem. Im surprised i cant seem to find an answer anywhere..

 

[Alex Blakenburg]

Unfortunately I think you have a spreadsheet design issue.
We need to know how the sheet 2 cells relying on sheet 1 are linked and it sounds like it is just
Sheet 2 - cell contains the formula "=Sheet 1 cell"

We then need to know how the data in the other columns of Sheet 2 are being populated.
Anything using a Vlookup or other lookup, should be fine and anything with a formula that only relates to cells on the same row should be fine.
Any typed in data or formulas referencing different rows will all be corrupt.

If you want us to have a look we need an XL2BB of both sheets (sample set of rows including headings) or a link to a shared file via something like dropbox, onedrive, google drive etc.

 

[Raindrop_001]

Unfortunately I think you have a spreadsheet design issue.
We need to know how the sheet 2 cells relying on sheet 1 are linked and it sounds like it is just
Sheet 2 - cell contains the formula "=Sheet 1 cell"

We then need to know how the data in the other columns of Sheet 2 are being populated.
Anything using a Vlookup or other lookup, should be fine and anything with a formula that only relates to cells on the same row should be fine.
Any typed in data or formulas referencing different rows will all be corrupt.

If you want us to have a look we need an XL2BB of both sheets (sample set of rows including headings) or a link to a shared file via something like dropbox, onedrive, google drive etc.

Maybe i should have you check it out..

Sort Test.xlsx

Sheet1 PA,10,2 OH,5,4 NY,2,6

docs.google.com

It seems like such a simple issue and I'm not sure how to explain myself without it sounded confusing.. I just want

 

[Raindrop_001]

Accidentally posted the reply too quick haha.. But I just want the Data to not mix on Sheet 2 as sheet 1 is sorted. Maybe you could check it out. i have that Google Sheets link if that helps..

 

[Alex Blakenburg]

Where are these numbers in sheet 2 coming from ?
Anything other than a link to the same row on sheet1 or the same row in sheet2 or a vlookup is not going to work.

Book1
	A	B	C	D	E
1
2
3		NY	8	3
4		PA	0	11
5		OH	6	5
6
Sheet2

Cell Formulas
Range	Formula
B3:B5	B3	=Sheet1!B3

 

[Raindrop_001]

Where are these numbers in sheet 2 coming from ?
Anything other than a link to the same row on sheet1 or the same row in sheet2 or a vlookup is not going to work.

Book1
	A	B	C	D	E
1
2
3		NY	8	3
4		PA	0	11
5		OH	6	5
6
Sheet2

Cell Formulas
Range	Formula
B3:B5	B3	=Sheet1!B3

Those numbers are manually entered. Those numbers have nothing to do with sheet 1..

 

[Alex Blakenburg]

In that case there is no way of linking some columns to sheet 1 and keeping them in line with the manually entered data.
Typically sheet 2 would have the key column manually entered and the do a lookup to Sheet1 to get related data and then manually enter your data.
If you sorted Sheet1 in that instance, Sheet2 sort would not change but keep all the data in the original order and correctly aligned.

 

[Raindrop_001]

In that case there is no way of linking some columns to sheet 1 and keeping them in line with the manually entered data.
Typically sheet 2 would have the key column manually entered and the do a lookup to Sheet1 to get related data and then manually enter your data.
If you sorted Sheet1 in that instance, Sheet2 sort would not change but keep all the data in the original order and correctly aligned.

Dang.. I would have figured there would be some kind of way to group those columns together. So for instance, C3:D3 would be grouped with B3. So when B3 moved, C3:D3 moved with it.. thanks for taking the time to help regardless.