Skipping a cell when formatting, NEW HERE PLEASE HELP

[MusicJunky]

hey Guys, I am new here I'm hoping someone can help me.

Okay so I am creating Product ID's for my inventory of games
I am doing the PS4 games and I am using the following formula to get the numeric value I need which in this case it's

="V1180"&ROWS(B$2:B2) The 0 is so that when I reach over 999 games that it will not change the category from 118 to 119. Since 118 is the PS4 category.

The problem is that I have a different ID for Pre-owed and New.
V118XXXX is the id used for NEW While
V918xxxx is used for Pre-owned.




Product ID Category Description Condition
V11801 PS4 1001 Spikes New
V91801 PS4 1001 Spikes Pre-Owened
V11802 PS4 100Ft Robot Golf New
V91802 PS4 100Ft Robot Golf Pre-Owened


This is the way I would need to formulate it but I cannot figure out how to make the formula skip a cell but still be the same alpha-numeric value

excel help.png

Please ignore the last line


I would like to place all of the category's on the same worksheet if I can figure this out.

each one has a different category id like 101, 901, 102, 902, 103, 903..etc it's the difference between new and pre-owned.

Once the Product Id is a true value it will never be changed, I can manually change all the id's..but that will take forever I have over 5,000 games.


when I use the formula and then re-alphabetize the games it changes the formula so then the correct id is not displayed.

As you can see here excel help 2.png

[shukla.ankur281190]

Row\Col	A	B	C	D	E
1	Product	ID	Category	Description	Condition
2	V11801	PS4	1001	Spikes	New
3	V91801	PS4	1001	Spikes	Pre-Owened
4	V11802	PS4	100Ft	Robot Golf	New
5	V91802	PS4	100Ft	Robot Golf	Pre-Owened

Try A2

Please Login or Register  to view this content.

Copy down

[PFDave]

Use this is B2 and fill down

Please Login or Register  to view this content.

[nick93]

S
EDIT: Oh sorry I didn't see your post, I see that you have a column with the Condition so scratch what I said. I think the above formula will help you.

[MusicJunky]

shukla.ankur281190

Thank you so much! this worked!

Success.png

[MusicJunky]

PFDave

This one worked too! thank you I hope this can help someone else with a similar question

[shukla.ankur281190]

Happy to help you
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.