creating sorted copy of table on second sheet

Hello all,
table on sheet 1 consists of (say) a row with names and a row with room
numbers and is sorted by name. (First row is header and not included in
sorting.)
I want to create a copy of this table on a second sheet containing the
same data sorted by room number which automatically re-sorts when the
data in the first table is changed.
I could think of a matrix function but could not find any.
Any idea?

--
email me: change "nospam" to "w.hennings"
Dipl.-Ing.(=M.Sc.Eng.) Wilfried Hennings c./o.
Forschungszentrum (Research Center) Juelich GmbH, MUT
<http://www.fz-juelich.de/mut/index.php?index=3>
All opinions mentioned are strictly my own, not my employer's.
0
7/10/2003 9:03:36 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
708 Views

Similar Articles

[PageSpeed] 40

How about just sorting by any column desired in the original table that is
named sortrange

Sub Sort()
mycol = ActiveCell.Column
End If
[sortrange].Sort Key1:=Cells(1, mycol), Order1:=xlAscending,
Orientation:=xlTopToBottom
End Sub



-- 
Don Guillett
SalesAid Software
Granite Shoals, TX
donaldb@281.com
"Wilfried Hennings" <nospam@fz-juelich.de> wrote in message
news:78aqgv80k40qmn3tdqh3837e0en0t0aitm@4ax.com...
> Hello all,
> table on sheet 1 consists of (say) a row with names and a row with room
> numbers and is sorted by name. (First row is header and not included in
> sorting.)
> I want to create a copy of this table on a second sheet containing the
> same data sorted by room number which automatically re-sorts when the
> data in the first table is changed.
> I could think of a matrix function but could not find any.
> Any idea?
>
> --
> email me: change "nospam" to "w.hennings"
> Dipl.-Ing.(=M.Sc.Eng.) Wilfried Hennings c./o.
> Forschungszentrum (Research Center) Juelich GmbH, MUT
> <http://www.fz-juelich.de/mut/index.php?index=3>
> All opinions mentioned are strictly my own, not my employer's.


0
Don
7/10/2003 1:25:42 PM
A different--nonsorting approach.

Select your range (include the last row of headers) and do
Data|Filter|Autofilter.

Then instead of sorting, you can just filter on your favorite field.  Use the
dropdown arrow and you can show just the values you want to see.

It might be sufficient.

Wilfried Hennings wrote:
> 
> Hello all,
> table on sheet 1 consists of (say) a row with names and a row with room
> numbers and is sorted by name. (First row is header and not included in
> sorting.)
> I want to create a copy of this table on a second sheet containing the
> same data sorted by room number which automatically re-sorts when the
> data in the first table is changed.
> I could think of a matrix function but could not find any.
> Any idea?
> 
> --
> email me: change "nospam" to "w.hennings"
> Dipl.-Ing.(=M.Sc.Eng.) Wilfried Hennings c./o.
> Forschungszentrum (Research Center) Juelich GmbH, MUT
> <http://www.fz-juelich.de/mut/index.php?index=3>
> All opinions mentioned are strictly my own, not my employer's.

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
7/10/2003 11:08:02 PM
I find it easier to keep my input in one spot and produce different sorts when I
need them.  I think that it's too difficult to get things like you want to work
100% of the time (my opinion only).

I'd either have a macro that would sort the data in place, use autofilter, or
generate a new worksheet (sorted in the correct order) whenever I needed it.



Wilfried Hennings wrote:
> 
> > Wilfried Hennings wrote:
> > >
> > > Hello all,
> > > table on sheet 1 consists of (say) a row with names and a row with room
> > > numbers and is sorted by name. (First row is header and not included in
> > > sorting.)
> Sorry, should read:
> table on sheet 1 consists of (say) a COLUMN with names and a COLUMN with
> room numbers and is sorted by name. (First ROW is header and not
> included in sorting.)
> 
> > > I want to create a copy of this table on a second sheet containing the
> > > same data sorted by room number which automatically re-sorts when the
> > > data in the first table is changed.
> > > I could think of a matrix function but could not find any.
> 
> Thanks for the responses, however your suggestions didn't produce what I
> need. Maybe i didn't make my question clear enough, maybe caused by the
> typo corrected above.
> 
> First workaround: Just mirror the contents of sheet1 on sheet1
> (A2=sheet1!A2, B2=sheet1!B2, ...) and do a manual sort on this.
> Could also be done by a macro. But neither is fully automatic.
> 
> Second is a workaround which does most but not all what I want. First
> problem is that VLOOKUP needs a coherent table so I have to first create
> the numbering column A and the rank column B and also mirror the two
> columns of sheet1 in sheet2 columns C and D. Second problem is, if 2
> persons share one room, the rank function returns the same rank value
> twice and the following rank value does not occur so the room list has
> empty cells and the second person using that room is not listed.
> 
> sheet2 contains:
> 
> row1 = headers
> A2 =ROW(A2)-1
> A3 =ROW(A3)-1
> ...
> B2 =RANK(sheet1!A2;sheet1!A2!A30;1)
> B3 =RANK(sheet1!A3;sheet1!A2!A30;1)
> ...
> C2 =sheet1!A2
> C3 =sheet1!A3
> ...
> D2 =sheet1!B2
> D3 =sheet1!B3
> ...
> E2 =VLOOKUP($A$2:$A$30;$B$2:$D$30;2;FALSE)
> E3 =VLOOKUP($A$2:$A$30;$B$2:$D$30;2;FALSE)
> ...
> F2 =VLOOKUP($A$2:$A$30;$B$2:$D$30;3;FALSE)
> F2 =VLOOKUP($A$2:$A$30;$B$2:$D$30;3;FALSE)
> ...
> 
> --
> email me: change "nospam" to "w.hennings"
> Dipl.-Ing.(=M.Sc.Eng.) Wilfried Hennings c./o.
> Forschungszentrum (Research Center) Juelich GmbH, MUT
> <http://www.fz-juelich.de/mut/index.php?index=3>
> All opinions mentioned are strictly my own, not my employer's.

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
7/11/2003 8:43:26 PM
Reply:

Similar Artilces:

Problems with creating a newsletter
I chose the form "Newsletter - email". I have created a 3 page newsletter. Now, I have NO IDEA how to send it out as an email. I do not want to send it as an attachment. I cannot figure out which "save as" format or what I need to do so that i can email this newsletter. Is there a website that goes through how to do this? Am I correct when I say that it has to be html in order for me to send it as email? That is not one of the options. Any help you give me would be greatly appreciated. Thank you, Markis www.adreamforabetterworld.com ...

PA Misc. Log Tables
Hi, How can I find the difference between Billed and Un-billed Miscelleneous Logs? Where Can I see Un-billed Misc. Logs and which tables. Hari I have a spreadsheet with the PA tables listed in it. Send an email message to charles.zarzour@intergraph.com and I will send you the spreadsheet. I do an excel spreadsheet for each Dictionary. Makes it easy to lookup tables. "Hari" wrote: > Hi, > How can I find the difference between Billed and Un-billed Miscelleneous > Logs? > Where Can I see Un-billed Misc. Logs and which tables. > > Hari > > Can I ...

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

auto sort?
Is there any way to make a dated entry insert itself in the worksheet in the correct order without doing a sort operation, or inserting a new row? would be a big help for my reservations workbook thanks D You could try the worksheet change event: Private Sub Worksheet_Change(ByVal Target As Range) <<Sort Code>> End Sub This should re-sort your selection everytime the worksheet changes. Hope this helps Sunil Jayakumar "Dave" <post@site.com> wrote in message news:%23czt38POFHA.2132@TK2MSFTNGP14.phx.gbl... > Is there any way to make a dated entry insert ...

Sort by Credit Card Type on EDC Detail Report
What do I have to do to get my EDC Detail Report to show different credit card types, like Visa, MC or Amex. Now under Tender Type, it just shows Credit card...well...I kinda knew that. How do I fix it? add tender types for each credit card type rather than just "credit card". then z out. all transactions after this change will reflect what you want in the reports. "tl" <tl@discussions.microsoft.com> wrote in message news:A54EEC95-208C-4962-A089-84B22A67632A@microsoft.com... > What do I have to do to get my EDC Detail Report to show different credit >...

VBA to creating autotext entries or quickparts in different catego
I have a VBA application that basically allows people to easily create autotext entiries, move them between machines and use them making comments on assignments. Currently it operates in EXACTLY the same way in Word 2003 and 2007 (using userforms) and I want to keep that as long as possible. You can see the application at http://emarking-assistant.baker-evans.com and either the screen image or the video demos will give you an idea of what I am doing Currently I store all the comments in a long list of autotext entries that is displayed in a field with the value of the entr...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Multiple copies of email in "Forward to"contact
HIe, I am running Exchnage 2003. My CEO has a conatct in our organisation that refer to his blackberry contact. I have set the forwarding to also keep a copy in his mailbox. What happens is that three copies of his forwarded email hit his blackberry account! Only one hits his internal mailbox. What I ahve determined is that the front end Exchange server seems to be creating the additional copies. Is this how its meant to be (don think so cos its such a nuisance and my CEO keeps complaining about it). How do I tell the front end server that this guy's mail server is the one that...

Opening pub files created with older Publisher versions #2
I have just upgraded to Publisher 2003 from 2000 and am having trouble with pub files sent to me for our chuch newsletter which is using Publisher 97 The text is not wrapping around graphics boxes. Can I fix this? I really don't want to go back to Pub 200 Thank Richard this is caused by Publisher 97 not been printer independent. Even if you went back to Publisher 2000, unless you have the identical font versions and printer driver you would have issues with formatting. It sounds like the person sending you the file has a garbage HP inkjet printer. Get them to install the HP5P laser p...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

printing multiple copies of Sigel GmbH
I have made a tent card in a small size of 5.588cm x 4.191cm for a swing tag, using Sigel GmbH in the "other labels" section. It prints one copy perfectly, leaving room on the page for at least about another 5 copies (which is what I want), but when in the print page section it doesn't give me the option to print multiple copies on the page. This will be an enormous waste of paper if I have to print the number of copies individually on each page. HELP! -- candere What is the product number for this label? I cannot find this size label on my 2007 version. -- Mary Sau...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Copying dbx files from a CDR back into Outlook
My computer was recently hijakced and I had to reinstall everything. I did however save all of my folders in Outlook onto a CD Rom and I do not know how to transfer them back so I can use them. Also I had an animated version of outlook where you could choose loads of diffent stationery and I cannot seem to find that upgrade anaywhere. Can you help me? Tee <tsahai33@hotmail.com> wrote: > My computer was recently hijakced and I had to reinstall > everything. I did however save all of my folders in > Outlook onto a CD Rom and I do not know how to transfer > them back so...

how to make macro work even a sheet is hidden.
hello there I have macro assigned to a button in Sheet 1 which goes to sheet 2. Press a button to Refresh and then PRINT preview It does work in normal state. I do not want the user to see the Sheet 2. I hid sheets from Format-sheet-hide. but the macro is not working when sheet 2 is hidden. how to overcome this error "can't execute code in break mode" the macro code is Sub Print_Preview() Sheets("PaySlips2009-10").Select Application.Run "'Latest 2009Payslip.xls'!Sheet2.HURows" ActiveWindow.SelectedSheets.PrintPreview ...

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

Unable to sort on customer CRM 4.0
Hi When you try to change the campaign response views to have customer as sorting CRM gives an error. You can't even click on customer column to sort when it's now customer that is default sorting. Is this a bug ? same here. I think its a bug "Help needed" wrote: > Hi > > When you try to change the campaign response views to have customer as > sorting CRM gives an error. You can't even click on customer column to sort > when it's now customer that is default sorting. > > Is this a bug ? > > > > ...

Creating Text Box in Publisher 2007 Crashes the Application
Hello, we have a clean install of Publisher 2007 under Windows XP SP2, and when we try to create a text box in a document, (both an existing document and a blank document), publisher crashes. Office is fully patched. I ran Office Diagnostics from the help menu and no problems were found and the issue persists. Any thoughts on how to resolve the issue? Thanks, Syd See if selecting a different printer as default helps. How to view error signatures if an Office program experiences a serious error and quits http://support.microsoft.com/kb/289508/en-us -- Mary Sauer MSFT MVP http://of...

Creating a Macro to Delete Commas #2
I have an excel file that the size will varry. I need a macro that will check all the fields for a comma. If there is one I would like to get rid of it. Does anyone have any idea how to do this? I have no idea and I have been assigned this task. Help --- Message posted from http://www.ExcelForum.com/ No macro required. ctrl-H for find/replace. find , replace nothing (leave the replace field blank). You can of course record that within a macro if you wish. Drabbacs >-----Original Message----- >I have an excel file that the size will varry. I need a macro that will >check ...

Create Exchange mailbox from command line
I'm writing a script using dsadd and I was wondering if it's possible to create an exchange mailbox from the command line. Donovan Maybe not exactly what you want but it may help: http://www.joeware.net/win/free/tools/exchmbx.htm -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexchangeblog.com "Donovan Linton" <DonovanLinton@discussions.microsoft.com> wrote in message news:D9C839EF-883D-4E2E-8BE9-57782582F043@microsoft.com... > I'm writing a script using dsadd and I was wondering if it's possible to > create an ...

sort by day of the week?
Excel 2002 I have 10 years of grain prices...one price for each trading day of th year. In 'A' we have the date, in 'B' the price. What I need to do i get a list of prices for Mondays, Tuesdays, Wednesdays, etc... Possible? (I really, really don't want to do this manually) Than -- Message posted from http://www.ExcelForum.com add a column = weekday(a1,1) etc and do a sort on this colum -- Message posted from http://www.ExcelForum.com Thank you. You saved me 3 or 4 hours of excruciating data entry. Thi was the last step of a vital report. Now I can do my char...

creating a backup on 2002 for Money 98
I am helping a friend who has 98. I need to make a backup of info on my 2002 for him to use on 98. Any suggestions as to how to do this? M98 can't read any file written by M02 besides .QIF import. M02 can't write any file readable by M98 except for .QIF export. Sounds like QIF export/import is your only choice. I suspect you will find this doesn't do what you want. "Carlotte" <Carlotta41@discussions.microsoft.com> wrote in message news:015b01c3d2fa$bbf8fd60$a101280a@phx.gbl... > I am helping a friend who has 98. I need to make a > backup of info on...

item class table
I am creating SOP IM import. I need to fill the distribution fields with a rev account that is part of the item class. I would like to find a table that would hold the item class accounts. I looked in IV40400 and did not see any distribution accounts. What is the best table to pull these accounts. If the accounts have been defined on the Item Class, they will appear on the records in the IV40400 table. They're in the fields IVIVINDX, IVIVOFIX, etc - and they're just the keys to the actual account definitions in the GL00100 table. If a particular account type isn't defined ...

sorts not sorting
OK- Thanks to everyones help I now i have a list i can work with... but there are a few problems.... the zips are all 5 digit and now i have to sort them... the problem is, THEY WONT SORT CORRECTLY!!!!!!! The numbers are not in order... some are but a lot are not... is there anyway to fix this???? i need to sort them by zip code but cant seem to get them to do it correctly.. any ideas??? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ You are going to be far better of...

Sorting by file extension in a worksheet
Hello, I have an excel worksheet which has a list of file names from a directory and various stats about each file. I need to be able to sort them by the file extension. Eg. c:\documents\folderA\picture1.eps c:\documents\folderB\document.doc I would like to sort them by the ".eps" extension. I have tried using the Data,Sort menu and something like *?*.eps as the criteria. But am obvisouly missing something. Any help would be most appreciated. Thanks Karl You could use a helper column to extract just the extension then include this in your sort range and sort on the helper col...

Adding blank "separator" Rows in a Pivot Table
Does anyone know how to insert blank rows into a pivot table (say, between groupings, etc.)? There has to be a way, since some of the various table formats that come with Excel include blank rows. Double-click the field button that you want to format Click Layout Add a checkmark to Insert blank line after each item Click OK, twice Bucko wrote: > Does anyone know how to insert blank rows into a pivot table (say, > between groupings, etc.)? There has to be a way, since some of the > various table formats that come with Excel include blank rows. -- Debra Dalgleish Contextures http...