Forum Discussion
Autuomatically adding Rows with a formula
Is there a way to use formulas to add rows based off a number that i input?
I am using excel and a printing software that automatically prints each cell and a label and assigns a QR code. The products we create a panel Hurricane Shutter system. I have an excel sheet that automatically combines the opening(window,door,etc) name with the part number, so i have to manually input the name and number. since the amount of part numbers is always consistent, i feel i should be able to speed things up a little bit.
Example. If i want to use a Shutter that has one panel, i know that it will always have 5 parts. So i have to manually input into 5 different rows, 10 different cells. Window1, 1A, Window1, 2A, Window1, 3A, Window1 4A, Window 1 5A.
If i want to use a shutter that has two panels, i will have to create 7 parts, (Window1 6A, Window1 7A) etc.
I would like to go into one row, input the name of the my window and number of parts and have it automatically generate the requested amount of rows with the 7unique inputs.
If youre not understanding what i saying, or just curious, this may help. The link to our sales page. basically everything part on this blueprint would have its own QR code that would lead it to this website.
https://frontrowhurricaneshutters.com/sales-ph32
(Dont let this distract from the main question, but one way i speed up the process now is that i have a table with the generic part numbers that i use to quickly input. I have to manually input the name(window1), but then i just copy the table so speed things up a little bit. But im looking to speed things up still)
A formula will not do what you require. The only way to insert rows like you reference is to use VBA code (macros). The request you have is a fairly common one. If you search the internet you will find different variations of the same answer. You could use a macro linked to a command button or even a worksheet event that triggers when you type a number into a specific cell. Here are a few different variations of what your trying to accomplish (I think the first one is closest to your requirements):
https://analysistabs.com/excel-vba/insert-rows-worksheet/
https://stackoverflow.com/questions/17588158/excel-2007-macro-insert-x-value-of-rows
https://www.exceltrick.com/how_to/insert-multiple-rows-in-excel/
If you are not familiar with VBA Code and how to use it this article may help:
http://www.contextures.com/xlvba01.html
Hope this helps
Matt
