sort data without changing pattern

I have a worksheet where every other row has a 'pattern' in it to help make 
it easier to read across the spreadsheet.

The problem I'm running into is as I add new rows at the bottom of the 
spreadsheet and then resort them into their correct place, the patterns go 
with the sort and now the patterns have to be redone again the get them back 
to every other row.

Any suggestions?
0
Utf
4/6/2010 2:30:03 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
2783 Views

Similar Articles

[PageSpeed] 29

This will happen if your patterns are manually applied. It would be
better to remove that manual shading and to apply background colours
using conditional formatting, the condition being if the row number is
even (or odd) then colour the cell - the CF formula would be something
like:

=3DMOD(ROW(),2)=3D0

for even-numbered rows.

Hope this helps.

Pete

On Apr 6, 3:30=A0pm, Dan <D...@discussions.microsoft.com> wrote:
> I have a worksheet where every other row has a 'pattern' in it to help ma=
ke
> it easier to read across the spreadsheet.
>
> The problem I'm running into is as I add new rows at the bottom of the
> spreadsheet and then resort them into their correct place, the patterns g=
o
> with the sort and now the patterns have to be redone again the get them b=
ack
> to every other row.
>
> Any suggestions?

0
Pete_UK
4/6/2010 2:48:38 PM
Rather than filling rows with a pattern, try a conditional format rule that 
sets the fill to a color for every other row.  For example,  a rule in A1 
that fills when =MOD(CELL("row",A1),2) is true, then copied to all rows, 
fills every other row (in columns selected) with color.  The color will 
persist when the column is sorted.

g

"Dan" wrote:

> I have a worksheet where every other row has a 'pattern' in it to help make 
> it easier to read across the spreadsheet.
> 
> The problem I'm running into is as I add new rows at the bottom of the 
> spreadsheet and then resort them into their correct place, the patterns go 
> with the sort and now the patterns have to be redone again the get them back 
> to every other row.
> 
> Any suggestions?
0
Utf
4/6/2010 2:53:01 PM
=MOD(SUBTOTAL(3,$A1:$A$2),2)=0

Used in Conditional Formatting as Formula.

First select a great whack of rows then enter the formula in activecell
only.


Gord Dibben  MS Excel MVP


On Tue, 6 Apr 2010 07:30:03 -0700, Dan <Dan@discussions.microsoft.com>
wrote:

>I have a worksheet where every other row has a 'pattern' in it to help make 
>it easier to read across the spreadsheet.
>
>The problem I'm running into is as I add new rows at the bottom of the 
>spreadsheet and then resort them into their correct place, the patterns go 
>with the sort and now the patterns have to be redone again the get them back 
>to every other row.
>
>Any suggestions?

0
Gord
4/6/2010 5:29:40 PM
Adding to all previous replies:
if you wanted to colour your sheet in bands of 3 (or any other number) of 
rows you could use this formula in your conditional format:
   =MOD(INT((ROW()-1)/$A$1),2)
where A1 contains the number of rows per band.

"Pete_UK" wrote:

> This will happen if your patterns are manually applied. It would be
> better to remove that manual shading and to apply background colours
> using conditional formatting, the condition being if the row number is
> even (or odd) then colour the cell - the CF formula would be something
> like:
> 
> =MOD(ROW(),2)=0
> 
> for even-numbered rows.
> 
> Hope this helps.
> 
> Pete
> 
> On Apr 6, 3:30 pm, Dan <D...@discussions.microsoft.com> wrote:
> > I have a worksheet where every other row has a 'pattern' in it to help make
> > it easier to read across the spreadsheet.
> >
> > The problem I'm running into is as I add new rows at the bottom of the
> > spreadsheet and then resort them into their correct place, the patterns go
> > with the sort and now the patterns have to be redone again the get them back
> > to every other row.
> >
> > Any suggestions?
> 
> .
> 
0
Utf
4/7/2010 10:34:01 AM
Here's a general formula for setting a periodic conditional formatting:

=MOD(ROW()-offset,period)=0

will format every 'period' row, and move the pattern up or down by 'offset'. 
 For example, to highlight every fifth row with the first highlighting in row 
4 the formula would read =MOD(ROW()+1,5)=0

g

"Gord Dibben" wrote:

> =MOD(SUBTOTAL(3,$A1:$A$2),2)=0
> 
> Used in Conditional Formatting as Formula.
> 
> First select a great whack of rows then enter the formula in activecell
> only.
> 
> 
> Gord Dibben  MS Excel MVP
> 
> 
> On Tue, 6 Apr 2010 07:30:03 -0700, Dan <Dan@discussions.microsoft.com>
> wrote:
> 
> >I have a worksheet where every other row has a 'pattern' in it to help make 
> >it easier to read across the spreadsheet.
> >
> >The problem I'm running into is as I add new rows at the bottom of the 
> >spreadsheet and then resort them into their correct place, the patterns go 
> >with the sort and now the patterns have to be redone again the get them back 
> >to every other row.
> >
> >Any suggestions?
> 
> .
> 
0
Utf
4/7/2010 12:24:01 PM
Reply:

Similar Artilces:

send the same e-mail with one or two fields changed.......
I would like to send the same e-mail to many differnet people with one or two fields changed (for example the name of recipient and the date).How canthis be done?? I would also like to be able to save the e-mail and use it again and again. can anyone help cheers john If you have Word installed and it's the same version as Outlook (both 2003, for example), you can do a mail merge between the two. This would allow you to set up the text the way you want it to, and you can save the document for future use. Look at the following page for further information: http://www.slipstick.com/con...

why does sorting change a scatter plot graph?
Why does the way a spread sheet is sorted change the look of a scatter plot graph??? the graph is just a plot of two points, (X, Y) and these two points are definded by two collumns for a given row. The two collumns don't change, and the row all stays together, so why does it change where points are plotted out on the graph when you re-sort it? AndrewT420 - Usually, for an XY (Scatter) chart, with values of X in a column and corresponding values of Y in an adjacent column, for three or more points, Excel assumes (correctly) "Series in Columns." But, when you have only...

Changing Prices in HQ.
Hi, I have this little issue. I want to change the put items on promotion using the price wizard using HQ. Unfortunately if I have stores who has differents prices for a same item the wizard do not make the proper change becuase it use the price already stored in the master table. Does anyone saw this issue before? Who was solved?. Thks in advance for your help. Rgds Rodrigo Hi there, The easiest way to look after this is to not change any data on the ITEM in HQ, but to simply do the worksheet for altering the sale price and then send it to the respective stores. Then in the works...

Content of emails is changing without any reason !
Hallo I changed operating system last week. From Win XP to Win 7. Used to work with Outlook Express at full satisfaction. I could transfer most of my emails automatically with export/import features of Microsoft software. But I suddenly discover 1 very big problem (bug ???) I am used to work with several maps, and hereby go to several levels deep. Such as : Saved mails Companyname Projectname Date of action Department Activity Name of patient Different emails So sometimes maps can go several levels deep. When I check ema...

Saving toolbar changes
After spending a lot of time to customize a toolbar in Excel 2003, it disappears when opening up another file, or starting the app again. I repeatedly change it, save it as XLB, XLT, save multiple copies in every possible location...but the damn thing always defaults to its own toolbar settings. This makes toolbars almost useless. How can one insist that PPT use YOUR toolbar setting, rather than its own default Thanks. Hi Jeff, If I have a lot of tool bar changes to make, I close all the workbook that are not hidden then unhide my personal.xls from the Window menu. I don't know why...

Change cannot be saved due to sharing violation
Hello I've this message while saving the excel file even if no change ha been done to the file. There is no share on this file (exclusive use) File resides on a network drive It's very disturbing Thanks for your help Vobiscu -- Vobiscu ----------------------------------------------------------------------- Vobiscum's Profile: http://www.msusenet.com/member.php?userid=245 View this thread: http://www.msusenet.com/t-187102186 http://support.microsoft.com/default.aspx?scid=kb;en-us;328170 Thanks for your answer, I will try tomorrow noo Vobiscu -- Vobiscu ----------------...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

Changing query execution sequence
Hi all, I got a spreadsheet which would execute a bunch of queries. It's noted that the queries are executing in the sequence of when it was added to the spreadsheet. Does anyone out there know of a way to switch the order without deleting and recreating them? Thanks! Wing ...

Data migration - Adventure Works
Hiya... I have a company where the adventure works db has been used and had a lot of data populated into the system. We have now purchased MSCRM and have obtained the company reg keys. What is the easiest way to get the data from the 1 system to the next? We will be establishing a new AD domain and users for the new system.... Data Migration Framework? Redeployment Framework? ;) redeploment tools http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "funboy...

Mem leak when implement Singleton pattern on CWinApp derived class
Coded my MFC app to have singleton pattern, my code looks like this: class CMyApp : public CWinApp { private: // static obj static CMyApp* MyAppInstance; // some other obj composed in this class COtherObj* obj; // private cons, I init the COtherObj here CMyApp() { obj = new COtherObj(); } public: // only way to access cons static CMyApp* GetInstance() { //check if already instantiated if( MyAppInstance == NULL) { MyAppInstance = new CMyApp(); } return MyAppInstance; } virtual int ExitInstance() { // I use ExitInstance to delete all ...

Sorting on Column headers
I have a spreedsheet that someone else created and I need to take the Column headers, which are in date form, and rearrange them in chronological order. Can this be done without cutting and pasting? I really need to save time with this project if possible. Thanks!:) --- Message posted from http://www.ExcelForum.com/ > I have a spreedsheet that someone else created and I need to take the > Column headers, which are in date form, and rearrange them in > chronological order. Can this be done without cutting and pasting? I > really need to save time with this project if possib...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

Automatic changes in cells
Hi for some reason I now have to save my work for any formlas etc to change when I update a worsheet, how can I stop this as it is a pain and sometimes I need to do changes to see how they work before saving the work. Many thanks Click on Tools | Options | Calculation tab and set to Automatic calculation, as it is probably set to Manual. You can press F9 to force a recalculation under a manual setting. Make sure you save the file with the Automatic setting, to avoid it happening next time. Hope this helps. Pete On Feb 1, 11:42=A0am, Office 2004 Test Drive User <heepenm...@yahoo.co.u...

Change the text of a shape rather than its master
Hi, I build custom masters by mixing two general shapes, say square and circle together, and have text on both the shapes. But after I drop an instance of the master into a page, I cannot modify the text of the instance. To do so, I need to modify the text on the master, which is non-sense for me. How to change the text of a shape without modifying its master? Thanks! How are you doing this? By code or by the UI? Are you grouping the shapes? If you drag two shapes to the stencil, it will group the shapes. So instead of a square and a circle you have three shapes. A Square, Circle and the...

cell colour change when set markers are reached
i need to get a cell to change colour when markers are reached eg a qualification lasts 12 months. what i want to do is have the cell change from yellow to orange to red as the expiry date gets closer. If column A contains expiry dates then select column A, Formats>Conditional Formatting>formula1: =DATEDIF(TODAY(),A1,"m")<1 red for 1 month Click Add button, formula2: =DATEDIF(TODAY(),A1,"m")<2 orange for 2 month Click Add button, formula3: =DATEDIF(TODAY(),A1,"m")<3 yellow for 3 month Adjust number of months as you like! Regards,...

Changing ip address of exchange server #2
Hi, I have a back-end server and a smtp server in DMZ. I want to change ip address of back-end server. are there any issues? all incoming and outgoing emails are going via smart host. Hi, No issues at all as long as you remember to change all the references to this server in your firewall, SMTP scanner etc. Leif "Jack Dorson" <JackDorson@discussions.microsoft.com> wrote in message news:FE5927A1-D20D-4C6B-991F-2E1EFD19434D@microsoft.com... > Hi, > > I have a back-end server and a smtp server in DMZ. > > I want to change ip address of back-end server. are ...

Sorting Cells by Colors
Hi all, Is it possible to write a VBA code to sort excel cells by colors, and the followed by other criterias, as in the normal sort? Thank you in advance. Hi SwiftCode, See Chip Pearson's Sorting By Color page at: http://www.cpearson.com/excel/SortByColor.htm --- Regards, Norman "swiftcode" <swiftcode@discussions.microsoft.com> wrote in message news:FC1550A7-A8DD-4EC0-B171-F1DB4373C35C@microsoft.com... > Hi all, > > Is it possible to write a VBA code to sort excel cells by colors, and the > followed by other criterias, as in the normal sort?...

How to change a background color with Publisher
How can I click on to a background color and change that background color to another color using Publisher? Ron Williams wrote: > How can I click on to a background color and change that background > color to another color using Publisher? ====================== Try these steps... Double left click the color you want to change and a Format screen should open that has a drop window with color choices. -- J. Inzer MS-MVP Digital Media Experience Notice This is not tech support I am a volunteer Solutions that work for me may not work fo...

how do I change date format in the header in Excel XP ?
I need to chage my date format, in the header to Day; Month DD, YYYY ie. Saturday; May 23, 2005. Woudl you please help me out ? Thank you. Regards, Hesam Shakourian Check this out. http://support.microsoft.com/?kbid=213742 but change the format to "dddd, MMMM dd, yyyy" "Hesam" <Hesam@discussions.microsoft.com> wrote in message news:93495F75-4196-4208-9C0D-E800BCAE3A89@microsoft.com... > I need to chage my date format, in the header to Day; Month DD, YYYY > ie. Saturday; May 23, 2005. > > Woudl you please help me out ? > > Thank you. > &g...

Excel List Sorting Problem (Descending)
Hi there, I'm having trouble sorting my list--my column contains *only* 4-digit numbers but when I click on "descending order", only about the first half of the rows are arranged this way, before it begins again to arrange the rest in descending order. Like this: 5120 5119 5118 4000 3050 5116 4112 etc. Has this problem happened for anybody else? I'd appreciate any help you can offer. Part of your list is text, although it looks like numbers. Format an empty cell as number. Enter the number 1. Copy. Select your "numbers". Edit>Paste Special, check Mul...

How to receive photos sent to me without having to click on them
I have just started using Outlook 2007. I am unable to receive photos in the text email message. They all come as attachments. I cannot find any settings to allow direct insertion of photos into the email text message area. Help Please..... Outlook doesn't support inline images. See http://www.slipstick.com/mail1/inline.asp for options. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newslett...

Data validation list from another worksheet?
Is it possible that the value list for data validation be populated fro another worksheet? Puneet Aror -- puneetarora_1 ----------------------------------------------------------------------- puneetarora_12's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1840 View this thread: http://www.excelforum.com/showthread.php?threadid=38572 Sure is! Use a named range as described here: http://www.officearticles.com/excel/drop-down_using_data_validation_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "punee...

Change Domain Name on outgoing Emails
Our company just purchased another company with their own Exchange Server and AD infrastrure. We want all users in this new facility to have Email addresses with our Domain such as username@abc.com instead of their current Domain username@123.com. Until I migrate resources from their Forest into our Forest I have created contacts to forward all Emails from the abc Domain to the 123 Domain. When users reply or send Emails from the 123 Domain it still has their username@123.com Email address which will cause confusion with our customers and suppliers. How do I force their Emails to us...

"external data sources" and "external data ranges"
what is the difference between these? i have run the vb macro code on http://support.microsoft.com/kb/330383 to check if i have any external data sources or data ranges but there are none. the reason i need to know is becuase i'm working with office sharepoint server 2007 and i cannot access a file thru the web access web part because it says: "The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services: External data ranges (also called query tables)" anyone? thanks ...