Multiple sheets with same data arranged differently

Sorry I couldn't think of a shorter title that would be descriptive enough.

I've got a workbook with 5 worksheets. Three of them contain exactly the same data, just arranged differently. i.e. First sheet is sorted by last name, second sheet is sorted by state and third is sorted by birthday. The 4th and 5th sheets only show a small number of entries from the first three. Every month the workbook is saved as a PDF to be sent out to the members of the club. The data entry worksheet is the 1st, By Last Name sheet, which has 12 columns of info on each member. All of this information is duplicated on the other 2 with the exact same date just arranged differently. I've been copying and pasting and I've tried recreating the other worksheets after the By Last Name changes are made. But, two methods are time consuming.

Is there a way to link the twelve columns on By Last Name to the State and Birthday sheets? It should be noted that the State and Birthday sheets have a blank column between items. i.e. Arkansas, blank, Alabama and January, blank, February.

Thank you in advance,
Hozey

Hi Hozey and Welcome to the Board,

It would be nice to see a sample of your workbook so we know exactly what we are dealing with, but it sounds like a vlookup could work.

If last name is always the farthest leftmost column on all sheets, then doesn't matter the order in which everything is on the other sheets just the postioning of the name.

Example: We have sheet1 with all the names and the birth date is in column 12.

On sheet2 (or whatever sheet) the same name is in column 1 but now you want the birth date in column 2.

Formula in sheet2 =VLOOKUP(A2,Sheet1!$A$1:$L$25,12,0)

Take a look at the Excel help on Vlookup
Also, http://www.contextures.com/xlfunctions02.html

If your lookup values will not be in the farthest leftmost column then you could use the Index/Match method

HTH
Jeff

The Last Name is only the furtherest left column on the By Last Name sheet. Birthday and State are the first columns on their respective sheets. Looked at examples of Index/Match; that looks even more time consuming than copy/paste.

Here's a copy.

Since you do this once a month and the first sheet "By Last Name" is constant and all other sheets are mere duplicates then why not use a macro.

The attached worksheet contains a macro which will produce a couple of actions.

Creates two sheets - By State and By Birthday

Moves columns (BD/State) to column A and then sorts.

IMO this seems like the best solution with the information provided.

HTH

Jeff

Unfortunately Office 2008 for Mac doesn't support VBA. To convert to AppleScript would entail me learning a whole new application. The time involved for such a small task isn't justified. Thanks though.

Found a simple solution.
Copy cell(s) needed from sheet 1. Paste Special, Past Link to sheet 2.
This will work on worksheets and/or workbooks.
Hope someone else finds this useful.