Help with incrementing number and keeping text the same

plasma

New Member
Joined
Feb 21, 2011
Messages
5
Hi there,

I have this string in A1: Player 1

and I'd like to setup excel so no matter what I typed I A1, that all the other predetermined cells auto increment by 1.

For example: If I typed "Player 1" in cell A1, then cells A2 through A40 would change to "Player 2", "Player 3", etc.

Hope that makes sense.

Thank you for any help!!
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Type Player 1 in A1

Select right hand corner of cell A1 until it turns into a vertical cross
Drag the cell down

It should return Player 2 player 3 etc
 

plasma

New Member
Joined
Feb 21, 2011
Messages
5
Type Player 1 in A1

Select right hand corner of cell A1 until it turns into a vertical cross
Drag the cell down

It should return Player 2 player 3 etc

Thank you for the reply, but that's not exactly what I need.

I need it so I could change "Player 1" to say "Player 50", and have it automatically increment to "Player 51", "Player 52", etc
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Where are all these other cells located, in related to the first one?
Is the first value in A1, the next in A2, then A3, etc?
 

plasma

New Member
Joined
Feb 21, 2011
Messages
5

ADVERTISEMENT

Where are all these other cells located, in related to the first one?
Is the first value in A1, the next in A2, then A3, etc?

Mainly yes, A1, A2, A3 etc.. Then will jump to D1, D2, D3 etc.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Well, if it didn't jump, we could make use of the row number that it resides in in the formula.
But if that isn't consistent, because then it jumps over to the beginning of a new column, I don't know that we would be able to do that.
The best bet, I would think, may to be to use VBA, where we tell it to add a static number to all the existing values after the word "Player".
Would that be an acceptable solution?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,934
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about in A2 filled down
=LEFT(A1,FIND(" ",A1))&MID(A1,FIND(" ",A1),99)+1
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
How about in A2 filled down
=LEFT(A1,FIND(" ",A1))&MID(A1,FIND(" ",A1),99)+1
How does that handle the column D part?

Mainly yes, A1, A2, A3 etc.. Then will jump to D1, D2, D3 etc.

Maybe have D1 use some sort of MAX formula on the column A values, and then have the rest of the D formulas use a formula similar to what you have posted for column A?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,934
Office Version
  1. 365
Platform
  1. Windows
I was thinking the OP meant that you have Player 1 in A1 & (say) Team 1 in D1 as hard values.
But reading it again I think I might have misunderstood.
If the OP means that D1 automatically follows on from A3, that's more of a problem
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
If the OP means that D1 automatically follows on from A3, that's more of a problem
Yeah, that is where I am at right now.

I think we need specific details instead of generic statements.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top