Last instance - Insert Row - Continue formulas Project

**Fett2oo5** · 06-13-2013, 12:54 PM

Hello all, I have an ambitious idea I would like to create. We have a gigantic list of parts I've consolidated into a "Master Item List" I would like to make it easy for the users to add new items. Items are grouped by category.

A User's natural thought being:
"I want to add a new [category] item"
The user would then scroll down a huge list to find the category (or autofilter)
Then they'd need to insert a new row by copying the previous one (so that formulas continue)
Then change the contents of the cells that don't have formulas.

I'd like for:
User to select a category from a drop down.
The sheet "autoscrolls" to last instance of that category item.
Automatically inserts a row with continued formulas and conditional formatting, but not the previous rows non-formula content (manually entered values)

If you could help with any part of this project I would greatly appreciate it. Even if it is just one small part of a task I'm looking to use.

Attached an extremely condensed example.

**Fett2oo5** · 06-13-2013, 01:16 PM

I guess it's worth mentioning I did find this in another thread:

Please Login or Register  to view this content.

Placed on a button it then asks you for the word to search, I'd rather it give a list to select from. And this enters a blank row, I'd like to formulas and Cond.Formatting to continue down.

**nilem** · 06-13-2013, 01:17 PM

Hi Fett2oo5,
try it

Please Login or Register  to view this content.

**Fett2oo5** · 06-13-2013, 01:39 PM

@nilem Much better than what I found, and continues the formula!

This is excellent!!!
Thank you!

Now to figure out how to change the way to handle the "list of category options" without using validation.

Thank you nilem!

Would you happen to know how to "autoscroll" to the inserted row? There are over 4000 items, so 4000+ rows

**nilem** · 06-13-2013, 01:54 PM

try

Please Login or Register  to view this content.

Note that the conditional formatting is copied as well
edited:
what do you mean "to handle the "list of category options" without using validation"

**Fett2oo5** · 06-14-2013, 10:18 AM

If I use validation I need to have a list of the options in a column. But if a new category is added, then the user adding the new category may not update the validation list.
I'd rather the options for adding a new item be what ever is in the category column. Does that make sense?

**Fett2oo5** · 06-14-2013, 10:23 AM

BTW this is superb!
Thank you so much for providing me with this code.

**nilem** · 06-14-2013, 10:53 AM

You can also try this option. Updating the validation occurs when you activate the worksheet.
Please note: there is a limit on the length of the validation list string (255 characters). If there are too many categories, an error will occur (but there is a solution to this problem

)