Question description:
This user has given permission to use the problem statement for this
blog.
My columns A and B on sheet 2 of a document get derive their values from columns A and B on sheet 1. I currently have sheet 2 sorted by column A first, then B (alphabetical on both). However, whenever I sort sheet 1 to try to get it in the same order it screws up columns C-F on sheet 2. The rows do not stay together, and therefore my client information on sheet 2 is shifted up or down a few rows. How do I ensure that when the rows change order when sorting that each row moves as a unit, not just the first two columns moving and the rest being out of order?
Solved by Z. Y. in 27 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
20/07/2018 - 07:34
Hi welcome!
User
20/07/2018 - 07:34
Good afternoon!
User
20/07/2018 - 07:34
Does my problem description make sense?
Excelchat Expert
20/07/2018 - 07:34
You have a question on sorting between 2 sheets-correct?
User
20/07/2018 - 07:34
Yes exactly
Excelchat Expert
20/07/2018 - 07:35
Can you upload the sheet-so we can take a look at the setup?
User
20/07/2018 - 07:35
Sadly no, the information is confidential
User
20/07/2018 - 07:36
column A on both sheets is my list of therapists. Column B are the clients themselves
Excelchat Expert
20/07/2018 - 07:36
OK is that sheet 1 or 2?
User
20/07/2018 - 07:36
sheet 1 is where I tally off the documents and forms the clients complete. Sheet 2 is basic contact information
User
20/07/2018 - 07:36
On both sheets columns A and B are the same. Columns C-F on them are different
Excelchat Expert
20/07/2018 - 07:37
It looks like you are doing some counts on sheet 1 and really want the attributes (names, other) from sheet 2.
User
20/07/2018 - 07:37
For sheet 2 I used the function =Sheet1!A2 (and so on for each box) to move the information from sheet 1's columns A and B to sheet 2
User
20/07/2018 - 07:38
Basically I need for my rows on sheet two to stay locked together when I sort sheet 1
User
20/07/2018 - 07:38
does that make sense?
Excelchat Expert
20/07/2018 - 07:38
I think you are processing info on sheet 1 and the rows are shifted.
Excelchat Expert
20/07/2018 - 07:39
You likley can not rely on simple = SHEET2!A1 formula
User
20/07/2018 - 07:39
Yes that sounds right. When I "Sort" sheet 1 columns A and B on both sheets move. The rows on sheet 1 move as units (i.e. entire rows move and are placed in the correct order)
Excelchat Expert
20/07/2018 - 07:40
You are likely going to need a LOOKUP formula to bring in the attributes (name, other) from sheet 2.
User
20/07/2018 - 07:40
whereas on sheet 2 only columns A and B are sorted and the rest of my data stays in place
User
20/07/2018 - 07:40
I don't know what that means
Excelchat Expert
20/07/2018 - 07:41
What does a single row in SHeet 1 look like?
Excelchat Expert
20/07/2018 - 07:41
Can you create an example in the view sheet here
User
20/07/2018 - 07:41
Yes one sec
Excelchat Expert
20/07/2018 - 07:41
Very hard to grasp thru discussion only
User
20/07/2018 - 07:41
agreed
User
20/07/2018 - 07:42
row 1 will be my headers by the way
Excelchat Expert
20/07/2018 - 07:42
Lets try a couple of rows in each of the view sheets
User
20/07/2018 - 07:45
So is there a better way than the =Sheet1!A to get my information from sheet 1 to 2?
Excelchat Expert
20/07/2018 - 07:47
Where does the process start? Looks like sheet 2 is a basic directory
User
20/07/2018 - 07:48
Process starts on sheet 1, that's where I enter all of my new clients in at
User
20/07/2018 - 07:48
And then I move to sheet 2 to add contact information for each new one
Excelchat Expert
20/07/2018 - 07:48
ok
User
20/07/2018 - 07:49
Is there a way I can just tell excel to move rows as a unit on sheet 2? Does that make sense?
User
20/07/2018 - 07:50
Currently, I can "sort" sheet 2 and everything moves as it should. But when I sort sheet 1 it screws up
Excelchat Expert
20/07/2018 - 07:51
Yes, picks up the cell in new position.
Excelchat Expert
20/07/2018 - 07:52
I think the only simple EXCEL way is before you sort sheet 1, SAVE sheet2 as VALUES
User
20/07/2018 - 07:52
What do you mean by that?
Excelchat Expert
20/07/2018 - 07:53
You dot really need sheet2 to change once you have the info saved-correct?
User
20/07/2018 - 07:54
correct
Excelchat Expert
20/07/2018 - 07:55
In EXCEL you can COPY/PASTE SPECIAL- VALUES- this removes formulas and keeps the data.
User
20/07/2018 - 07:55
I only did the =sheet1 thing because I had entered 120 therapists/clients on sheet 1 and didn't know of another way to copy them over to sheet 2
User
20/07/2018 - 07:55
yes that sounds like what I need
Excelchat Expert
20/07/2018 - 07:55
That is a simple method.
User
20/07/2018 - 07:55
So then from here on when I add a new client on sheet 1 I need to add them on sheet 2 also?
Excelchat Expert
20/07/2018 - 07:56
Yes, just copy A and B as values not formulas
Excelchat Expert
20/07/2018 - 07:57
Then to check you have all you can do a copy unique columns A and B from Sheet 1 to make sure you have all unique combos of Therapist and Client.
Excelchat Expert
20/07/2018 - 07:58
From the main menu is the DATA function, that has a functon REMOVE duplicates.
Excelchat Expert
20/07/2018 - 07:59
You can use that to verify all unique combos of Therapist and Client.
Excelchat Expert
20/07/2018 - 08:00
to make sure you did not miss anyone.
User
20/07/2018 - 08:00
I believe that worked perfectly!
User
20/07/2018 - 08:00
Thank you very much~
Excelchat Expert
20/07/2018 - 08:00
Ok, thanks for stopping by.
User
20/07/2018 - 08:00
Thank you
Excelchat Expert
20/07/2018 - 08:00
Please stop back if you need any other EXCEL formula help.
User
20/07/2018 - 08:01
Will do for sure
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
