Increment code automatically

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,638
Office Version
  1. 2007
Platform
  1. Windows
Hi,
Just a quick bit of advice before I look into continuing.

Imagine a range which is say A20:X23 & there is a code in each cell that is incremated by 1 each time as you go down the page.
Let’s say the last row with cell values is X20 X21 X22 X23

If a value was entered into cell Y20 can the code in the other cells automatically be incremenated by 1

Thanks.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,638
Office Version
  1. 2007
Platform
  1. Windows
Just an update.
I have started with this project and have advised the current situation below.

The new Range is now CF2:CI182

In cell CF182 we have a name but no code

In cell CG182 we have the code =--ISNUMBER(IFERROR(SEARCH($CE$2,CF182,1),""))

In cell CH182 we have the code =IF(CG182=1,COUNTIF($CG$2:CG182,1),"")

In cell CI182 we have the code =IFERROR(INDEX($CF$2:$CF$182,MATCH(ROWS($CH$2:CH182),$CH$2:$CH$182,0)),"")

Now if a name is inserted in the next cell down being CF183 how can we update the code above to reflect in the new row CF183

If it makes a difference on how the name is being entered in the new cell each time it is a copy / paste code from another sheet in the same workbook.

Many thanks.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,638
Office Version
  1. 2007
Platform
  1. Windows
Just had a though,
This is the named range from the Name Manager.

Would the OFFSET code work ??

If so & the current named ranged supplied below can you advise how i would edit to to read/work correctly.
Thanks

Code:
=INFO!$CI$2:INDEX(INFO!$CI$2:$CI$182,MAX(INFO!$CH$2:$CH$182),1)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,143
Messages
5,527,072
Members
409,743
Latest member
sukuto20

This Week's Hot Topics

Top