Hi,

I have been trying to figure this out but, cant 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 its possible to have
an extra row inserted.

Thanks a lot

Alex

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


On Feb 29, 12:51 pm, Alex wrote:
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

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" wrote:

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


On Feb 29, 12:51 pm, Alex wrote:
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

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

"Alex" wrote in message
...
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" wrote:

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


On Feb 29, 12:51 pm, Alex wrote:
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