Data Validation - Insert Row

[Alex]

Hi,

I have been trying to figure this out but, can’t seem to find a solution.
Please Help.

Cell A1 will show lists of categories (fruits or veggies), cell B1 is
dependent on A1 and will show its items depending on what I choose in A1. Up
to here, no problem.

But I want something a little different. I want cell A1 to show lists of
categories and have the rest of column A be dependent on cell A1. If I select
the rest of the column and have it refer back to cell A1, excel automatically
adjusts the formula to each cell (e.g. A2 refer to A1, A3 refer to A2….and so
on); this way I have to change it cell manually.

Another thing and this is the most important to me. Is there a way to insert
an empty row if the user selects “other†in the list in cell A1? (e.g. the
user wants a category that is not available, so he chooses “other†in the
reference cell, A1, so another row will be inserted and he will be able to
type what he wants). I know something close to this can be done with
“unchecking†the error message, but I want to know if it’s possible to have
an extra row inserted.

Thanks a lot

Alex

 

[Pete_UK]

You can't insert a row by means of a formula - you would need a macro
to do that.

I don't quite understand what you are saying about cell references -
if you make it A$1 then the row part will not change.

Hope this helps.

Pete

 

[Alex]

Hey, Pete

Yes, you helped. I oversaw the "$" symbol.

But, about that macro you are talking about how would I do that? Any ideas??

Thanks

 

[Pete_UK]

Glad to be of help.

Regarding the macro, I can't really help you with that, but perhaps one of
the more experienced VBA contributors will chip in. If not, then you might
like to repost in the .programming group.

Pete