[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

Sorting alphabetically across different sheets

I have 2 sheets. In Sheet1 names are entered in column A for use of the corresponding cell in Sheet2. In Sheet2 additional information is entered in column B. When adding or removing names from Sheet1 I would want to sort alphabetically. That's easy when sorting in Sheet2, but I would want to sort in Sheet1, but keeping column B in Sheet2 in the same row as the name it was entered with in column A.
Attached is a simple example before and after. Before is information entered, after is what I want it to look like by only sorting in Sheet1.

How do I do that
Before.xls
after.xls
0
Brasch
Asked:
Brasch
1 Solution
 
MarioAlcaideCommented:
Hello,

Maybe the following link will hjelp you, it's a similar case:

http://excel.bigresource.com/Track/excel-HucbZZMM/
0
 
Kannan KManager - EngineeringCommented:

Hi,

You will have to write a macro to read the content from Sheet1 and write into sheet2 and do the sorting thru macro. as of now you are referring the sheet 1 cells in sheet 2 cell. sheet 2 will not know that, the sheet 1 has done sorting.

KK,
0
 
BraschAuthor Commented:
@MarioAlcaide;
The formula in the link might work, but I simply do not completely understand what it does, so very hard to fit my needs

@Kannan;
problem is, that I have 35 sheets that I need to sort - I'd rather not have to sort all of them one after another. Anyway, what actions would that macro contain?

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SiddharthRoutCommented:
Brasch: The problem is the formulas in column A of Sheet 2.

If there were not formulas then you could have used the code below to sort the entire range of sheets.

Sub SortAll()
    Dim LastRow As Long, lastCol As Long
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Sheets
        LastRow = ws.UsedRange.Rows.Count
        lastCol = ws.UsedRange.Columns.Count
        
        colname = Split(ws.Cells(, lastCol).Address, "$")(1)

        ws.Range("A2:" & colname & LastRow).Sort Key1:=ws.Range("A2"), _
        Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
    Next
End Sub

Open in new window


Sid
0
 
BraschAuthor Commented:
Well, the problem in example isn't (or shouldn't be) Sheet1 - it's basically a list of names - that could just as well be entered into sheet2, sheet1 is just for user-friendlyness, since the rest of the sheets also contain relevant data aside from the names. The problem arises in the "real world" when applying to the rest of my sheets. I somehow need to attach an entire row to the first cell in the row
0
 
SiddharthRoutCommented:
If you have formulas referring to some other cell then sorting them will give you some undesired results. If only the 1st column in 2nd or the 3rd or any other sheet is connected to the 1st column of another sheet then it will create a problem.

Try and run the code that I gave and see what happens.

Sid
0
 
BraschAuthor Commented:
It's getting quite late here, I'll give it a go after some sleep
0
 
BraschAuthor Commented:
well, the code basically works the same way as the sort a-z, not much help. Considered putting all the sheets together in one "Enter Data" sheet, but since I'm using Office 2003 I'm running out of columns
0
 
SiddharthRoutCommented:
Brasch: Can I see the actual Excel File?

Sid
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now