automatically numbering rows #2

I have an XL2003 spreadsheet i use to enter data. It is constructed so
that column A has a formula...row()-10, which automatically number the
rows, which start at row 11. Col. B has is used to enter a
descriptions, and in cols. C - Q i enter numbers. Cols R - V have
formulas that summarize the data entered in C-Q. C-Q gives me 15 cols
to enter data. Sometimes i need additional cols. I cannot add columns
because the landscape printout would have to be inconveniently small.
I have macros that allow me to insert or delete rows with the formulas
in cols R-V. Of course this does not make my numbering wrong. What I
need is to be able to occasionally insert a row that would have no
formulas, to use to add additional numbers in excess of 15. The
problem is that these added 'data only' rows will mess up my numbering
formula. Is there a way to add a row, that I would not want numbered,
without making the numbered rows below it off by 1?

Thanks,

Tonso
0
wthomasss (97)
9/30/2010 6:23:55 PM
excel 39879 articles. 2 followers. Follow

2 Replies
484 Views

Similar Articles

[PageSpeed] 36

On Sep 30, 2:23=A0pm, Tonso <wthoma...@hotmail.com> wrote:
> I have an XL2003 spreadsheet i use to enter data. It is constructed so
> that column A has a formula...row()-10, which automatically number the
> rows, which start at row 11. Col. B has is used to enter a
> descriptions, and in cols. C - Q i enter numbers. Cols R - V have
> formulas that summarize the data entered in C-Q. C-Q gives me 15 cols
> to enter data. Sometimes i need additional cols. I cannot add columns
> because the landscape printout would have to be inconveniently small.
> I have macros that allow me to insert or delete rows with the formulas
> in cols R-V. Of course this does not make my numbering wrong. What I
> need is to be able to occasionally insert a row that would have no
> formulas, to use to add additional numbers in excess of 15. The
> problem is that these added 'data only' rows will mess up my numbering
> formula. Is there a way to add a row, that I would not want numbered,
> without making the numbered rows below it off by 1?
>
> Thanks,
>
> Tonso

I think i have a solution to my problem.   =3Drow()-10-countblank(A
$11:A15)      where A15 is on the current row.
I am currently testing this formulas to determine if it works in all
situations.

Thanks to everyone who provides help on this usergroup!!

Tonso
0
wthomasss (97)
9/30/2010 8:13:51 PM
Hi Tonso,

if column B in inserted rows is empty, try in A11:
=IF(B11="","",COUNTA($B$11:B11))


Regards
Claus Busch
-- 
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
0
claus_busch (107)
10/1/2010 8:09:03 PM
Reply:

Similar Artilces:

Numbering for column
Hi, Good Day! I seldom used microsoft excel but sometimes i do used it for some simple calculation. When I just opened an excel spreadsheet, the numbering of column is in number format(1, 2, 3...) and not in alphabet format(A, B, C...) May I know how can i reset it back to alphabet? Thanks in advanced. rgds, Phoebe Tools|Options|General|Uncheck R1C1 Reference Style (this is picked up from the first workbook you open in that session. So make sure you save your workbook after the change--and make a note of how you fixed it. It can happen again if the first workbook opened has R1C1 refe...

Validate the format of a number
I need to determine that an entered serial number is valid. It must check that it is 11 characters and follows the format as follows: a letter, followed by a number, followed by 2 letters, followed by 6 numbers, and ending with a letter. For example, the user enters D7PM234567B and the cell next to it would indicate 'valid' or something similar. If 87PM2345674 was entered, it was indicate 'invalid' next to it or something similar to alert the user it is not in the correct format. Thank you very much in advance. Steve This formula =AND(LEN(A1)=11,CODE(...

Format text to numbers
Hi All, Just a quick question guys, I've used a formila before that converts text to a unique number, for example...... H464 - now this might bring back 123456 Any ideas? Thanks, James. The formula should work both ways: if you can convert text to a unique number, then you should be able to reverse it to convert that unique number to text. What is your algorithm? Hi Dave, Meaning I can't remember how to transform text to number, I'm 100% sure there is a function that does this but can't remember! Thanks, James. "Dave O" wrote: > The formula should wor...

Minimum row height
Hello I'm using the Auto fit row height and it works very well but is there a way I can have a minimum height for the rows such as 105? thanks answered above -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "SteveZmyname" wrote: > Hello > I'm using the Auto fit row height and it works very well but is there a way > I can have a minimum height for the rows such as 105? > > thanks ...

page numbering #2
I'm working on a landscape Excel document...the document will become part of a booklet,and I need the page numbers at the bottom of the page, but in portrait. Any help? ...

MSDN Installation 1.2: Javascript Errors
I have installed a CRM Server and separate CRM client (along with the supplied example database). Access through IE6 works fine. Within Sales for outlook, I have 2 issues; 1> It takes 1.5 minutes to start up Outlook (it's only a 1Ghz machine, but CPU usage appears as 1-2% while it is loading). 2> I get various script errors when clicking on the CRM Shortcuts; Errors include messages such as... 'http//crmsvr/sfa/home_comps.aspx - object does not support this property or method' IE script error 'Could not complete the operation due to error 80020101' For testing, ...

Hide rows with value "Hours"
I would like to toggle rows to hide/unhide based on the value "Hours" in column B. I think filtering is an issue to some blank seperator rows and merged cell headings. There are no merged cells involving Column B. Thanks. Public Sub ProcessData() Const TEST_COLUMN As String = "B" '<=== change to suit Dim i As Long Dim iLastRow As Long With ActiveSheet iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).row For i = 1 To iLastRow .Rows(i).Hidden = .Cells(i, TEST_COLUMN).Value = "Hours" Next i End With ...

USB2 2.5 external backup
After very useful help from this group earlier, I am now deciding which external drive to buy. I am converting to USB 2.0 but on seeing a number of sales sites often 2.5 drive is shown as laptop and 3.5 as desktop. As I have very little backup 40 GB would give me ample space. I have a desktop unit but would prefer a 2.5 would this be compatible with desktop setup. Regards Roger ========================================= ============================== Roger wrote: > After very useful help from this group earlier, I am now deciding which > external drive to buy. I a...

Microsoft Outlook 2000 error #2
I have not been able to keep Outlook up on our server, it closes soon after it is opened with this error that I cannot understand. It says: Exception: access violation (0xc0000005) Address: 0x3f99a142 What does it mean and how can I fix it? Does anyone have any idea? Thank you! ...

Programaticly / Automaticly save email attchments in exchange
I am looking for a soultion on how to automaticly save (detach) email attachments sent to a specific email address(mail box). The action would talke place on the exchange server when the mail arrives and would be put into a specific directory Can this be done with a script? Can this be done with VBA? If so, may I have a code sample. I apreacete you help. Regards, Peter ...

how do I find an average number of specific words in a column
I am attempting to calculate a number of specific word occurrences. In example, I have a column with yes in certain cells, and no in the others. How can I display the total number of yes and no occurrences? I am fairly new to Excel and know very little about coding in it. Hi you can use the countif function to return the numbers of "yes" and "no" e.g. =COUNTIF(A1:A100,"yes") will count the number of "yes" answers in the range A1 to A100 likewise, =COUNTIF(A1:A100,"no") will count the number of "no" answers in the same range H...

Multiple users #2
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I installed a home/student version of Office. My wife installed the same and unfortunately used the same of the three product codes. Every time she uses this I get kicked off. I uninstalled Office and re-installed with the idea of entering a different product code, but it never asked for a product key code. The program is fully working, except when my wife starts using hers and I get kicked off again. How do I fix this? See step #4 here: http://www.entourage.mvps.org/install/index.html#resetkey -- HTH |:>) Bo...

Maximum number of SMTP addresses per user
Hi we use an antispam filter that using ldap to determine if an email address is valid before it reaches our exchange servers. This is very effective and reduces the ammount of spam we receive. The system does not send bounce backs if an address is not valid. What I have been asked to do is to send find a way to send a message back to the sender when someone emails an address of an employee that has left the company to inform them to contact our customer services department. The only way I can think of doing this is to create a mailbox that has multiple smtp addresses on it that has an...

Trying to automatically import specific data from Excel into Word:
Thanks in advance for anyone who can help me with this: I'll start by explaining the spreadsheet I created in Excel (an inventory / information worksheet): It is pretty simple; approximately 12 columns. The key column is the item number / UPC / bar code number, then after that there are various information columns including item name, description, condition, picture links, etc. Secondly, I created a template Microsoft Word document that is relatively long, but will have a few lines (give or take...about 7) customized for each item on the inventory. What I am trying to d...

Banks #2
I am reviewing my current banking relationship and am looking for a better option than Wells Fargo. Their current charges for using bill pay through M/S money is $9.95 a month, no relief it having a high balance or combined accounts. "John" <John@discussions.microsoft.com> wrote in message news:F89443FF-1B73-4F22-A029-488DCE4701FF@microsoft.com... >I am reviewing my current banking relationship and am looking for a better > option than Wells Fargo. Their current charges for using bill pay > through > M/S money is $9.95 a month, no relief it having a high ...

Best way to add a row to a table with calculations?
Suppose I have a worksheet like this: 1 2 Date Days 3 5/25/08 35 4 4/20/08 45 5 3/06/08 46 6 1/20/08 41 ... The value in the Date column is calculated (=A3-A4). I would like to add a row between rows 2 and 3 and have it acquire all of the properties of row 3. The best procedure I could come up with is: 1. Right click on row 3, select Insert. This inserts a row between 2 and 3, but it acquires the properties of row 2. 2. Select row 4 (old row 3) and press Ctrl-C (copy). 3. Select row 3 (new row) and press Ctrl-V (paste). This seems to work. Is there a better wa...

Error Handler #2
Has anyone written an error handler for VBA forms. The error handler would display error messages based on bad input and pop up messages dynamically, etc. If not, has anyone developed a clean way to display error messages without using excessive message boxes? Thanks for any help. --- Message posted from http://www.ExcelForum.com/ Instead of message boxes and popups, consider a label on the form itself that displays the error message. In article <Ripan.zrum2@excelforum-nospam.com>, Ripan <<Ripan.zrum2@excelforum-nospam.com>> wrote: > Has anyone written an error ...

Sumif base on 2 criteria
I am having problems coming up with a sumif formula based on two conditions. I have three columns. Column A = client #, Column B = work code, and Column C = hours. I am looking for a sumif formulat that will calculate the total hours worked for employees working on (for example) Client #387 and work code 88. Please help if you know how to do this. thanks. Joe Hi Joe; use sumproduct for this; =SUMPRODUCT((A1:A100=387)*(B1:B100=88),C1:C100) I am assuming 387 and 88 are NUMBERS. If not, enclose them in ""s. "Joe Rotondo" <desjoe@aol.com> wrote in m...

Highlighting Rows #2
Hi Is there a way in which the row you are currently on in excel is highlighted in a different colour, and reverts back to original when user moves to a new row? Thanks right click sheet tab>view code>paste this>SAVE. You'll like it. 'McCurdy.Here is something inspired by Don Guillett. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim MyRng As Range Set MyRng = Target.EntireRow Application.EnableEvents = False On Error GoTo end1 Application.Cells.FormatConditions.Delete With MyRng .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=R...

reading pane in outlook 2003 #2
i prefer not to use the reading pane and have it switched off but it seems its configured seperately for every folder, is there an overall control for the reading pane in outlook 2003 so you can just switch it off for all folders? You can do this by changing the default Messages when all folders are still in this default view. Otherwise define a new view and apply it on first use of the folder. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "jas0n" &...

2 Y Axes: Lines and a Stacked Column
Hello, I'm using Excel 2003 SP3 and having trouble with the following... Sample data: X Axis Y Axis1 Y Axis1 Y Axis2 Y Axis2 Y Axis2 Date DataA DataB DataC DataD DataE 1/31/09 4.3 3.6 10% 40% 50% 2/28/09 2.9 1.9 30% 60% 10% 3/31/09 1.2 6.4 15% 10% 75% I need Y Axis1 to be two simple lines and Y Axis2 a stacked column that sums to 100%. Can't figure this out. Please advise... Thanks! Jeff First, clear the cell above the dates,...

PSTDisconnectDelay #2
I'm running Outlook 98 on an XP machine and I need to change the default time for how long outlook locks it's PST file for. I have added a registry key for this and set it to 10 seconds as listed in the help files (See below) but with no change. It still takes 30 minutess for outlook to release the file. Microsoft Knowledge Base Article - 234211 "The .pst file lock release interval is now configurable by adding the following DWORD value PSTDisconnectDelay to the following registry key: HKEY_CURRENT_USER\Software\Microsoft\Office\8.0 \Outlook\PST Setting PSTDisconnect...

Ending Cash Balance in Investment Transactions Register vs Cash Transactions #2
This subject is the same as a previous topic. There didn't seem to be a conclusion in the last discussion of this problem, so I decided to address it once again for those who may still be suffering from this issue. Nonetheless, it's probably worth it for you to read the previous discussion for additional background and context information. THE PROBLEM: In MS Money 2004, for whatever reason, some transactions are stored as Opening Balance transactions and they're hidden from the user such that the opening balance will always be greater than zero even when there are no transaction...

RE: Outlook 2003 / CRM 1.2 Install issue.
Hey there, Having trouble installing client v1.2 on a Win2000 Office 2003 client. The install completes sweet as no probs but when I first load up Outlook '03 I get the message Microsoft CRM ---------------- An error occurred whilr installing Microsoft CRM folders. Have tried searching for log files for the client but to no avail. If anyone can point me towards something or even tell me where the (install)log files are would be much apprec. Chris. Chris, I had a similar problem on a test install of CRM 1.0 with win XP and office 2003. I Just kept clicking ok and then c...

Automatically Save Message To Contact Detail
Is there a way to automatically have emails sent to and/or from a contact automatically saved in that contact's detail as an attachment? Cass <cass84@gmail.com> wrote: > Is there a way to automatically have emails sent to and/or from a > contact automatically saved in that contact's detail as an attachment? Isn't that sort of what the Journal does? -- Brian Tillman No... Brian Tillman wrote: > Cass <cass84@gmail.com> wrote: > > > Is there a way to automatically have emails sent to and/or from a > > contact automatically saved in that conta...