Dropdown Sheet
 

Good afternoon all, i am struggling to create a spreadsheet looking like this
below, this is an hierarchical dropdown, i know its not simple.

Product Type Desc extra
Clothes
Dress
Pants
Long
Regular
Short
Loose
Skirt
Pleated
Causual
Mini
Party
Sweater
Longsleeves
Black
Short sleeves
Red
Blue
Vegetable
Lettuce
x
y
I would appreciate this very much i have been looking at the Dalglish
template and cant seem to be able to match this.
Very much appreciated....

Dropdown Sheet
 

If you're typing the data, one way is to just type it in the way you want.

But this doesn't look like a format that can be used very easily for other
things. If I were doing it, I'd try to make my data look more like:


Clothes DressPants Long Regular
Clothes DressPants Short Loose
Clothes Skirt Pleated Casual
.....

One row with something in each column (if it applies to that row)


whatzzup wrote:

Good afternoon all, i am struggling to create a spreadsheet looking like this
below, this is an hierarchical dropdown, i know its not simple.

Product Type Desc extra
Clothes
Dress
Pants
Long
Regular
Short
Loose
Skirt
Pleated
Causual
Mini
Party
Sweater
Longsleeves
Black
Short sleeves
Red
Blue
Vegetable
Lettuce
x
y
I would appreciate this very much i have been looking at the Dalglish
template and cant seem to be able to match this.
Very much appreciated....

--

Dave Peterson

Dropdown Sheet
 

Maybe this, similar to Dalglish instructions:

Make a copy of the data
Choose F5 Special blanks
press [=] followed by [Up Arrow] then [Enter]
Select all data and copy and paste values
Delete any rows you don't want to show

"whatzzup" wrote:

Good afternoon all, i am struggling to create a spreadsheet looking like this
below, this is an hierarchical dropdown, i know its not simple.

Product Type Desc extra
Clothes
Dress
Pants
Long
Regular
Short
Loose
Skirt
Pleated
Causual
Mini
Party
Sweater
Longsleeves
Black
Short sleeves
Red
Blue
Vegetable
Lettuce
x
y
I would appreciate this very much i have been looking at the Dalglish
template and cant seem to be able to match this.
Very much appreciated....

Dropdown Sheet
 

Hi Lori, thanks. I tried using the instruction but i ahve failed maybe i
misunderstand the command. I copy data,f5,special,blanks,ok, =, up
arrow,enter, select all data, copy paste special values. I did all that so
maybe i miss a step please can you put me right...thanks a lot

"Lori" wrote:

Maybe this, similar to Dalglish instructions:

Make a copy of the data
Choose F5 Special blanks
press [=] followed by [Up Arrow] then [Enter]
Select all data and copy and paste values
Delete any rows you don't want to show

"whatzzup" wrote:

Good afternoon all, i am struggling to create a spreadsheet looking like this
below, this is an hierarchical dropdown, i know its not simple.

Product Type Desc extra
Clothes
Dress
Pants
Long
Regular
Short
Loose
Skirt
Pleated
Causual
Mini
Party
Sweater
Longsleeves
Black
Short sleeves
Red
Blue
Vegetable
Lettuce
x
y
I would appreciate this very much i have been looking at the Dalglish
template and cant seem to be able to match this.
Very much appreciated....

Dropdown Sheet
 

Are you trying to fill the empty cells with the value of the cell above it?

If yes:

Select the range
F5|Special|blanks|ok
type =
hit the up arrow key
hit ctrl-enter (to fill all the blank cells with that formula)

Now select all the range
edit|copy
Edit|paste special|values

From your description, it looks like the copy data wasn't needed and you didn't
hit ctrl-enter instead of enter when filling the empty cells.

whatzzup wrote:

Hi Lori, thanks. I tried using the instruction but i ahve failed maybe i
misunderstand the command. I copy data,f5,special,blanks,ok, =, up
arrow,enter, select all data, copy paste special values. I did all that so
maybe i miss a step please can you put me right...thanks a lot

"Lori" wrote:

Maybe this, similar to Dalglish instructions:

Make a copy of the data
Choose F5 Special blanks
press [=] followed by [Up Arrow] then [Enter]
Select all data and copy and paste values
Delete any rows you don't want to show

"whatzzup" wrote:

Good afternoon all, i am struggling to create a spreadsheet looking like this
below, this is an hierarchical dropdown, i know its not simple.

Product Type Desc extra
Clothes
Dress
Pants
Long
Regular
Short
Loose
Skirt
Pleated
Causual
Mini
Party
Sweater
Longsleeves
Black
Short sleeves
Red
Blue
Vegetable
Lettuce
x
y
I would appreciate this very much i have been looking at the Dalglish
template and cant seem to be able to match this.
Very much appreciated....

--

Dave Peterson

Dropdown Sheet
 

Hi, Thanks again, that was kind of what i wanted to a point, but to only
display unique value and display it just as i show it in the original
question. I have now done what you said but for example a2:a19 is populated
with the same data "clothes" what i wanted was Clothes to be only displayed
in a2. Same with "pants" only displayed in b4.
So Dress, pants,skirt & blouse would be a selection from Clothes and say
Pleated and Mini was a selection from Skirt.

Think that can be done in excel?

"Dave Peterson" wrote:

Are you trying to fill the empty cells with the value of the cell above it?

If yes:

Select the range
F5|Special|blanks|ok
type =
hit the up arrow key
hit ctrl-enter (to fill all the blank cells with that formula)

Now select all the range
edit|copy
Edit|paste special|values

From your description, it looks like the copy data wasn't needed and you didn't
hit ctrl-enter instead of enter when filling the empty cells.

whatzzup wrote:

Hi Lori, thanks. I tried using the instruction but i ahve failed maybe i
misunderstand the command. I copy data,f5,special,blanks,ok, =, up
arrow,enter, select all data, copy paste special values. I did all that so
maybe i miss a step please can you put me right...thanks a lot

"Lori" wrote:

Maybe this, similar to Dalglish instructions:

Make a copy of the data
Choose F5 Special blanks
press [=] followed by [Up Arrow] then [Enter]
Select all data and copy and paste values
Delete any rows you don't want to show

"whatzzup" wrote:

Good afternoon all, i am struggling to create a spreadsheet looking like this
below, this is an hierarchical dropdown, i know its not simple.

Product Type Desc extra
Clothes
Dress
Pants
Long
Regular
Short
Loose
Skirt
Pleated
Causual
Mini
Party
Sweater
Longsleeves
Black
Short sleeves
Red
Blue
Vegetable
Lettuce
x
y
I would appreciate this very much i have been looking at the Dalglish
template and cant seem to be able to match this.
Very much appreciated....

--

Dave Peterson

Dropdown Sheet
 

I'd keep the data there, but I'd hide it using Format|Conditional formatting

See Debra Dalgleish's site for a method:
http://contextures.com/xlCondFormat03.html#Duplicate

whatzzup wrote:

Hi, Thanks again, that was kind of what i wanted to a point, but to only
display unique value and display it just as i show it in the original
question. I have now done what you said but for example a2:a19 is populated
with the same data "clothes" what i wanted was Clothes to be only displayed
in a2. Same with "pants" only displayed in b4.
So Dress, pants,skirt & blouse would be a selection from Clothes and say
Pleated and Mini was a selection from Skirt.

Think that can be done in excel?

"Dave Peterson" wrote:

Are you trying to fill the empty cells with the value of the cell above it?

If yes:

Select the range
F5|Special|blanks|ok
type =
hit the up arrow key
hit ctrl-enter (to fill all the blank cells with that formula)

Now select all the range
edit|copy
Edit|paste special|values

From your description, it looks like the copy data wasn't needed and you didn't
hit ctrl-enter instead of enter when filling the empty cells.

whatzzup wrote:

Hi Lori, thanks. I tried using the instruction but i ahve failed maybe i
misunderstand the command. I copy data,f5,special,blanks,ok, =, up
arrow,enter, select all data, copy paste special values. I did all that so
maybe i miss a step please can you put me right...thanks a lot

"Lori" wrote:

Maybe this, similar to Dalglish instructions:

Make a copy of the data
Choose F5 Special blanks
press [=] followed by [Up Arrow] then [Enter]
Select all data and copy and paste values
Delete any rows you don't want to show

"whatzzup" wrote:

Good afternoon all, i am struggling to create a spreadsheet looking like this
below, this is an hierarchical dropdown, i know its not simple.

Product Type Desc extra
Clothes
Dress
Pants
Long
Regular
Short
Loose
Skirt
Pleated
Causual
Mini
Party
Sweater
Longsleeves
Black
Short sleeves
Red
Blue
Vegetable
Lettuce
x
y
I would appreciate this very much i have been looking at the Dalglish
template and cant seem to be able to match this.
Very much appreciated....

--

Dave Peterson


--

Dave Peterson

Dropdown Sheet
 

Dave, thanks this is the closest that i have come to exactly what i want it
does display the exact layout but the areas that is hidden with the formatted
text could still be selected and i would hope that they were locked.
Is there a conditional format that will allow cells to be locked if the font
colour is defined?

"Dave Peterson" wrote:

I'd keep the data there, but I'd hide it using Format|Conditional formatting

See Debra Dalgleish's site for a method:
http://contextures.com/xlCondFormat03.html#Duplicate

whatzzup wrote:

Hi, Thanks again, that was kind of what i wanted to a point, but to only
display unique value and display it just as i show it in the original
question. I have now done what you said but for example a2:a19 is populated
with the same data "clothes" what i wanted was Clothes to be only displayed
in a2. Same with "pants" only displayed in b4.
So Dress, pants,skirt & blouse would be a selection from Clothes and say
Pleated and Mini was a selection from Skirt.

Think that can be done in excel?

"Dave Peterson" wrote:

Are you trying to fill the empty cells with the value of the cell above it?

If yes:

Select the range
F5|Special|blanks|ok
type =
hit the up arrow key
hit ctrl-enter (to fill all the blank cells with that formula)

Now select all the range
edit|copy
Edit|paste special|values

From your description, it looks like the copy data wasn't needed and you didn't
hit ctrl-enter instead of enter when filling the empty cells.

whatzzup wrote:

Hi Lori, thanks. I tried using the instruction but i ahve failed maybe i
misunderstand the command. I copy data,f5,special,blanks,ok, =, up
arrow,enter, select all data, copy paste special values. I did all that so
maybe i miss a step please can you put me right...thanks a lot

"Lori" wrote:

Maybe this, similar to Dalglish instructions:

Make a copy of the data
Choose F5 Special blanks
press [=] followed by [Up Arrow] then [Enter]
Select all data and copy and paste values
Delete any rows you don't want to show

"whatzzup" wrote:

Good afternoon all, i am struggling to create a spreadsheet looking like this
below, this is an hierarchical dropdown, i know its not simple.

Product Type Desc extra
Clothes
Dress
Pants
Long
Regular
Short
Loose
Skirt
Pleated
Causual
Mini
Party
Sweater
Longsleeves
Black
Short sleeves
Red
Blue
Vegetable
Lettuce
x
y
I would appreciate this very much i have been looking at the Dalglish
template and cant seem to be able to match this.
Very much appreciated....

--

Dave Peterson


--

Dave Peterson

Dropdown Sheet
 

No. But you could lock the cells that you don't want changed and then protect
the worksheet.

In xl2003, you'd use:
format|cells|protection tab

and

Tools|Protection|protect sheet

Make sure you unlock the cells you want to allow the user to change.

ps. Worksheet protection is easily broken. So don't think of it as Fort Knox!

whatzzup wrote:

Dave, thanks this is the closest that i have come to exactly what i want it
does display the exact layout but the areas that is hidden with the formatted
text could still be selected and i would hope that they were locked.
Is there a conditional format that will allow cells to be locked if the font
colour is defined?

"Dave Peterson" wrote:

I'd keep the data there, but I'd hide it using Format|Conditional formatting

See Debra Dalgleish's site for a method:
http://contextures.com/xlCondFormat03.html#Duplicate

whatzzup wrote:

Hi, Thanks again, that was kind of what i wanted to a point, but to only
display unique value and display it just as i show it in the original
question. I have now done what you said but for example a2:a19 is populated
with the same data "clothes" what i wanted was Clothes to be only displayed
in a2. Same with "pants" only displayed in b4.
So Dress, pants,skirt & blouse would be a selection from Clothes and say
Pleated and Mini was a selection from Skirt.

Think that can be done in excel?

"Dave Peterson" wrote:

Are you trying to fill the empty cells with the value of the cell above it?

If yes:

Select the range
F5|Special|blanks|ok
type =
hit the up arrow key
hit ctrl-enter (to fill all the blank cells with that formula)

Now select all the range
edit|copy
Edit|paste special|values

From your description, it looks like the copy data wasn't needed and you didn't
hit ctrl-enter instead of enter when filling the empty cells.

whatzzup wrote:

Hi Lori, thanks. I tried using the instruction but i ahve failed maybe i
misunderstand the command. I copy data,f5,special,blanks,ok, =, up
arrow,enter, select all data, copy paste special values. I did all that so
maybe i miss a step please can you put me right...thanks a lot

"Lori" wrote:

Maybe this, similar to Dalglish instructions:

Make a copy of the data
Choose F5 Special blanks
press [=] followed by [Up Arrow] then [Enter]
Select all data and copy and paste values
Delete any rows you don't want to show

"whatzzup" wrote:

Good afternoon all, i am struggling to create a spreadsheet looking like this
below, this is an hierarchical dropdown, i know its not simple.

Product Type Desc extra
Clothes
Dress
Pants
Long
Regular
Short
Loose
Skirt
Pleated
Causual
Mini
Party
Sweater
Longsleeves
Black
Short sleeves
Red
Blue
Vegetable
Lettuce
x
y
I would appreciate this very much i have been looking at the Dalglish
template and cant seem to be able to match this.
Very much appreciated....

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson