4

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.

2
  • 1
    That's way, way too much information. Sometimes the user ( you and me ) inadvertently add a character ( space for example ) to a formula and now it turns to text. Fix only the first formula. Make sure not extra spaces in the first character position = , correct -2 to -3. Copy down.
    – ejbytes
    Apr 26, 2017 at 23:46
  • I have not experienced having the cell changed to text just from adding a space to a formula. And I add them all the time, for the sake of readability. I just gave it a try but it didn't seem to fix anything.
    – user722556
    Apr 27, 2017 at 22:23

2 Answers 2

4

I have Excel sheets with more than twelve millions of formulas; I don't think there is any limit.

Your problem is probably that the cell format is set to 'Text', and whatever you enter is treated as text. Change the format to 'General', and edit the content afterwards (add an x and remove it), and it should work. Alternatively, you can copy a working formula into the field (which copies the format too), and edit it as needed.

2
  • That was what I was thinking, but this was causing me such a headache trying to figure it out I had to at least consider the possibility. I had tried to copy a working cell to a 'broken' cell, but as soon as I tried to update it, it just broke again. However, I just checked and some of the cells causing the problem (not all by a long shot) are formatted as Text. Not sure how or why (I inherited this spreadsheet), but I've changed them to General and it does seem to be helping. Thanks for the idea and cross your fingers for me that it keeps working! :)
    – user722556
    Apr 27, 2017 at 22:39
  • it your file has such a huge spreadsheet then it'll be much better and efficient to use array formulas. Or better yet, use a real database
    – phuclv
    Feb 15, 2019 at 16:01
0

100 rows are nowhere near the limit. Each speadsheet can have 1,048,576 x 16,384 cells, and there's no limit on the number of sheets in a workbook, so you can have billions, trillions (or even more) of formulas in your workbook.

Regarding your question, it's too broad. Without a demonstration I don't think anyone can answer it. But if the data automatically changes type when typing or copying then I think it's because some add-in misbehaved


As a side note, why do you use CONCATENATE? I've never find that I need to use it in my life. Change the formula to

=$A$22 & "." & (ROWS($A$22:A97)-2))

This is recommended by Excel:

Best practices

  • Do this:

    Use the ampersand & character instead of the CONCATENATE function.

  • Description:

    The ampersand (&) calculation operator lets you join text items without having to use a function.

    For example, =A1 & B1 returns the same value as =CONCATENATE(A1,B1). In many cases, using the ampersand operator is quicker and simpler than using CONCATENATE to create strings.

https://support.office.com/en-us/article/CONCATENATE-function-8f8ae884-2ca8-4f7a-b093-75d702bea31d

Another important note

Important: In Excel 2016, Excel Mobile, and Excel Online, this function has been replaced with the CONCAT function. Although the CONCATENATE function is still available for backward compatibility, you should consider using CONCAT from now on. This is because CONCATENATE may not be available in future versions of Excel.

2
  • That's not the question he asked
    – Aganju
    Apr 27, 2017 at 11:56
  • 1
    True, but it's good information to have, nonetheless. I just tried it and it works on some of the early cells but, for the ones having the problem, it doesn't help.
    – user722556
    Apr 27, 2017 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.