Occurence #'s
 

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 the
top of column C on page 1.

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

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

I think this might be confusing so I will do my best to illustrate what
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. With
the last number of column G increasing by one for the first number in
column a on page 2. 8 columns per page.

Thanks for looking.


--
Seveneleven
------------------------------------------------------------------------
Seveneleven's Profile: http://www.excelforum.com/member.php...o&userid=30954
View this thread: http://www.excelforum.com/showthread...hreadid=506222

Occurence #'s
 

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:A6553 5)))),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"
wrote in message
...

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 the
top of column C on page 1.

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

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

I think this might be confusing so I will do my best to illustrate what
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. With
the last number of column G increasing by one for the first number in
column a on page 2. 8 columns per page.

Thanks for looking.


--
Seveneleven
------------------------------------------------------------------------
Seveneleven's Profile:
http://www.excelforum.com/member.php...o&userid=30954
View this thread: http://www.excelforum.com/showthread...hreadid=506222

Occurence #'s
 

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


--
Seveneleven
------------------------------------------------------------------------
Seveneleven's Profile: http://www.excelforum.com/member.php...o&userid=30954
View this thread: http://www.excelforum.com/showthread...hreadid=506222

Occurence #'s
 

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"
wrote in message
...

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


--
Seveneleven
------------------------------------------------------------------------
Seveneleven's Profile:
http://www.excelforum.com/member.php...o&userid=30954
View this thread: http://www.excelforum.com/showthread...hreadid=506222

Occurence #'s
 

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

Thanks for your help.

Bob Phillips Wrote:
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




--
Seveneleven
------------------------------------------------------------------------
Seveneleven's Profile: http://www.excelforum.com/member.php...o&userid=30954
View this thread: http://www.excelforum.com/showthread...hreadid=506222

Occurence #'s
 

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"
wrote in message
...

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

Thanks for your help.

Bob Phillips Wrote:
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




--
Seveneleven
------------------------------------------------------------------------
Seveneleven's Profile:
http://www.excelforum.com/member.php...o&userid=30954
View this thread: http://www.excelforum.com/showthread...hreadid=506222

Occurence #'s
 

That helped a great deal! Thanks.


--
Seveneleven
------------------------------------------------------------------------
Seveneleven's Profile: http://www.excelforum.com/member.php...o&userid=30954
View this thread: http://www.excelforum.com/showthread...hreadid=506222