Validation issue #2

I have set up my cells on one sheet with validation to a list. The list is 
on a different sheet. I accomplished this be selecting the 20 possible cells 
to contain data in the list and Insert|Name called MyList. Now on the other 
sheet, for the cell validation the source is: =MyList

The issue is not all 20 cells will hold content at all times (depends on how 
many activities the user needs to define). When a user clicks the pull-down 
to choose from the list, the pull-down starts at the first blank cell ....so 
you have to scroll up on the pull-down list to see any content.

 I know it works fine when the source is from the same sheet and you just 
select the range.

Is there a way to force the pull-down list to start at the first cell? 
.....or is a limitation to use this method of gathering validation data from 
a different sheet?

I am using Excel 2000

-ADK 


0
ADK1 (22)
9/11/2006 6:55:17 PM
excel 39879 articles. 2 followers. Follow

1 Replies
493 Views

Similar Articles

[PageSpeed] 18

It works exactly the same on  the same sheet as far as I can see.

If the blanks are only at the start of the range, you could try  formula
like so in the name

=OFFSET(Sheet3!$H$1,COUNTBLANK(Sheet3!$H$1:$H$10),0,COUNT(Sheet3!$H:$H),1)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"ADK" <ADK@noreply2today.com> wrote in message
news:O9heMOd1GHA.1256@TK2MSFTNGP04.phx.gbl...
> I have set up my cells on one sheet with validation to a list. The list is
> on a different sheet. I accomplished this be selecting the 20 possible
cells
> to contain data in the list and Insert|Name called MyList. Now on the
other
> sheet, for the cell validation the source is: =MyList
>
> The issue is not all 20 cells will hold content at all times (depends on
how
> many activities the user needs to define). When a user clicks the
pull-down
> to choose from the list, the pull-down starts at the first blank cell
.....so
> you have to scroll up on the pull-down list to see any content.
>
>  I know it works fine when the source is from the same sheet and you just
> select the range.
>
> Is there a way to force the pull-down list to start at the first cell?
> ....or is a limitation to use this method of gathering validation data
from
> a different sheet?
>
> I am using Excel 2000
>
> -ADK
>
>


0
9/11/2006 7:59:10 PM
Reply:

Similar Artilces:

Combining Cells #2
I need the easiest way to do this: I have a column with values. Sometimes there are 2 rows, sometimes 20. For another program, I need all the values in that column in one long string separated by commas. So if my orginal data is: 8766 7788 9987 What I need excel to spit out is: 8766,7788,9987 How do I do this given that my number of rows can vary. I don't mind at all copying and pasting a function in that handles this, but just not sure how to go about it. Any help is appreciated. Thanks! Let's say the numbers start in A1 In B1 enter = A1 In B2 enter =B1&","&...

ListCtrl #2
Hello I have a bitmap background and I want my List Control to have transparency so the background goes through, Is there anyway to do this? TIA Nancy ...

Print Issue and Saving Issue
Dear All Firstly, is it possible to use VB code to create a macro that allows m to save multiple worksheets in a workbook as csv files and the name o the csv files to be defined by the tab names... for example UK(Sheet1 would be saved as UK.csv... and so on... Secondly, is it possible to create a macro that prints define worksheets... I only want to print 5 pages of data if there's more tha 5 pages of data and if there's less than 5 pages of data, then prin all... hence all print outs should not be more than 5 pages... Also, a my vb knowledge is limited, i only know how to define my ...

Batch Not Posting #2
Hello all, we have an issue where when we attempt to post from several different modules it gets so far then kills great plains. we have to go into the batch recovery run the batch recovery at which time it will print the reports them GP dies again. then we have to go back into batch recovery and hit cancel on all the reports and it will them post the batch. TIA ...

excel based budgeting #2
I amusing Excel Based Budgeting, the spreadsheet is getting created, yet created blank. I know the system created the spreadhseet b/c the worksheet page 1 gets named the Budget ID that we started with. this workstation/user can export with no issue from smart list At the same time we are working on exporting the budget, on another workstaion we were processing a pretty big GL integration any ideas? ...

sequence numbers #2
how i can put sequence number after applying filter. if anyone can help me on this matter, i'll be very thankful. If your list begins in cell A1.... Insert a column and put SEQ in the "new" cell A1. Then put this formula in Cell A2 and copy down: =SUBTOTAL(3,B$2:B2) Now filter your list. That formula will count the number of non-blank, visible cells. Note the dollar sign in the formula. Cell A3 will count the non-blank, visible cells from A2:A3. Cell A5 will count them from A2:A5, etc. Does that help? -- Regards, Ron "Ashley" wrote: > how i can put se...

Issue when exporting into Excel in 3.0
Hi, One of the problem that i notice after clicking export button on opportunity form, the crm form will close down and open with the excel form. As i remember i did not encounter this problem in previous version. After i exported the file, another excel form was loaded. The CRM form was still available. What's wrong with this?? Did i miss some settiing here?? Carrie Hi, I notice the same when exporting files to MS Excel... Really bad that we had to open a new web browser window and login again :( "Carrie" wrote: > Hi, > > One of the problem that i notice aft...

CRM Mobile Express Issues
have an issue with CRM Mobile Express access. It works, if I have no entities published. Once I publish an entity (any entity), I get this error just going to the home page: ---- Message: Exception of type 'System.Web.HttpUnhandledException' was thrown. Stack trace: at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessReques...

validating xml doc with multiple schemas using wildcards
I am trying to validate a xml file with two schema files cust.xsd and cust1.xsd. The schema file cust.xsd allows addition of elements from another schema cust1.xsd by using xs:any wildcard. cust.xsd - schema <?xml version="1.0" encoding="utf-8" ?> <xs:schema targetNamespace="urn:xmlns:sysapex-com:customer" elementFormDefault="qualified" xmlns="urn:xmlns:sysapex-com:customer" xmlns:mstns="urn:xmlns:sysapex-com:customer" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="Customer"&...

Trim function #2
How can I have displayed the figures (cells) used, when I run a Trim function. Hi not really sure what you're rying to achieve. Could you give an example? >-----Original Message----- >How can I have displayed the figures (cells) used, when I run a Trim function. >. > When you use the TRIM Worksheet Function by itself the result is displayed in the cell. =TRIM(B1) If you want to know what B1 actually contains, you could use CODE for each byte, but easier would be Chip Pearson's Cell View -- http://www.cpearson.com/excel/CellView.htm For a maco to trim all cells i...

Current Issues Article Archive
A growing archive of fascinating media articles on current issues & affairs. Available at Arcis Foundation Website: www.arcis.co.uk/php/ -- Posted by News Bulk Poster Unregistered version ...

Compare 2 rows of data and update the first
I have 2 lists both have the same data in each (account names). Th first list is the master and the second list is a new accounts list. I would like to compare the second list to the first one and if th account is not in the first list then I would like it added to list 1. The second list changes frequently so I am looking to automate thi process. I am able to do vlookups to check for matches but I am unsure of how t perform the additional tasks of updating. Is there a simple solution with formulas or do I need to insert macro/program? thank you for your time and help, Bob -- Message ...

"No more fonts may be applied in this workbook." when saved. Why? #2
"No more new fonts may be applied in this workbook." Message box when spread sheet is saved. Why? ...

ActiveX issue in PowerPoint 2003
I used More Tools/"Microsoft Forms 2.0 Frame" to add a scrollbar to one slide's image. This works fine, but now every time I open the saved file I get the following message: "This application is about to initialize ActiveX controls that might be unsafe. If you trust the source of this document, select Yes ..." Well, of course I trust the doc, because I'm the one who created it. The message also states "If you have changed the default setting for ActiveX initialization during the installation process ... the system will perform the initializati...

Trying to work with Transaction.DropAmount #2
I have been tasked with making an addition to the status.htm that will display a notification when the cash in the drawer has reached a particular value. I'm familiar with HTML and VB Script, but I've been going over the customization guide provided by MS and I'm a bit lost. The syntax that I see doesn't appear to be working for me or perhaps I just forgot how to do things. If anyone has any advice on how to access the values of Transaction.DropAmount or the value of cash in the cash drawer, I would really appreciate it. Thanks, Andrew Handerson ...

0x800CCC0F error #2
OL2003 I have my POP and SMTP settings correct (I have run test message and it sends and receives) yet when OL automatically sends and receives, I get the above error with server responded on 553 unknown address. I have increased server timeout but still nothing, any help is appreciated It's a bit tough to know exactly what the problem is but one suggestion is that you have 2 email addresses set up; one which is working and one with erroneous settings - maybe set up by mistake when you were configuring OL. Oliver "T5" <noanswer@hotmail.com> wrote in message ne...

Round Issue
Hi, Here is my problem I am summing a column of numbers to the secound decemal place however when I sum the values get a number broken out past the the secound decemal place why. the example below shows the issue however the column of is 1149 long. example: 1.87 2.00 4.90 0.87 Total 9.6399999999 Are the numbers in the column entered or calculated? If calculated, then although you have it formatted to show only two decimals, it retains the full value and so you're getting the answer that you do. To check, reformat the cells to show, say four decimal places and check the values...

Show <> Records Between 2 Tables ?? (ANTI-Join)
Folks, I've got 2 tables. One has 2000 records (original) The Second has 1000 (created from the Original with 1000 records removed). There is a unique ID (which is in the Original and the Second for matching like records...the CustomerNum). I'm familiar with creating a query that would join them at the CustomerNum.... which would generate a list of records with matching CusterNums in each table) In the New Query dialog box, there's a wizard to create an Unmatched Query. That's what you're looking for. One of the tables has to be the "base" table, so you m...

Data Validation #4
In excel I want to validate a cell where no more than 14 digits should be entered. And I want another cell to be validated to be entered as text only and any number should be restricted? Please help me with this. Not sure how to do it. Thanks Select cell1 and DV>Allow>Whole Number 0 to 14 Select cell2 and Allow>Custom =ISTEXT(cell2) Where cell2 is your cell reference like A1 Note: this does not prevent someone entering a number preceded by an apostrophe which Excel thinks is text. Gord Dibben MS Excel MVP On Thu, 19 Apr 2007 12:54:04 -0700, Liz <Liz@discussions.micro...

Line graphs #2
I am using a line graph in Excel 2007. It has 3 variables. One of the variables, I changed to a column graph. When the data is plotted:this yr sales,last yr sales and budget, on the bottom of the graph shows dates for every week of data that I have for this year. How do you just show dates for every 4th week instead of every week. I had that in my scatter graph and I think it looks cleaner. Thanks Hi, You select the X-axis Choose Chart Tools, Format, Format Selection, Axis Options tab, Major Unit, Fixed, Days, 28. -- Thanks, Shane Devenshire "Charles Eaves" wrote: >...

FRx log in issue
I'm currently experiencing a problem logging into the FRx designer or launcher. What happens is after a day or two of not using FRx, I try to log in and get a "Try Again?" message. This happens with all the clients logging into the database; regardless of the company they are trying to access. The work-around that I have is to go into the company information, change the ODBC path to something else and then change it back. Once I save it, I'm asked for a user ID and password. FRx will take the user name and password at that time. After that I'll be able to ...

workbook macro help #2
Hello there, I would like some help with a macro that I need to run in a workbook. this is the macro. Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Columns("A:Z").EntireColumn.AutoFit End Sub what or how do I get it to work for every worksheet in the workbook, I currently place the macro in each worksheet which is very time consuming. Is there a way to place the macro in the workbook so all worksheets have the autofit macro? regards Ditchy Hi ditchy, Try putting the following code into the ThisWorkbook module (replace the names of the sheets to single out as ...

exported excel charts and pdflatex #2
pdfLatex is compatible with jpg and pdf images, and real pdfs loo better than jpgs it seems. I found that the best way was to make a .p file of the chart by printing to a postscript printer, and the converting to pdf. All is done in vba (uses ghostscript) -- hk ----------------------------------------------------------------------- hke's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=155 View this thread: http://www.excelforum.com/showthread.php?threadid=26893 JPG is the wrong format for most charts anyway, being designed for photographic-type images with cont...

List box #2
Simple question for all you MFC guru's. How do you change the back color for a list box? Thanks >Simple question for all you MFC guru's. How do you change >the back color for a list box? Mark, Handle the WM_CTLCOLORLISTBOX notification message - in MFC this is done via OnCtlColor CTLCOLOR_LISTBOX. Dave -- MVP VC++ FAQ: http://www.mvps.org/vcfaq ...

Memory Issue ---------
Any body noticed Extremely high percentage of physical memory in use on Exchange 2007 MBX and CAS and HTservers in the past few weeks??? No updates or changes were made to Windows or Exchange Server. Advise Please. Thanks, On Wed, 30 Dec 2009 07:20:01 -0800, WildPacket <WildPacket@discussions.microsoft.com> wrote: > >Any body noticed Extremely high percentage of physical memory in use on >Exchange 2007 MBX and CAS and HTservers in the past few weeks??? Well, yeah . . . but that's the way it's supposed to be, isn't it? >No updates or c...