for excel to automatically add in a line if customer places bigger order

i dont know if this can be achieved via excel, is ther a way to automatically add a line if a customer wants to place a bigger order? for example in the attachment in row 12, to automatically enter new line and subtotlas to automatically scroll down and add it in the line? please help or point me in the direction on what i should be searching for. thanks.

Just right click on the line and then click "Insert". Copy down any formulas.

The totals will automatically recalculate.

hi, I am just wondering if it can be done automatically, or if there was a way to do it, since I don't know what it can be done or can't be done since I am a novice to excel program.

Probably done using VBA but how would Excel (VBA) know you wanted an extra line ??

thanks every body

ok, i think if i click on cell and it will add a line and scroll everything down a line.
But how can i make it to output a message "error" when they pick the wrong model, for example if they pick on A3, then the price is just empty on D3, if i can output a message and put some kind of warning on there.

The whole point of Data Validation (dropdown)is that you always select something: hence how would you know the selection is wrong?

D3 would only be empty if there was no entry in the price table for the cabinet/model/colour selected: will this happen?

If so, you could put this in Column G

=IF([@[PRICE/EACH]]="","Input error","")

i tried that but it gives me some kind of error, what about adding on B3, for example, if it dont have that model, then to output 'error", i am think because a customer might change his ming and the user can go back and pick a different model but he does not pick on B2, so just to point out to the user that there is an error instead of leaving it blank.

ok, I think I can understand. I was just trying to prevent from user to make errors, as if they changed the A1 column, but they forgot to change B2, so to point out to the user that he has picked 'wrong' , since customer change their mind in the middle of an order.

If you want the error checking to cover what are valid combinations of cabinet/model you will have to define a set of "rules" defining all valid options. This starts to get very complex and may requires VBA code to perform this. Even then it is possible to put in a valid but wrong i.e. not what customer ordered, set of data.

ok, i understand. How about setting the quantity to 0 as default, that way if a customer order just 2 items, i dont have to go into each row and set it.

here is the attachemtn so you kow what i mean. I have to go into each row to set to 0 if customer just order 1 item.

Why? Why are you selecting goods if the customer only wants the first item?

ahh isee, that works,
Now, i added dynamic range to the 'QUANTITY' list, as the attahcments shows, how can i add dynamic range so i can add different models in base, wall, pantry, crowns, etc...?

I changed the layout so I can add dynamic range to new products, but I lost the price formula, don't seem to work or I cant get it to work. Please take a look at the attachments. I read in google about 'vlookup' but read that index is more efficient as you have set it. Please help, I wouldn't have gotten anywhere without help.

ok, this is close to what I need, is there a way to narrow down the choices in the drop down list? in sheet3, in column A, then narrow down to category in "column B", and then narrow down in 'Column C" to model number from the drop down list?
here is my attachment.

HI, i understand, i thank you for everything and i am grateful you have spoonfed me this far. I appreciate your help and very grateful again.
Its not that i have continuos changing, its just that i don't have a overall knowledge on what can be done and what can't be done, and how to do thing since i don't know excel much. Its just when i use it i run into errors and how i can simplify the spreadsheet to allow me to do the things that i need.
Thank you again.

hey Johntopley! that was it! I got confused with all the file names and changes, but you got it working as I wanted! I have a whole bunch of files and didn't know which is what, so I redownloaded your file, and it works... IF I may ask 1 last thing, can you set the color to be instead of picking 1 in each row, to pick one on F2 and set the whole thing? PLEASE PLEASE PLEASE...
I know I am over reaching myself .. sorry and apologies, I just don thave the sufficient knowledge to do this... please add the color on how you had it in the beginning.. to have one set.. instead of picking each row on color in F2

Put a DV list in F2 and change the $F references in the Price formula to $F$2

ok, I added a data validation, but in the price formula, in what part of the formula do I put in the reference?

Look at the PRICE formula .... there is only ONE reference to $Fn where is n= 3, 4 etc. ...

GREAT! IT WORKS!! THANK YOU Johntopley!!!!
a question, how come when I copied it donw the formula by dragging? it will enter F3, F4, F5, and so on, I changed it to F2 (for reference) in the price, is there a trick to copy that or its just entered manualy? is that how you did it the first time? so I can learn.
BUT MUCH MUCH THANK YOU!!.. you are the greatest, when I had no where to run, you, I will marked this solved ..thank you again..
I know that you have given up your valuable time to help .. I am grateful..

....$F$2 to FIX the reference

Thank you for the offer but the services offered here are free. Hang on to your Commercial Services points as you might find a use for them in the future.

Hopefully you can now move forward but if you need further help from the forum post a new thread.

ok, thank you.
I was trying to get rid of the blank spaces from the drop down list, when there are for example 3 items in Lazy susan, the drop down list in sheet 1 shows some blank spaces. I am just trying to clean it up and make it a bit nice.

Just change the rage in Named ranges to your likely maximum.