hiding data

[Aardvark]

I have a source list for a data validation which I want to be useable for
the validation but invisible to the user. IS there a way of hiding a
selection of cells rather than hiding entire columns or rows?

Thanks,

Danny

 

[Max]

Don't think there's a way of hiding a selection of cells
rather than hiding entire columns or rows.

But you can try putting all the source lists on a separate sheet*

And use named ranges for these lists (for the Data Validation [DVs])

You can then hide the sheet which holds all your source lists for the DV
(All source lists will then be invisible to the users)

*It's also neat for maintenance purposes

For example:

Let's say you've a source list in A2:A20 in Sheet1

Name this range as say: List1

Hide the Sheet1

In Sheet2 say,

Select the range for the DV

Click Data > Data Validation

Under Allow: Choose "List"
In the "Source:" box, put : =List1

Click OK

 

[Jerry W. Lewis]

You can make the foreground color the same as the background color, but
if someone selects the cell they will see the data in the formula bar.
Even with a hidden row or column, formulas that reference the cell can
access it.

Jerry

 

[Aardvark]

Thanks Max,

It is a nice idea but I cannot find a way to make the validation work form a
separate data sheet within awork book. Thank you also Mr Lewis, I could
put a box over it I guess.

Thank you both for your help.

Danny

 

[Peo Sjoblom]

Danny, validation works from other sheets if you name the lists as opposed
to using the
reference. So Max's solution would work, you just have to remember to refer
to the list's name,
not the range..

 

[Max]

It is a nice idea but I cannot find a way to make the validation work form
a

separate data sheet within awork book.

I'm not sure why the example steps
I gave earlier did not work for you (it should work..), viz.:
----------------------------
For example:

Let's say you've a source list in A2:A20 in Sheet1

Name this range as say: List1

Hide the Sheet1

In Sheet2 say,

Select the range for the DV

Click Data > Data Validation

Under Allow: Choose "List"
In the "Source:" box, put : =List1

Click OK
----------------------------

ok, here's just some extra steps
(as a further help):

a. To name the range A2:A20 in Sheet1 as "List1"

Select A2:A20

Click inside the namebox & delete whatever's inside
(the namebox is the one with the drop arrow
just to the immediate left of the formula bar)

Type in the namebox: List1
Press Enter

b. The steps listed for:

Under Allow: Choose "List"
In the "Source:" box, put : =List1

are to be done in the "Settings" tab of the Data Validation dialog

When you select/choose "List" from the drop arrow under "Allow:"
the box for "Source:" will appear

 

[Aardvark]

Thanks!

form

I'm not sure why the example steps
I gave earlier did not work for you (it should work..), viz.:
----------------------------
For example:

Let's say you've a source list in A2:A20 in Sheet1

Name this range as say: List1

Hide the Sheet1

In Sheet2 say,

Select the range for the DV

Click Data > Data Validation

Under Allow: Choose "List"
In the "Source:" box, put : =List1

Click OK
----------------------------

ok, here's just some extra steps
(as a further help):

a. To name the range A2:A20 in Sheet1 as "List1"

Select A2:A20

Click inside the namebox & delete whatever's inside
(the namebox is the one with the drop arrow
just to the immediate left of the formula bar)

Type in the namebox: List1
Press Enter

b. The steps listed for:

Under Allow: Choose "List"
In the "Source:" box, put : =List1

are to be done in the "Settings" tab of the Data Validation dialog

When you select/choose "List" from the drop arrow under "Allow:"
the box for "Source:" will appear

--------------------------------------

Check out also MVP Debra Dalgleish's nice coverage of Data Validation
(with screen details / pictures) at her:
http://www.contextures.com/xlDataVal01.html

--
hth
Max

 

[Max]

you're welcome, Danny!

cheers
Max

 

[JTD]

You can format the cells that contain the data so that the text is stil
there but is hidden. Select cells. click "Format cells", click o
number tab, choose custom and type in ;;; in the type box. This wil
make the contents invisible. The user will be still be able to see th
contents of the cell in the formula bar if they click on the cell. T
prevent this lock the cells and apply protection to the worksheet

 

[Aardvark]

I LOVE this newsgroup!!!!!

Thanks to everyone!

Danny