Occurence #'s

S

Seveneleven

Hi I have to create an excel sheet that has the following details.

1. numbers ranging from 06-0001 to 06-8000
2. The number at the bottom of column A on page 1 must continue at th
top of column C on page 1.

I tried doing this with the row() function but I think it will take m
about a year to enter it all as I have to restart at the top of eac
column and add the number from the bottom of the previous column.

I also tried dragging the box, but that was equally tedious. I a
hoping there is a better way?

I think this might be confusing so I will do my best to illustrate wha
I need.
Page 1
A B C D E F G H
06-0001 Blank 06-0048 Blank
... ... 06-0049 Blank
... ... ...
... ... ...
06-0047 Blank

this pattern would continue until the 06-8000 number is reached. Wit
the last number of column G increasing by one for the first number i
column a on page 2. 8 columns per page.

Thanks for looking
 
B

Bob Phillips

In C1

=LEFT(INDEX(A:A,MAX(IF(ISBLANK(A1:A65535),0,ROW(A1:A65535)))),3)&TEXT(RIGHT(
INDEX(A:A,MAX(IF(ISBLANK(A1:A65535),0,ROW(A1:A65535)))),4)+1,"0000")


which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Seveneleven" <[email protected]>
wrote in message
news:[email protected]...
 
S

Seveneleven

Thanks, that worked fine for moving from a to c but all of the othe
columns arent working? I get REF# error
 
B

Bob Phillips

Works fine for me. Can you give an example? Also, how do you calculate A2,
A3, etc., then if you have that formula in C1, C2, C3 etc.?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Seveneleven" <[email protected]>
wrote in message
news:[email protected]...
 
S

Seveneleven

I am entering 06-0001 in a1 and the formula in C1 (with control, shif
enter) then I drag a column down to row 46 and the numbers increase t
06-0046. And the formula works to give me 06-0047 at the top of colum
C. However that is as far as I can get. I need to be able to go up t
06-8000 this way over several pages.

Thanks for your help.
 
B

Bob Phillips

Use these formulae

C1:
=LEFT(INDEX(A:A,MAX(IF(ISBLANK(A$1:A$65535),0,ROW(A$1:A$65535)))),3)&TEXT(RI
GHT(INDEX(A:A,MAX(IF(ISBLANK(A$1:A$65535),0,ROW(A$1:A$65535)))),4)+1,"0000")
C2: =LEFT(C1,FIND("-",C1))&TEXT(MID(C1,FIND("-",C1)+1,99)+1,"0000")

C1 is still an array formula. Copy C2 down

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Seveneleven" <[email protected]>
wrote in message
 
Top