Excel - COLUMN Function Problem - Expert Solution

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
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
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc