Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 102
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,344
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 102
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,344
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,344
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I set up Excel sheet that auto increments a no. when opened Craneman Excel Discussion (Misc queries) 2 May 1st 08 09:53 AM
can I sort the order of sheets in a book? The Life Coach Excel Discussion (Misc queries) 1 November 2nd 07 04:51 PM
sort excel when the workbook is opened Michael Excel Discussion (Misc queries) 7 February 1st 07 05:33 PM
How to auto activate macro when file opened bonzio Excel Worksheet Functions 3 December 17th 05 04:19 PM
auto insert invoice number that increases by one each time opened Michael HPSC Excel Worksheet Functions 3 November 29th 05 09:10 AM


All times are GMT +1. The time now is 02:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"