Auto Number/Auto Reorder

[JediDA]

Hi everybody,

I'm not sure what the best way is to go about what I'm trying to do, like if it can be done with formulas or pivot tables, or if it would require VBA, so hopefully I posted this in the right place.

Every month, I build a catalog/magazine and each brand/product line get's its own page. My instinct (because I'm a designer and not that familiar with all of the products) is to just build the magazine with the ads in alphabetical order by brand, but the purchasing/sales departments have their own priorities based on inventory levels and incentives, and I'd like to make it easier to plan the book with/for them. I've built an excel sheet with the different sections of the book listed, room for brand names to be listed, and given a page count (it defaults to automatically fill in a 1 whenever the ad name cell is filled in, but it can be changed to a .5 or a 2 for a spread). I also added a little counter with conditional formatting that turns the cells with the page count red if the book is short pages, and turns green when we have enough pages accounted for.

So with that context, here's what I'm hoping to try to do. I left a column left of the ad name for "Order" where I want it to autofill a number for every row that's filled (starting with 1 and going up 1 as long as there's something in the column to the right of it). I know how to do create that order listing with an if function that looks to see if there's something in the column to the right, and if there is, shows the number in the row above it+1, and that's what I currently have in the sheet. But here's the problem, I want the order to be flexible.

Ideally, I want the order to auto-fill as long as there's an ad listed in the column to the right, but I then want someone to be able to change the order number, and then automatically have that row move up the list and bump everything else down, and automatically renumber the rest of the list.

The ultimate goal is to pass this excel file around to 5-7 people in purchasing/sales, have them all add the brands that should have a page dedicated, and then send the final list to our boss, and let him just start renumbering the list until he's happy with the order. That way, he can send it to me, and I can build a complete framework for the book for the month even before I have artwork or pricing adjustments. I got the idea when working on my portfolio website and encountered this kind of feature when adjusting the display order of project pages, so I'm hoping that excel can do something like this.

Any help would be greatly appreciated. Thank you.