Dropdown Sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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....
 
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)
 
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
 
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 said:
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 said:
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....
Click to expand...
Click to expand...
 
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.
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 said:
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 said:
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....
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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.
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 said:
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

:

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....
Click to expand...
Click to expand...
Click to expand...
 
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
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 said:
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.
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

:

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

:

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....
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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
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 said:
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

:

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

:

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....
Click to expand...
Click to expand...
Click to expand...
 
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!
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 said:
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
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?

:

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

:

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

:

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....
Click to expand...
Click to expand...
Click to expand...
 
Back
Top