using range names in validation

Would like to do validation and use a list that is 
referenced by a named range.  Any way to do this?  My main 
objective is to have validation expand and contract based 
on the changing list, so if there is another, better way 
of doing this, that's fine, too.
0
boriss1 (3)
7/21/2003 6:37:50 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
413 Views

Similar Articles

[PageSpeed] 29

"boris",

Go here
http://www.contextures.com/xlNames01.html#Dynamic

then here
http://www.contextures.com/xlDataVal01.html

Rgds,
Andy


0
andy.j.brown (443)
7/21/2003 6:49:28 PM
Hi Aladin.

> =Sheet1!$A$2:INDEX(MATCH(REPT("z",255),Sheet1!$A:$A))

I tried this and got "too few arguments".

I wouldn't know where to start deciphering it, so ???

Rgds,
Andy

XL2K on XP Home.


0
andy.j.brown (443)
7/21/2003 11:56:05 PM
It should be:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))

Thanks pointing out.

"Andy Brown" <andy.j.brown@ntlworld.com> wrote in message
news:O36gsO%23TDHA.1740@TK2MSFTNGP12.phx.gbl...
> Hi Aladin.
>
> > =Sheet1!$A$2:INDEX(MATCH(REPT("z",255),Sheet1!$A:$A))
>
> I tried this and got "too few arguments".
>
> I wouldn't know where to start deciphering it, so ???
>
> Rgds,
> Andy
>
> XL2K on XP Home.
>
>


0
akyurek (248)
7/22/2003 5:28:55 AM
> It should be:
> =Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))
> Thanks pointing out.

I was sure it was an oversight. Works fine -- for text. Old standard
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)
works for values, barring gaps. I wouldn't define something with gaps as a
list, but can see your formula could be useful in terms of "binding" used
ranges for cleaning up. I will make a note of it, but would probably stick
with ActiveSheet.UsedRange.Select
then redim to take out eg: header row.

Rgds,
Andy


0
andy.j.brown (443)
7/22/2003 6:21:49 PM
Reply:

Similar Artilces:

Compare incoming FROM display name to contact
We send txt to cell phones from outlook, we have these cell users in our contacts as well, (example: phonenumber@carrier.com) is named JOE, when JOE reply’s from his cell phone the FROM display name showing in outlook from the cell phone shows numbers (example: phonenumer@carrier.com) how can I make it show the name JOE that I have for it in my contacts? or maybe some script can compare it to a list in a file and then re-write the info to outlook. Thanks The Outlook mail.SenderName property is read-only in the Outlook object model. You would need to use a lower level API ...

Using rules to filter out specific words in an HTML message
I am running outlook XP, and repeatidly receive a peice of junk mail, offering me prescription drugs. The message is nothing but an image, but there is a common peice of source code in that HTML message. However, when I add that peice of code to the "with specific words in the body" rule, it won't filter it out. How do I do that? ...

Using =sum to add values of vlookup formula
Is it possible to add the values given by a vlookup formula? Eg. I've 2 colums - Month 1 & Month 2 The values for month 1 & Month 2 are taken using a vlookup formula - s when u try & do an =sum to add month 1 & month 2, it give 0 as there i no value in either column, just a formul -- loscherlan ----------------------------------------------------------------------- loscherland's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=670 View this thread: http://www.excelforum.com/showthread.php?threadid=27237 It should give the sum. Are you sur...

Using Another PST File
Hello, I am using MS-Outlook 2003. How does 1 access a pst file, after the "owner" of that pst file, leaves the company??? The owner of this pst file has been deleted from the server(s), but their workstation is still available, & the pst file is on the workstation. Thank you, Charles L. Phillips Does not File->Open->Outlook Data File work? --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 After furious head scratching, Charles L...

Using MsgBox in Formula
Hi All, Just wondering if it is possible to use MsgBox in a Formula (withou VBA) ie =IF(A1<=10, MsgBox"Order more stock") except when I do it thi way I get the old Formula error... I thought it be good to use instea of using conditional formating to change the cell red or without havin to program it in VBA Cheers in advance guzz -- guzz ----------------------------------------------------------------------- guzzy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2243 View this thread: http://www.excelforum.com/showthread.php?threadid=48073 Good e...

HELP: problems using old version of Outlook....
Does anyone still use Outlook 97? I am having problems trying to setup an email account that uses POP/SMTP and also SPA for authentication for SMTP. POP/SMTP doesn't seem to be one of the available options in Outlook 97. The only email account types are MS Exchange, MS Mail, and something else but no SMTP or POP. Do I have to install some addon or plugins? It has to be brought up-to-date to be useful. Be sure you installed SR1 and SR2 and Internet E-mail Enhancement Patch (IMEP) for Outlook 97, then use the setup instructions for Outlook 98 found here: OL98: (CW) Configuring the ...

Keeping Sent Items in mailbox when using the Send As permission
We have set up several general purpose email accounts, and have several staff assigned to monitor and reply to them. We have set them up with Send As permission capability so they can monitor both their own personal email and the general one. The problem is when someone replies to an email from the g.p. mail account, the copy is not stored in that general purpose account's Sent Items, but in the person who sent the email. Is it possible to change this behaviour? If not, user b has no idea of what user a said in the reply email. We need the sent items on the mail account to retain ever...

Adding an extra validation to this formula.
Here is the formual and here is what I am trying to add to it. =CONCATENATE(IF(Monday!R33="4D","EST 4D",Monday!R33)," ",IF(OR(AND(F34=TRUE,G34=TRUE),AND(F34=FALSE,G34=FALSE)),H34,IF(F34=TRUE,"X","Y"))," ",Monday!D33) I would like to add if B34=True, Put in Monday!A5 and "Z" in instead of H34 or "X" OR "Y' =IF(Monday!R33="4D","EST 4D",Monday!R33)&" "& IF(B34=TRUE,Monday!A5 & " Z", IF(OR(AND(F34=TRUE,G34=TRUE),AND(F34=FALSE,G34=FALSE)),H34, IF(F34=TRUE...

Can I enfore the use of stationary
I want every email hat leaves our network to have a disclaimer on it, can I enforce this somehow? In news:%23%23rzuAzEGHA.1464@TK2MSFTNGP11.phx.gbl, Slim <me@home.com> typed: > I want every email hat leaves our network to have a disclaimer on it, > can I enforce this somehow? Do you use Exchange? If so, post in an Exchange group such as microsoft.public.exchange.admin, and include your version & SP level. If you don't use Exchange, or even manage your own mail server in-house, signatures in Outlook are your only option. http://www.slipstick.com/addins/content_...

How can I add a column of names for example
I have A2 = adrienne, A3 = Joe how do I do the formuala to make their names = 2 This formula counts the number of non-blank cells in A2:A5 =COUNTA(A2:A5) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "christina" <christina@discussions.microsoft.com> wrote in message news:28F5908A-C131-413B-87D8-7040A71C9275@microsoft.com... >I have A2 = adrienne, A3 = Joe how do I do the formuala to make their >names > = 2 "Ron Coderre" wrote: > This formula counts the number of non-blank cells in A2:A5 >...

How do I use Excel for an eigenvalue/vector problem?
Use the Poptools add in from http://www.cse.csiro.au/poptools/ -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "mr-ideahamster" <mr-ideahamster@discussions.microsoft.com> wrote in message news:E9A9213B-1AAB-41E2-8AB7-B7B3E93BDDB8@microsoft.com... > ...

Manager Error 'Bad File name or Number'
I keep getting this message on one of my machines when I try to open manager. Admin and POS both open and operate fine. I have uninstalled and reinstalled twice to no avail. Any help would be much appreciated. Thanks. Open Administrator and go to File/Configuration. Check the Paths and Templates Tabs - you probably have an invalid path or filename in one of those fields. If you are using files shared on the network, you may not have permission to access the files or folders. -- Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.com ----------------------...

#VALUE error using MoneyCentral Stock Quotes
WHy do these errors happen? I am using the current version, all options are checked, and I even uninstalled and reinstalled it. Yet half my quotes come back with the VALUE error. The Help feature does not cover my situation. ...

Sometimes have to use "Rebuild All"
I have never noticed this before, but in this project I am working on right now, I made some changes and hit "F7" like normal, but it didn't detect the changes and didn't compile the new code. So for this one file I keep having to do a "rebuild all". Any ideas why this is happening and how to fix it? Yasoo wrote: >I have never noticed this before, but in this project I am working on right now, I made some changes and hit "F7" like normal, but it didn't detect the changes and didn't compile the new code. So for this one file I keep having to...

Multiple validation rule for a single cell
Hello: I have a question? Example: If I have a validation rule for "Time In" on cell A01 Allow: Custom Formula: =AND(B10<A10, B10>"12:00:00 AM"+0, B10<"11:59:00 PM"+0) AND "Time Out" on cell B01 Allow: Custom Formula: =AND(A10>B10, A10>"12:00:00 AM"+0, A10<"11:59:00 PM"+0) How can I add validation for if the Time In cell has enter a value than Time out can not be empty or null. Vise versa, if the Time Out cell has enter a value than Time In can not be empty or null Any help will be appreciated. Thanks....

Outlook slow when selecting items using shift key and down arrow
Client: W2K Pro, Office 2000 Pro. Server: W2K Advanced Server, Exchange 2000 In Outlook, when selecting a large number of emails, say, from the Inbox, using SHIFT and the DOWN ARROW, things get progressively more sluggish. The first 10 or 20 items get highlighted pretty quickly, but it begins slowing down and levels off at about 1/2 second for each new item to be selected. Using select-all or CTRL-A highlights everything immediately, but then Outlook seems to hang for a while. It seems to be a function of the number of items in the folder. So, should I just add memory to the server, or wh...

Navigating after using find and replace
We use Excel to keep track of cost information. It is helpful to use "find" (Ctl+F) to locate the specific job number, which may appear many times in a long list of data. After we find it we typically click on the cell containing the job number and then use the arrow keys to go over several columns to check data. Because there are many rows containing the same job number it gets to be a real pain having to do this. It would be really nice not to have to move the cursor over to the cell containing the job number and clicking on it to be able to use the cursor or tab afte...

Sumif using VALUE() in sum range
Hey, I need to write a formula that does a sumif with the sumrange being in text. lets say i have this: 3377 work 00:01:16 3377 work 00:03:16 3377 work 00:01:16 3377 work 00:01:17 3377 work 00:01:16 7733 work 00:01:16 7733 work 00:01:26 7733 work 00:01:13 7733 work 00:01:16 7733 work 00:01:16 When i do =sum(Value(C1:C10)) on this range, i get a result. when i do =sumif(A1:A10,"3377",Value(C1:C10)), it won't even let me do this. clues? Don't bother with it =sumif(A1:A10,"3377",C1:C...

How to apply formatting to Excel with existing external connection ranges?
Hi, I used Advanced Find in MSCRM and exported the results in Dynamic Excel file. Now, I wish to format the list using Excel's 'Format as Table' in Home ribbon, but it warns me that an overlapping external data ranges occurred and it will be converted to a table and remove all external connections. How will avoid this? We wish to format the list and maintain the external connections from CRM. Please advise. ...

#NAME? is driving me crazy
When I load a spreadsheet from our servers on the network, I get #NAME? errors all over the place. Other people can load it just fine. Any ideas what the problem might be? Jeff, One cause may be that you don't have the Analysis Tool Pack loaded. Go to the Tools menu, choose Add-Ins and put a check next to the Analysis Tool Pack. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Jeff" <jeff@globaldataguard.com> wrote in message news:0e6c01c36270$c4897db0$a601280a@phx.gbl... > When I load...

how to use filebuf::sputc() successfully
Can anyone provide an example of how to create a file, add some content (even if just one char) with filebuf::sputc, and then close the file successfully? I could not get my PutCStringArrayIntoFile() function to work. I think it has something with the sync() command or something... A file is created, but it is empty. Can anyone provide a working code example, or have any idea why my fn doesn't work? here it is: void PutCStringArrayIntoFile(CStringArray &StringArray, CString FileName) { int nFileHandle = _sopen(FileName, _O_CREAT, _SH_DENYRW); filebuf FileBuf(nFileHandle); CSt...

Use wildcards
Find and Replace should have a "Use wildcards" option, just like Word's option, to allow search using regular expressions. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=ff9b1102-f080-474c-...

unable to use ip literals user@[x.x.x.x]
hello everybody. my exchange server 5.5 had an open relay which has now been fixed. but the IP address of my mail server got listed in www.dsbl.org. and now i am not able to send mails to our branch office which uses the list of www.dsbl.org to get rid of spam mails.now i need an address postmaster@[x.x.x.x] to receive a confirmation email from dsbl.org. i did everything that the following miscrosoft site says. http://support.microsoft.com/default.aspx?scid=kb;en- us;193316 but still i am not able to receive mails at postmaster@ [x.x.x.x]. i tested this by sending a mail to this ID ...

Don't want old co name, address in new documents
how do I get Publisher to not automatically include old info such as name, address, title in a new document? I made one set of bus cards and now all docs with those fields automatically are pupulated with these data View, personal information, clear or change all the information you don't want. This information is always included in the Wizards. You can avoid it altogether by creating great designs of your own. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "RosemarySF" <RosemarySF@discussions.microsoft.com> wr...

problem using delphi dll in vc++
hi, I am using a delphi DLL in vc++,static linked with ".h"and "lib". the export functions in DLL are "__stdcall",but the function doesn't work well,it often returns some weird values. when I add codes as follows,it suddenly works well. why?? DWORD returnAdd; __asm { mov ecx,[ebp+4] mov returnAdd, ecx } I am confused,if there is something wrong with stack,I have already using "__stdcall",why it is still wrong? I don't even know what those asm code do,I just add them to get some ...