Formula to add rows based on required capacity

[sbweeks]

Hi All,

First post to get the ball rolling;

How can I get a tab, to add rows based on a variable at the top of the sheet?

For example, I have a data rack that can be either 20, 10 or 5 rows, depending on the size of the cabinet, and I want to be able to use a drop do to select the size (20/10/5) and then have it make a list of 1-20/10/5 as required, that I can then fill in which each row is for.

Hopefully, that makes some sense!

Thanks

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[sbweeks]

Hi Ali, thanks for the information (and welcome).

So I've attached a workbook with a basic setup for what I need it to do, with the desired output shown in yellow.

Thanks in advance.

[JeteMc]

If I understand correctly then the following will do what you want:
1) The following formula in G5 and down references the number, picked by data validation in cell H3: =IF(ROW(1:1)<=H$3,ROW(1:1),"")
2) The following formula in the range H5:I24 will copy the data from columns C:D based on whether or not there is a value in the corresponding row of column G: =IF($G5<>"",C5,"")
Let us know if you have any questions.

[sbweeks]

Thanks for the reply, its not exactly what I need, but I may be able to modify that.

I've attached another sheet, showing two cabinets (on CAB1 + CAB2), what I need to be able to do is if for example I now need more ways on CAB2, to select the drop down to say 10 ways and I add the blank (formatted) rows for me to populate.

[JeteMc]

Perhaps this is closer to what you want. The 'Does not contain...' rule is restricted to the range that will receive the numbers and the cells to the right are formatted using the formula: =$G5<>""
Let us know if you have any questions.