Formula for a range between two variables in one cell

[WadeMK]

I am making a packing list for my company and we want to number the cartons on the excel sheet. We number the cartons as a range e.g 001 - 005 which means the product is packed in 5 cartons, numbers 1 to 5. The next product has 2 cartons so the range would be 006 - 007. I want a method that allows my staff to input the quantity of cartons for each product and excel automates the numbering. The staff would write "2" in the cells E$10-E16 (or another hidden column, that's fine) and excel would input 001 - 002. In the next product column, they'd write "5" and excel would input 003 - 007.

The second part of my question is: we also write the CBM (cubic meter) of the carton on the sheet, I'd like the staff to input a value in H2 and that value be multiplied by the "CTN number" in the same row E2 and the result to be input in the same CBM cell the staff wrote in (cell H2). How would I go about doing this? I prefer to use as few columns as possible and as few hidden columns as possible in order to avoid confusion as we're printing on A4, but I would like to hear all ideas you guys have :D

Thank you in advance for the help

[AliGW]

Welcome to the forum!

Regarding the first query: you can't have data entry in hidden columns without the staff unhiding them first. Is this really what you mean? By the way, 2 cartons would be 006-007.

[Glenn Kennedy]

Put the staff-entered nuumber of cartons in a column off to the left (I used column A), and leave it out of the print area. I then used

=TEXT(SUM($A$1:A1)+1,"000")&" - "&TEXT(SUM($A$2:A2),"000")

to generate your result AFTER I killed your merged cell. Don't use them. they are the spawn of Satan and mess everything up.

[WadeMK]

Welcome to the forum!

Regarding the first query: you can't have data entry in hidden columns without the staff unhiding them first. Is this really what you mean? By the way, 2 cartons would be 006-007.

Ya the staff would unhide the columns, enter the data and then hide the columns again before printing and/or sending to the customer. Thanks for noticing the typo!

[WadeMK]

Put the staff-entered nuumber of cartons in a column off to the left (I used column A), and leave it out of the print area. I then used

=TEXT(SUM($A$1:A1)+1,"000")&" - "&TEXT(SUM($A$2:A2),"000")

to generate your result AFTER I killed your merged cell. Don't use them. they are the spawn of Satan and mess everything up.

This is fantastic. Thank you so much! Sometimes we put different products in the same cartons. Example: 3 pcs of Product A and 2 psc of Product B are in Carton 1. Then 5 pcs of product A in Carton 2. The only way to show that is to either merge cells or repeat the carton numbers of Product A on Product B. I've been trying to use merged cells with your method and I think it would still work, spawn of Satan seems helpful sometimes

Any idea for a solution about my second question? is there a way for the staff the type the CBM value of one carton in H2 (I2 in your document) and the value gets multiplied by A2 and the result is input into H2. I understand this might be unconventional but I'd rather use two columns for the job rather than three because I'm worried my staff will forget to hide a column before sending out the packing list. Many thanks again!

[Glenn Kennedy]

Good luck - but the Spawn of Satan will get you in the end. You have been warned.

Anyhow...

You're welcome.

---

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

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Glenn Kennedy]

Just noticed your supplementary Q. I'll try to remember to look at it in the UK morning. I'm signing out now... the sea bass is frying nicely.