CODEWITHSUNDEEP
excelexcel-formula
EV1
EV1

Reputation: 1

Excel Issue with using Concatenate & Increment while creating unique number

I would greatly appreciate help on this issue I am having with excel

I have a table in excel and adding rows of data to it. For one column I am creating a unique number based on 4 cell values, and then have the last digit increment + 1. Right now I have a formula

=IF((OR($B5="BF",$B5="AD")),LEFT((TEXT([@(Current Date]],"MM"),2)&"-"&RIGHT(TEXT([@Current Date]],"YY"),2)&"-"&[BF/AD]&"-"&[CODE]&Rows()-4,"")

so the output looks like 07-21-BF-QA-1 and then the next row would be 07-21-BF-QA-2

so everything works BUT I can not use rows because I do not want the last number to change when a row is deleted. For auditing purposes, I need the number to not change. I have spent so much time trying to figure this out and no luck. I keep looking at alternate functions instead of rows but keep running into the same issue of the number automatically changing.

Please help!!! Thanks in Advance.

Upvotes: 0

Views: 272

Answers (1)

Qualia Communications
Qualia Communications

Reputation: 715

You can achieve the desired result with the following approach:

  • Generate your unique number through a reference to a separate tab
  • Hide that "tab" to avoid that someone removes a row in it.

Just replace your ROWS()-4 command by ROWS([Tabname]!A1).

The tab bar looks like this: enter image description here or (if Rows is hidden) enter image description here

In my example, the formula is on Output, and ROWS() references cell A1 on Rows tab.

Output at the beginning

enter image description here

Output after removing row #6

enter image description here

Note: Please note, that Excel has two to options for hiding tabs (xlSheetHidden and xlSheetVeryHidden). While the first one can be applied and undone via the Excel GUI, the second one can only be set and changed via VBA. Check Worksheet's Visible property. enter image description here

Upvotes: 1

Related Questions