Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto sort when worksheet/book opened
Two parts to this I'm afraid ....
I have a sheet that ranges A to S and down. When another sheet updates I have a formula running to auto update columns A & B with Last Name & First name which works well - I want to have the sheet auto sort each time it is opened by column A - 1. How do I do this please 2. Can I make it only sort when there are names in the cells rather than including the formulas which at this point show as 0 until updated beacuse at this point when sorted the 0's all go to the top?? Assistance as always appreciated. -- Thanks Lise |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto sort when worksheet/book opened
Hi,
There is no command to autosort data in the regular area of the spreadsheet, the Piovt Table will do that when you refresh and you can choose to hide 0's. In the regular part of the spreadsheet you could apply an Auto Filter and hide the 0's with that. In 2007 you can click the Reapply command which might help in your case. Other than these ideas you probably would need to write VBA code to do what you want. Let us know if that is an options, in which case we will need a more detailed description of the spreadsheet. What is the data range, including titles? Do you have totals at the bottom, touching the data? Why are the 0's at the top, do you want to leave them there? do all cells on a row have 0's or only certain columns? -- Thanks, Shane Devenshire "Lise" wrote: Two parts to this I'm afraid .... I have a sheet that ranges A to S and down. When another sheet updates I have a formula running to auto update columns A & B with Last Name & First name which works well - I want to have the sheet auto sort each time it is opened by column A - 1. How do I do this please 2. Can I make it only sort when there are names in the cells rather than including the formulas which at this point show as 0 until updated beacuse at this point when sorted the 0's all go to the top?? Assistance as always appreciated. -- Thanks Lise |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto sort when worksheet/book opened
Hi Shane
I think it will be a code option - My spreadsheet is made up of (take a deep breath this is long) Data in Columns A through to S but it is only A & B that I have the formula running that will copy last name & First names from another workbook depicting new staff - which is working fine (if the above workbook is blank though these cells show a 0 which I have hidden) it is these 0's that shoot to the top when I currently sort - perhaps I have written the formula wrong and shouldn't be getting them at all?? Formula is ='Completed Inductions'!B168 - all other columns from C - S are completed manually. Having given more thought - I Would love for the worksheet to auto sort perhaps on a certain key value ie new user opens worksheet sees that the latest staff are at the bottom so can manually complete rest of sheet then when "key" pressed whole sheet auto sorts by Column A last Name (A-Z) Sorry - this is the easiest way I can describe it. Many thanks in advance for your patience Lise "ShaneDevenshire" wrote: Hi, There is no command to autosort data in the regular area of the spreadsheet, the Piovt Table will do that when you refresh and you can choose to hide 0's. In the regular part of the spreadsheet you could apply an Auto Filter and hide the 0's with that. In 2007 you can click the Reapply command which might help in your case. Other than these ideas you probably would need to write VBA code to do what you want. Let us know if that is an options, in which case we will need a more detailed description of the spreadsheet. What is the data range, including titles? Do you have totals at the bottom, touching the data? Why are the 0's at the top, do you want to leave them there? do all cells on a row have 0's or only certain columns? -- Thanks, Shane Devenshire "Lise" wrote: Two parts to this I'm afraid .... I have a sheet that ranges A to S and down. When another sheet updates I have a formula running to auto update columns A & B with Last Name & First name which works well - I want to have the sheet auto sort each time it is opened by column A - 1. How do I do this please 2. Can I make it only sort when there are names in the cells rather than including the formulas which at this point show as 0 until updated beacuse at this point when sorted the 0's all go to the top?? Assistance as always appreciated. -- Thanks Lise |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto sort when worksheet/book opened
Hi,
I will look this over tomorrow and see what I can put together. It's sleepy time here. -- Thanks, Shane Devenshire "Lise" wrote: Hi Shane I think it will be a code option - My spreadsheet is made up of (take a deep breath this is long) Data in Columns A through to S but it is only A & B that I have the formula running that will copy last name & First names from another workbook depicting new staff - which is working fine (if the above workbook is blank though these cells show a 0 which I have hidden) it is these 0's that shoot to the top when I currently sort - perhaps I have written the formula wrong and shouldn't be getting them at all?? Formula is ='Completed Inductions'!B168 - all other columns from C - S are completed manually. Having given more thought - I Would love for the worksheet to auto sort perhaps on a certain key value ie new user opens worksheet sees that the latest staff are at the bottom so can manually complete rest of sheet then when "key" pressed whole sheet auto sorts by Column A last Name (A-Z) Sorry - this is the easiest way I can describe it. Many thanks in advance for your patience Lise "ShaneDevenshire" wrote: Hi, There is no command to autosort data in the regular area of the spreadsheet, the Piovt Table will do that when you refresh and you can choose to hide 0's. In the regular part of the spreadsheet you could apply an Auto Filter and hide the 0's with that. In 2007 you can click the Reapply command which might help in your case. Other than these ideas you probably would need to write VBA code to do what you want. Let us know if that is an options, in which case we will need a more detailed description of the spreadsheet. What is the data range, including titles? Do you have totals at the bottom, touching the data? Why are the 0's at the top, do you want to leave them there? do all cells on a row have 0's or only certain columns? -- Thanks, Shane Devenshire "Lise" wrote: Two parts to this I'm afraid .... I have a sheet that ranges A to S and down. When another sheet updates I have a formula running to auto update columns A & B with Last Name & First name which works well - I want to have the sheet auto sort each time it is opened by column A - 1. How do I do this please 2. Can I make it only sort when there are names in the cells rather than including the formulas which at this point show as 0 until updated beacuse at this point when sorted the 0's all go to the top?? Assistance as always appreciated. -- Thanks Lise |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto sort when worksheet/book opened
Hi,
I was hoping for a quick solution, but not exactly an obvious solution. Suppose your formula that link back to the other sheet look like this =Sheet1!B2 Then replace them with: =IF(Sheet1!B2="","zzzzzzzzz",Sheet1!B2) This will force them to sort to the bottom. Next, select all the cells and choose Format, Condtional Formatting, from the second drop down choose equal to, and in the third box enter ="zzzzzzzzz" Click Format, and on the Font tab choose No Color. Click OK twice. This will hide the formulas that find nothing on the other sheet. Suppose that the First Name is in column A and the Last Name in column B, then manually you could sort your data by Last Name, First Name by putting your cursor in any single cell of column A and clicking the Ascending Sort button. Then clicking in any single cell of column B and clicking the Ascending Sort button again. To make the file automatically sort Sheet2 whenever the user activates it the following code can be added to the workbook: Private Sub Worksheet_Activate() Range("A1").Sort _ Key1:=Range("B2"), Order1:=xlAscending, _ Key2:=Range("A2"), Order2:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal End Sub This code goes into the Sheet2 object. 1. Press Alt+F11. 2. In Projects window on the top left side you will see your workbook listed with sheets under its folder. Double-click the sheet inwhich your formulas are located 3. On the right hand side in the white area (the code window) paste in the code I gave you above. If any of this helps, please clickt the Yes button. -- Thanks, Shane Devenshire "ShaneDevenshire" wrote: Hi, I will look this over tomorrow and see what I can put together. It's sleepy time here. -- Thanks, Shane Devenshire "Lise" wrote: Hi Shane I think it will be a code option - My spreadsheet is made up of (take a deep breath this is long) Data in Columns A through to S but it is only A & B that I have the formula running that will copy last name & First names from another workbook depicting new staff - which is working fine (if the above workbook is blank though these cells show a 0 which I have hidden) it is these 0's that shoot to the top when I currently sort - perhaps I have written the formula wrong and shouldn't be getting them at all?? Formula is ='Completed Inductions'!B168 - all other columns from C - S are completed manually. Having given more thought - I Would love for the worksheet to auto sort perhaps on a certain key value ie new user opens worksheet sees that the latest staff are at the bottom so can manually complete rest of sheet then when "key" pressed whole sheet auto sorts by Column A last Name (A-Z) Sorry - this is the easiest way I can describe it. Many thanks in advance for your patience Lise "ShaneDevenshire" wrote: Hi, There is no command to autosort data in the regular area of the spreadsheet, the Piovt Table will do that when you refresh and you can choose to hide 0's. In the regular part of the spreadsheet you could apply an Auto Filter and hide the 0's with that. In 2007 you can click the Reapply command which might help in your case. Other than these ideas you probably would need to write VBA code to do what you want. Let us know if that is an options, in which case we will need a more detailed description of the spreadsheet. What is the data range, including titles? Do you have totals at the bottom, touching the data? Why are the 0's at the top, do you want to leave them there? do all cells on a row have 0's or only certain columns? -- Thanks, Shane Devenshire "Lise" wrote: Two parts to this I'm afraid .... I have a sheet that ranges A to S and down. When another sheet updates I have a formula running to auto update columns A & B with Last Name & First name which works well - I want to have the sheet auto sort each time it is opened by column A - 1. How do I do this please 2. Can I make it only sort when there are names in the cells rather than including the formulas which at this point show as 0 until updated beacuse at this point when sorted the 0's all go to the top?? Assistance as always appreciated. -- Thanks Lise |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I set up Excel sheet that auto increments a no. when opened | Excel Discussion (Misc queries) | |||
can I sort the order of sheets in a book? | Excel Discussion (Misc queries) | |||
sort excel when the workbook is opened | Excel Discussion (Misc queries) | |||
How to auto activate macro when file opened | Excel Worksheet Functions | |||
auto insert invoice number that increases by one each time opened | Excel Worksheet Functions |