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.

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.



In article ,
Sue wrote:

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.

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

"JE McGimpsey" wrote:

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.



In article ,
Sue wrote:

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.

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.

In article ,
Sue wrote:

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.

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

"JE McGimpsey" wrote:

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.

In article ,
Sue wrote:

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.

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")


In article ,
Sue wrote:

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?

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

"JE McGimpsey" wrote:

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")


In article ,
Sue wrote:

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?