I have a rather large Excel document with a lot of separate sheets in it. There is some info (email, last name, first name) that has to be the first three columns on each sheet. In order to be sure that no mistakes are made, I created a "Person" sheet that only contains those three columns. On the other sheets, I want to get the info from that Person sheet. I can get the email column in several ways (right now, I have =Person[Email] in that column), and then I use that to get the last name and first name.

So, there isn't a problem getting the data into those other sheets; but now, I want to sort by last name or first name (this is all in a table). What happens is that if I sort by Name, then you can see a flash where it re-orders the entire table, but then the =Person[Email] gets run again and the first column resets back to the order that is in the Person sheet. So this is even worse--not only can't I sort properly, but now the entire table is messed up because all of the data is in name ascending order except for the email addresses which are in the default order.

Is there a way to get the email column to replicate in all other sheets, but then stop updating so I can sort/etc? Thanks in advance


I haven't tested it on your situation, but typically I use paste special to accomplish this:

Select the e-mail addresses that result from your formulas and copy them.

Select the first cell from the same range you just selected.

Go to "Paste Special" and choose "Values"

That will paste the e-mail addresses in without the formulas and you can sort as you please.

  • That at least gets the data in there...the only other problem introduced is that if the table is sorted (say, by Last name) and then you paste in the emails, they will paste into the table in the order they were in the first sheet, not the order they should be in the table. This may be an unanswerable question... – user34029 Apr 13 '10 at 18:32

You could also try Paste Special>Paste Link if you want the data to remain connected to the original sheet.


You have two main options to solve your problem:

  1. If your data is rather static you can do the "Paste Special" and choose "Values" approach that Jordan 1GT suggested. This will solve your problem. The main problem with this approach is that you will have to duplicate your data and it won't be linked anymore.
  2. If on the other hand you want to centrally maintain the Email information and avoid duplication of data you may want try the following.

I assume you are already using the vlookup function to look the information such as name, last name up based on the email. Which is a must here. You can find more information on Vlookup in the Excel help.

The key problem here is to populate the email field in a way that it will sort properly with the other cells.

For your case, I would suggest against using an approach like =Person[Email] to populate the email field since this formula does not explicitly state in which order the Emails need to be shown, it just states that data from the email column will be used in any order. After any sorting this formula will run and reset all the email addresses.

One -not so elegant- solution here that I can come up with is to link directly to the email addresses. What I mean by this is the following.

On your "other" sheets

  • In the first cell of the emails column enter a simple cell reference to the Person sheet like =Person!A2, where A is the column with your emails on your Person sheet
  • Copy this reference down as far as you need to so that it will cover all the emails you need
  • Fill the rest of the table with vlookups or any other info/formulas that you need

If you sort this table the emails and everything else should also sort properly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy