sequential numbering in non adjacent cells

G

Guest

I am doing a receipt template under excel where it automatically updates info
from various workbooks giving the period payments made and then totals it and
converts the total to words. My question is how do I get it to automatically
put the sequential receipt number's into non adjacent cells e.g.
first receipt no. is 06/001 in cell G1, 06/002 is to be in cell O1, 06/003
cell G21, 06/004 cell O21, 06/005 cell G41, 06/006 cell O41... Don't want to
do it manually.
I'm just a novice, so I'm hoping someone has a simple answer.
 
J

JE McGimpsey

One way:

O1: =LEFT(G1,3) & TEXT(RIGHT(G1,3)+1,"000")
G21: =LEFT(G1,3) & TEXT(RIGHT(G1,3)+2,"000")

Copy O1 to O21, O41, etc. Copy G21 to G41, etc.
 
G

Guest

Hi,
Thanks for the prompt response. I have given it a try and it does work but
I still have to change the + no. to get it to move on a number each time. I
have to set up 150 receipts so that way would take alot of time. If you have
another idea I'm all ears.
Thanks
Sue
 
J

JE McGimpsey

Did you try?

If you copy G21 to G41, you will see

G21: 06/003
G41: 06/005

so you don't have to change the "+ no." XL will automatically adjust the
cell reference.
 
G

Guest

Hi,
I copied G21 to G41 and got:
G21: 06/003
G41: 06/003
G61: 06/003
I copied O1 to O21 etc and got:
O21: 06/002
O41: 06/002
O61: 06/002
and so had to alter the + no. afterwards.
I have double checked your instructions and redid it all again but still got
the same answer. Excel does not seem to be recognising that it has to move
the numbers on.
What am I doing wrong?
Sue
 
J

JE McGimpsey

First, make sure Calculation is set to Automatic
(Tools/Options/Calculation)

Second, make sure you're copying the entire cell (not just the formula
in Edit mode). XL will automatically adjust the formula so that you get:


G21: =LEFT(G1,3) & TEXT(RIGHT(G1,3)+2,"000")
G41: =LEFT(G21,3) & TEXT(RIGHT(G21,3)+2,"000")
G61: =LEFT(G41,3) & TEXT(RIGHT(G41,3)+2,"000")
 
G

Guest

Hi,
Thanks for that. Yes I was just copying the formula not the cell and now
doing it correctly it works just great. Thanks heaps!
Sue
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top