I need to have a form generated with a list of numbers down a column starting with a particular number. I have a formula set up that just takes the previous row’s number and adds one, so the column automatically increments…
…the problem is that the numbers have to be four digits, and I’m starting at 0569. I can’t make Excel stop changing “0569” into “569”. I mean, I can change the top one where I entered the number to be formatted as “text” and then IT shows up as 0569, but all the ones thereafter omit the zero.
HEEEEELP!!!
(The person who did this before me just manually entered every number… I don’t want to do that.)
Ok, enter the following formula in the cell directly below your starting value (e.g 0258).
=RIGHT(CONCATENATE(10000+A1+1),4)
Replace A1 with whatever cell your beginning number is in. This should display your starting value +1 with a zero in front of it right below your starting value. Highlight the cell with the formula in it and Edit - Fill Down for all the cells that you wish to diplay and increment of the original value.
How the formula works:
It looks at the cell above it and creates an incremented value with a one as the leading digit (e.g. 10258). Then it goes back and selects the first 4 digits from the right leaving out the 1.
In general, if you’re having trouble with Excel auto-interpreting your text as some sort of other data, just stick a ’ in front of it. '0258 will render as 0258 instead of 258.
Rather than opening a NEW Excel thread, may I piggyback your thread?
Working a long spreadsheet with Row 1 as my header. How do I keep that row static? I don’t want to keep scrolling up/down to remember what goes in what cell.
Depens on the formula. Anything that’s treating the values as text should be fine, but anything that’s treating them as numbers may or may not have problems. If I have A1 = '0258 and A2 = '0569, A1 + A2 returns 827 but SUM(A1, A2) returns 0.
The apostrophe or the ’ will not appear on your prinouts nor should it appear in the cells of the workbook. Just as formulas will display totals as opposed to displaying formulas.