Data validation with Excel 2004 on Mac OS X

  • Thread starter Thread starter nfuerstga
  • Start date Start date
N

nfuerstga

Greetings.

I've been beating my head against the wall for the past 2 hours trying
to make this work. Here is what I am doing:

I've got an Excel workbook with two worksheets (sheet1 and
ListMasters). On the ListMasters worksheet, I have defined a Name
called Cat as '=ListMasters!$H$3:$H$17'. On the worksheet sheet1, I
have a cell that I want to use data validation on. So, I select the
cell, select Data -> Validation. On the Settings dialog box, I
selected Allow to be 'List'. I set the Source to be '=Cat' and I
selected the option to allow In-cell dropdown.

It seems to me that this would work. However, when I select the cell,
I get the drop-down arrows but no drop down box. If I try to enter any
text that is not part of the Cat list, I get an error message
indicating that the value entered is not in the required list. If I
enter text in the cell that is part of the Cat list, I do not get an
error message.

So, why does the drop-down box not work?

Any suggestions would be greatly appreciated. By the way, if I open
the spreadsheet using Excel on a PC, the drop down works fine.
 
I don't use a Mac, but in the windows version, there's an option to show that
dropdown.

Data|Validation|Settings tab
with List selected, I get an option to show the "in-cell drop down"

Any chance that's not checked for you?
 
If you don't get a good reply here, maybe posting in a Mac newsgroup will help:

Stolen from an announcement in a different newsgroup:


Subject: [ANN]: We've moved to microsoft.public.mac.office.excel!! (3/27/05)
Date: Sun, 27 Mar 2005 05:00:48 -0800
From: Microsoft Macintosh Business Unit <[email protected]>
Newsgroups: microsoft.public.excel.macintosh

Dear users of microsoft.public.excel.macintosh,

We're listening! Due to extensive user feedback, we have created a new set
of newsgroups dedicated exclusively to Microsoft products for Macintosh
computers.

The new, improved newsgroup for this forum is:
microsoft.public.mac.office.excel

We understand that moving to another newsgroup can be an inconvenience, but
we ask you to bear with us as we streamline our newsgroup offerings. Our
goal is to provide you with the best possible user experience. By making
these new newsgroups available, we hope to achieve the following objectives:

- Make it easier to find newsgroups for our Mac products
- Clarify and consolidate newsgroups to avoid confusion about where
to go for answers
- Provide access to newsgroups through a Web browser

We are working very hard to make it easier for our customers to find and
communicate with each other because we recognize that online communities are
one of the most effective ways to find help, ideas, and solutions to
problems.

For a full list of Microsoft newsgroups for Macintosh computers; information
on setting up your newsreader software; as well as links that enable you to
access our newsgroups by using a Web browser, visit our Web site at:

http://www.microsoft.com/mac/community/community.aspx?pid=newsgroups

If you have any comments about this transition, please feel free to post a
message to the new newsgroup or send mail to (e-mail address removed).

If you are having technical difficulties accessing the new newsgroups please
read the information located at:

<http://www.microsoft.com/mac/community/community.aspx?pid=newsgroups> and
<http://www.microsoft.com/mac/community/community.aspx?pid=community&page=newsreaders>
We apologize for any inconvenience and hope to see you in our new newsgroup.

The Macintosh Business Unit
Microsoft

---
This message was created and posted automatically by using Word, Entourage
and AppleScript on the Mac!
 
What's the possibility that "Cat" does *not* exist on Sheet1?

It sounds to me like you entered the named range as WS specific, because you
prefixed the range name with the sheet name, preventing the range from being
globally recognized.

Click in the name box of Sheet1, and see if the drop-down list contains the
name "Cat".
If it doesn't, that's the answer to your problem.
 
Is the dropdown in a frozen part of the window?

In older versions of Excel for Windows, data validation didn't work
correctly above the freeze line. That was fixed in Excel 2000.

Maybe the Mac version still has the problem.
 
I read it that the name Cat was a workbook name and refered to the
=ListMasters!$H$3:$H$17 range.

In xl2003 (for windows), if I used a sheet level name Listmasters!cat, then I
couldn't even use that as the source for the list.

Not sure about Macs, but it sure looks like the list either has to be on that
same worksheet or have a workbook level name (on any sheet) for windows
versions.
 
I couldn't use "Cat" unless it was recognized by the sheet containing the
drop-down, but who knows (J.E.) about Macs?<g>
 
If you prefix the defined name with the sheet name, you create a sheet
level name, e.g.

Names in Workbook: ListMaster!Cat
Refers to: =ListMasters!$H$3:$H$17

The OP did not use the sheet name in the defined name, only in the
Refers to, and created a workbook level name:

Names in Workbook: Cat
Refers to: =ListMasters!$H$3:$H$17
 
You may very well be right Debra, ... BUT ... That's *not* what the OP
*said*!

Emphasis mine!
<<"On the ListMasters *WORKSHEET*, I have *DEFINED A NAME* called Cat *AS*
'=ListMasters!$H$3:$H$17">>

A WS specific name of "Cat" would be listed in the "refers to" box *exactly*
as the OP posted.
 
Actually, we should have asked if anything (sheet name) was listed on the
*right* side of the "define name" window.
 
My point was that the default behaviour is to create a workbook level
name. Extra effort is required to create a sheet level name.

The original message said the validation was working, except for the
dropdown, which would indicate that the list was recognized.

If the defined name was sheet level, on a different sheet, it wouldn't
have been allowed in the data validation dialog box.
 
You're right, and I realized that.

That's why I stated the *possibility* of this happening, not knowing about
Macs.

And you have to admit, that what he said *did* sound like he created a WS
specific name.<g>
 
I have excel 2004 on my powerbook and I can confirm that it has the same bug
as 97, no dropdown if the validation cell is within the frozen area, of
course I have not downloaded the latest service pack yet although I will as
soon as I get the darn DSL running
 
Thanks Peo. Good luck with DSL!

Peo said:
I have excel 2004 on my powerbook and I can confirm that it has the same bug
as 97, no dropdown if the validation cell is within the frozen area, of
course I have not downloaded the latest service pack yet although I will as
soon as I get the darn DSL running
Click to expand...
 
Back
Top