Skip multiple blanks

The following details will skip blank cells in a range so that when creating
a custom validation list there will be no blanks.

BA4:BA500 contain customer names

BB4:BB500 contains the formula:
=IF(INDIRECT("BA"&ROW(IV4))="","",ROW())

BC4:BC500 contains the formula:
=INDEX(INDIRECT("$BA$4:$BA$500"),MATCH(SMALL($BB$4:$BB$500,ROWS($IV$1:IV1)),
$BB$4:$BB$500,0))

Defined range with the name SkipCustomerBlanks:
=OFFSET(Customer!$BC$4,0,0,SUMPRODUCT(--NOT(ISERROR(Customer!$BC$4:$BC$500))
),)

When a customer name is deleted in BA the range BC4:BC500 then moves up one
cell and the error #NUM! appears on the bottom cell. This does not cause a
problem, the problem I am encountering is when a large number of customer
names are deleted there comes a point where instead of the error #NUM!
appears a zero (0) is returned instead. Where a zero is returned to a cell
in BC4:BC500 the validation list will then display the zero (0) in the
dropdown list.

Anyone know what causes this to happen?
Much appreciate if you can help.
Pat


0
2/5/2005 5:12:17 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
668 Views

Similar Articles

[PageSpeed] 43

I have found what was causing the zeros to appear in the val list. Some
cells contained a formula which returned zero. I have removed these
formulas.

Pat


"Pat" <glass_patrick@hotmail.com> wrote in message
news:uC$X5V6CFHA.3256@tk2msftngp13.phx.gbl...
> The following details will skip blank cells in a range so that when
creating
> a custom validation list there will be no blanks.
>
> BA4:BA500 contain customer names
>
> BB4:BB500 contains the formula:
> =IF(INDIRECT("BA"&ROW(IV4))="","",ROW())
>
> BC4:BC500 contains the formula:
>
=INDEX(INDIRECT("$BA$4:$BA$500"),MATCH(SMALL($BB$4:$BB$500,ROWS($IV$1:IV1)),
> $BB$4:$BB$500,0))
>
> Defined range with the name SkipCustomerBlanks:
>
=OFFSET(Customer!$BC$4,0,0,SUMPRODUCT(--NOT(ISERROR(Customer!$BC$4:$BC$500))
> ),)
>
> When a customer name is deleted in BA the range BC4:BC500 then moves up
one
> cell and the error #NUM! appears on the bottom cell. This does not cause a
> problem, the problem I am encountering is when a large number of customer
> names are deleted there comes a point where instead of the error #NUM!
> appears a zero (0) is returned instead. Where a zero is returned to a cell
> in BC4:BC500 the validation list will then display the zero (0) in the
> dropdown list.
>
> Anyone know what causes this to happen?
> Much appreciate if you can help.
> Pat
>
>


0
2/5/2005 9:00:00 PM
Reply:

Similar Artilces:

Multiple Combo Boxes Highlighted
Hi, I have a problem with something in VB6. I have 4 combo boxes with individual names (not an array) located on an SSTab object. When I select another tab and then return to the tab these boxes are located on, each of them appears to be highlighted in blue. I add values to them only when loading the form so I'm at a loss as to why this happens ? Any ideas are greatly appreciated. Thanks, Jen. "Jennifer Ward" <jward@comcast.net> wrote in message news:eq5HrcBrKHA.6064@TK2MSFTNGP02.phx.gbl... > Hi, > > I have a problem with someth...

adding a skip pattern
Does MS Access refer to "skip patterns" by a different name? Is there any relatively simple way to include skip patterns in an Access form? Example: if the answer to question 1 = no, then skip to question 6. In a previous post, the reply below was given, but I must confess that I am not so swift to fully understand. Can someone provide a bit more detail: "You'd used the afterupdate event on a given control then use the setfocus method to move the cursor to another control and use the Enabled control property to control whether or not other controls remain active or...

selecting multiple choices from a drop down list
I have a drop down list in a cell and I want to be able to selec multiple entries from that list. Does anyone know how I go this -- Message posted from http://www.ExcelForum.com If this is a data validation list, you could use code to compile a list of selected items. For example, if the data validation is in column C, the following code will store the selected items in the same row in column D: '============================= Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Application.EnableEvents = False If Target.Count > 1 Then Exit Sub On Error Resume N...

use IF to return a truly blank cell
I often use if statements to return empty cells, for example: =IF(a1=0,"ERROR","") The trailing "" returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one "ERROR" cell to the next by hitting Ctrl arrow-down because it seems to think that these empty cells have contents. Is there a way to designate, in such a formula as above, to return a TRULY empty cell? Thanks -- cwinters ------------------------------------------------------------------------ cwinters's Profile: ht...

Skip if empty
Everyone, I'm sure this is an easy fix, but it is just beyond me. Hopefully, one of you is going to make me feel stupid. I have an excel 2003 workbook that is a template for a merge document in Word. I need it to be able to handle 10 different sets of information starting in column B and ending in column J. I have all the information for the Word document in sheet1 of the workbook, and I'm going to merge from sheet2. I'm not always going to have all 10 of the columns filled with information. so I need to be able to skip code when I run out of records so that when I merge them...

RM Statement on Blank Paper
I am making some changes on the RM Statement on Blank Paper. Currently Statement Name, Address 1, Address 2, and Address 3 all print in uppercase. I created a calculated field City-State-Zip which prints in the appropriate upper/ lower cases. How can I adjust the Statement Name, Address 1, Address 2, and Address 3 to print in the appropriate cases? I tried to adjust the Format Definition but this had no effect. If it is not possble to make those fields print in the appropriate case, how can I make my calculated field print in uppercase? Thanks for your help. in report writer ther...

multiple watermarks
I wish to have multiple watermarks in one document but Word seems to only allow one regardless of how many sections there are. Could anybody suggest a way in which I could have different watermarks in different sections? All responses received with thanks. Kind Regards A watermark is nothing more nor less than a washed-out graphic anchored to the header. If you unlink headers in the different sections, you can insert different watermarks. See “HOW TO: Create a Watermark in Word 97 and in Microsoft Word 2000” at http://support.microsoft.com/?kbid=211324 and http://sbarnhil...

file skipped
HI,All The following file is being skipped, should I be concerned..???? Backup - \\USFIN2KEX1\D: Data Unable to open the item \\USFIN2KEX1\D:\exchsrvr\imcdata\queue.dat - skipped. thanks Do online, exchange aware backups of the store. Do not do flat-file backups of the exchsrvr dirs and you wont see that error. :) On Sun, 20 Jun 2004 10:35:33 -0400, "Darren D" <Darren@somewhere.com> wrote: >HI,All >The following file is being skipped, should I be concerned..???? >Backup - \\USFIN2KEX1\D: Data >Unable to open the item \\USFIN2KEX1\D:\exchsrvr\imcdata\queue.da...

Multiple selection in the page area of a pivot table
Is there any way i can have multiple selection check boxes in the page area of a pivot table? ...

multiple sending addresses
How can I set up Outlook to have multiple sending addresses. I have set up two accounts, but in the email window of Outlook there is no From: with the pulldown listing the from address choice as there is in Outlook Express. Please Help. Lu Winberg "lucenak@yahoo.com" <anonymous@discussions.microsoft.com> wrote in message news:123501c3b8c2$96ed0db0$7d02280a@phx.gbl... > How can I set up Outlook to have multiple sending > addresses. I have set up two accounts, but in the email > window of Outlook there is no From: with the pulldown > listing the from address...

Multiple inheritance problem
Hi, I want to make a class, JNDlg, which is derived from two MFC-classes (CDialog and CStringArray). The class declaration (a bit simplified) looks as follows: class JNDlg : public CDialog, public CStringArray { // Construction public: JNDlg(CWnd* pParent = NULL); // standard constructor // Dialog Data //{{AFX_DATA(JNDlg) enum { IDD = IDD_JNW }; //}}AFX_DATA //{{AFX_VIRTUAL(JNDlg) protected: virtual void DoDataExchange(CDataExchange* pDX); // DDX/DDV support //}}AFX_VIRTUAL protect...

Saving Multiple attatchments Simultaeously from multiple Emails
I would like to save all the attachments from 100 to one file from 100+ emails. Can anyone help? Try "You Software". I think they have a solution for that. Good luck! R. "Jorge P." <Jorge P.@discussions.microsoft.com> wrote in message news:B5AA5C08-9B86-4D41-8AC8-10DE615AA7BC@microsoft.com... > I would like to save all the attachments from 100 to one file from 100+ > emails. > > Can anyone help? > > Jorge P. <Jorge P.@discussions.microsoft.com> wrote: > I would like to save all the attachments from 100 to one file from > 100...

Excel 2K: Print of multiple copies is very slow
Hello we have a problem in our company with printing from Excel 2000 to Laserjet 2300 (local). If we print one Word-document 20 times, that takes very fast. If we print one Excel-document (one page) 20 times, the first 13 pages are getting printed very fast too, but for each of the last 7 pages it takes about 5 seconds. -We tried PCL6, PCL5 and PS-drivers. We tried other drivers from Microsoft (ie LJ2100). -There are no pictures of complex data in the Excel-Sheet, it's very simple -We increased the LJ2300-memory from 32MB to 160MB -We tested with Parallel- and USB-cable --> always th...

Outlook 2003
My Outlook 2003 (with Business Manager) is sending multiple copies of emails to receipients. In other words, instead of sending one copy of the email to each receipient, it somes sends as many as 16 copies of the same email to the receipient. I am using the latest versions of McAfee VirusScan and Firewall with automatic updates. My system has been scanned for viruses by McAfee online with negative results. Can anyone clue me in? Is this a virus missed by McAfee or have I done something in the files to cause this problem? Chris I am having the same problem, except the thing i...

Ability to skip/not skip lines on the Blank Invoice Form
Would like the ability to skip or not skip lines on the Blank Invoice Form, similar to how GPA let you enter a blank line between items. ---------------- 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/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/defa...

Prevent equation from skipping data when referencing import data?
I am referencing data that is being imported from access in table on a seperate worksheet. When the data is refreshed, only the last new entry is placed in my seperate table, not all of them. How do I fix this? ...

Multiple Licenses
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC I have a family pack of Office 2008 Student and Teacher Edition, and have used two of the licenses. I just bought a new 'book and transferred files from my main computer, which gives me 2 instances of Office. Is there some way I can change the serial number of the 'book version without deleting and installing Office over again--which would negate the reason I used the transfer in the first place. Thanks for any help, Dick Z On 6/19/09 6:18 PM, in article 59b76e68.-1@webcrossing.caR9absDaxw, "RWZeren@off...

Importing Multiple Data On the Same Row
I am using Excel 2000. I have a spreadsheet with part numbers in col. A (the same part number can appear multiple times) and purchase order numbers in col. B (a purchase order number is never shared by multiple instances of the same part number). The list is very long! I need to import this data into another file which shows (in col. A) all the part numbers that we have (each part number appears only once in this spreadsheet). The problem is that I have to list ALL the purchase order numbers related to the same part number on one row, starting in col. C... So, while for some part numbers ...

Single CRM 4.0 for multiple domains?
Hello! Can a single CRM 4.0 be installed for multiple domains? These are two different domains for two different organizations but they are partners , so they prefer to have a single CRM. What kind of trust must be create between these two domains? Each domains want to use own exchange server, if possible. regards, Thor ...

Multiple lookup
Hello, Given the following information: Col A Col B Col C Company Source 1 Source 2 1 Company 1 10 ? 2 Company 1 170 ? 3 Company 1 20 ? 4 Company 1 4 ? I am trying to lookup information for 'company 1' from 'source 2', based on the information given in 'source 1'. The information for in 'source 2' is not exactly the same as in 'source 1' so I need to return the closest match. Does anyone know how this can be done? Any assistance will be greatly appreciated. Simon ...

not charting blank formula values as zero
Is is possible to not chart blank formula values as zeros. I have tried using tools, options, and chart (plot empty cells as 'not plotted'). This does not seem to work when the cell is referencing a formula. Thanks! That's because cells with formulae aren't empty. Have your formulae return the #N/A error instead - that will be ignored in the chart. For instance: =IF(A1<>0, B1/A1, NA()) You can use conditional formatting to change the font color to the background color when the cell's value is #N/A if you don't want to see the errors on your sheet. ...

If and blanks
Is using ifnumber the best way to have a formula not count blank cells as zeros? If so, can someone show me using =COUNT(C3:C31) -- LucasBuck ------------------------------------------------------------------------ LucasBuck's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19710 View this thread: http://www.excelforum.com/showthread.php?threadid=467238 Without you telling us what data (or blanks or zeros) are in your specified range, we can't understand what you are looking for. COUNT, by definition, only counts cells containing numbers (from the HELP f...

Editing multiple records
We use CRM 2.0. I wonder if there is a multi-record editing command similar to "Find & Replace". We have imported several dozen records as Leads. I wish to update the Industry field to a single value, say "Insurance". How can I do this without opening every single Lead record and changing the field manually? Thanks for the help Hello Jose, There isn't a such a global "Find & Replace" command, but the easiest way to simulate this is to create a manual workflow in WM, and then run the workflow against the subset of records you desire or compiled ...

Multiples....
Hi, I am trying to set up a cell to look up the least no. of give values to produce a given amount :) So say in column A I have the values 60, 50 & 5 and the value I a looking for is 100. How would I get excel to return a number 2 (ie 5 x 2) in column B next to the value of 50? At the moment I am gettin 60x1 & 5x8 to produce the result of 100. Thanks, Ca -- Ca ----------------------------------------------------------------------- Cam's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2783 View this thread: http://www.excelforum.com/showthread.ph...

how do i resize multiple boxes (not all) and their associated tex.
I have a drawing and I want to resize some of the boxes to 150% of the original size. I want the text to resize at the same time. ...