Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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