I'm hoping someone out there can help me. I've got a spreadsheet that numbers a list of steps. There are several sections to the steps and each section has a section number (1, 2, 3, etc.). This number is listed in a cell and I'm using the following function to generate a numbering scheme of the form <sectionNum>.<stepNum>
, where <stepNum> = 1
for the first step in each section.
I'm using the following function to auto-generate this listing:
=CONCATENATE($A$22,".",(ROWS($A$22:A97)-2))
Where $A$22
is the cell containing the section number (static text – no formula there) and A97 is the current cell to be numbered. In this case, there is a row containing a comment that is not to be numbered, so I subtract 2 so that the first <stepNum> = 1
.
This worked very well for part-way through file. Then I hit another comment row and had to adjust the formula to subtract 3 instead of 2, to continue numbering. However, as soon as I tried to change the formula, the cell began acting like the data was text instead of a formula. I've checked to ensure there's not a '
at the beginning of the line and tried playing with the different formats for the cell, but nothing changes. I can't get it to evaluate the formula. Not only that, but I can't get any other formula I type into a cell to work, either. All show the same text version of the formula.
I'm trying to figure out whether I may have exceeded a limit of functions. I have gotten up to section 6 before hitting this problem and the number of cells using the formula for each section is:
1: 5
2: 4
3: 3
4: 1
5: 2
6: 79
The problem with worrying about whether I've gone over the usage limit is that Excel will still let me copy and paste an existing cell (which is still evaluating the function) into a new cell and evaluate it just fine. But it will switch to just showing the formula text even if I just COPY the function from the formula bar (or the cell contents, instead of the whole cell) and haven't pasted it anywhere yet. Pasting – or even just typing – the exact same function into another cell results in it just showing the formula text again. Editing the formula in any cell (even one that was previously working) results in the cell displaying the formula as text. And all I'm trying to do is replace that -2
with a -3
!
I've used general numbering formulas (just adding 1 or 2 to the previous cell's content to get the current cell's content) and have been able to go several hundred rows deep with no trouble, so I'm somewhat skeptical that I have, in fact, hit a limit, but I want to rule that out, if possible.
I'm running MS Excel 2013 on a 64-bit Windows 10 OS. The file is on my desktop, so there shouldn't be any network-related issues. The only cells with formulas are the ones I've numbered – everything else is static text. There is a lot of color-coding of cells but, otherwise, it's a pretty plain-Jane spreadsheet.
I've tried entering formulas on other tabs with no luck. I've tried quitting and restarting Excel with no luck. I've tried rebooting my computer with no luck. I can't upgrade to a later version of Excel because this is a work computer and all updates have to come through IT – I don't expect the next one any time soon.
I'm just a little over 100 rows into this document and I expect it to be well over 1000 rows by the time I'm done. I shudder to think I'll have to manage all the numbering manually.
=
, correct -2 to -3. Copy down.