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.

[Keebellah]

Hi, I think this is only possible if you write a macro since it will be event-driven; but then you will have to allow macros and be familiar with macros.
Adding a +1 to to the previous row value will not do the trick.
You would also need to include a check if the number is not alrteady present, you will have to play with the max and min functions as well, it's simpler said than done.

[JediDA]

I had a feeling that'd be the best way to go, which is why I posted in the VBA/Macros section of the forums. I'm very well versed in formulas and conditional formatting, I have a little experience with Pivot Tables, but I don't know any VBA, so I was hoping for some help in setting up the code for this.

[Keebellah]

Just a question, may I assume that the different “sections” each have their own sort order?

[JediDA]

Correct. The sections will be independent when I build the book, so ideally, the sections in the excel book will be independent as well. If it would make things easier, I can build each section on it's own sheet instead of having them all in one though.

[Keebellah]

No, it’s okay, I’ll try and post my idea later tonight (tonight here it’s 20:00) here now

[JediDA]

Thank you very much. I look forward to seeing what you come up with.

[Keebellah]

Look, let me see if I understand this.
First your formula's in Row2 for the PAge Count are IMHO entirely inconsistent
Column C ads up to row 61, H up to row 95 M up to row 87 and R row 96
Is this intentional?
What will be entered under the column Page Count?
Can it be that lets say "Fasfl Butane" will have order 1 but covers 2 pages?
Is anything entered in the columns under the checkmarks?

[JediDA]

My apologies for the confusion, I initially set the total page counts to take into account down to row 100 (overkill, considering the whole book is usually less than 100 pages) but in the process of removing listings that aren’t always in the book, I deleted whole cells, which adjusted the formula, and I forgot to adjust it back, or make the references absolute. But my plan is to have 100 be the last row used.

As for the page counts (per row) the default is 1 page per brand ad, but 2-page spreads are possible, and 1/2 page ads are possible (though I’m trying to avoid those, but that’s currently a debate with my boss).

The order is independent of the amount of pages an ad will take up. It is purely intended for my boss (who know the products and the priorities of the company) to easily adjust the order the ads should be in. It is not intended to indicate the page number the ad will be on. The page numbers will be flexible, and I have features to fill space whenever needed to accommodate a spread or to guarantee ad placement on a right hand page if need be.

The checkmark column is just for me. I just use it to mark off when an ad is done/placed in the book, so that I know it’s done without having to find the page in the book.

I hope that helps clarify, and again, apologies for the sloppiness of the row 2 formulas. And thank you so much for tackling this problem.

[Keebellah]

It's clear to me, you'll have to be a little more patient, am working on it.
It's a little more than just change a number and then resort.
The code I used cannot be applied here due to other specifications.
Got the blue-print ready but needs some testing.
Am a little busy but won't forget you, like this challenge and it's doable without too much complicated actions for the users

[JediDA]

I'm absolutely not in a rush. The feature you're helping me with is totally a bonus that's nice to have, not strictly speaking necessary. If I have it by this time next month, it'd be spectacular, but if it takes even longer, that's still fine. In the mean time, I'm still using the one without the auto-order feature, just telling my boss to use the column to rank the order, and then I'll just doing a sort when I get it back. Again, this is just amazing that you even think you can make this happen, and I truly appreciate the work you're doing to help me with this. If I knew coding, I'd make an app for this, but excel is the height of my experience with anything close to that.

[Keebellah]

Thanks for the confidence, I’m sure I’ll have my idea in code by Monday, I code and develop on-the fly and love this type of challenge.
It’s possible and what I like to achieve is that one single module can be used for like in this case 4 tables; I like to avoid duplicate coding routines

[Keebellah]

Well, I think this works. I have not added extra error trapping or anything in that sense.
The users must NOT change the table's dimensions, just work with the four tables adding a brand, item or whatever
The attached pdf is a one-page explanation of what I understood and hoe it works.
Hope this helps you.

[Keebellah]

Morning, have taken a look yet? Just curious.

[JediDA]

Hi there,

I've got it open, and it looks like it works exactly the way I was hoping it would. This is going to save me tons of time of copy/pasting back and forth. You're awesome!
I've marked the original post as solved and made a reputation comment for you. Thank you so much!

[Keebellah]

You're welcome. I hope some of the comment lines in the code help you to see what I did.
VBA is nothing else than a sequential set of commands following the train of thoight you put into it, of course respecting the VBA syntax rules.
Hope the guys at your work like it too

[JediDA]

Definitely, this is super helpful. Thanks again.

[Keebellah]

Great. You're welcome

[JediDA]

Quick question for you. If I want to add a column for applicable promotions to each set, is that something I'd have to do in the VBA code?

[Keebellah]

You can add the column and see what happens but there is a section in the code that checks the columns and it’s the sorting section that might mess up if the extra column is not taken into account

[Keebellah]

You have to decide what you want to do.
The worksheet now consists of 4 tables with 4 columns and a fifth with 3 columns.
Each Listobject of the first 4 will have to be resized to 5 (or more if you decide you want more columns later.
Inserting a column is not that difficult but the VBA code needs to be 'told' that so yes, the VBA code needs adapting, not much but it cannot be done without losing functionality.

[Keebellah]

I took a look at the file and the most difficult is to make sure that inserting the columns does not modify the Tables.
The steps I did:
Firs select the empty column between each table and inser a new column, do this four times
The last table I did not change
Then select each table and on the ribbon select resize table and expand it one column
The new header will have the name Column 1 with you can rename.
Do this again for each table
In the VBA code I added a new variable eCol dimensioned as string

[JediDA]

That's amazing! I honestly wasn't expecting more than a "Yes" or "No". Thank you so much. In talking with my boss, it occurred to us that it might be helpful to have the deal listed where applicable, and I figured it wouldn't be a big deal to just add it to the main title column, but as I did that, I realized it would probably be easier for deals/promos to have it's own column, so I really appreciate you explaining how to go about adding that column in. Thanks again for all your help.

[Keebellah]

You’re welcome, I did not add much comments to the vba code but I do hope that it gives you an idea of the way it works, my methods are not standaard and I’m sure others will approach it in entirely other way
Imagination, patience, some fantasy and imagination a s lot of time is all you need😉