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 into my Word document the output is
formatted a certain way.  For example:

A2 =((Sheet1!B10&", "&Sheet1!B11&": "&Sheet1!B17&"; ")&"
"&(Sheet1!C10&", "&Sheet1!C11&": "&Sheet1!C17&"; ")&"
"&(Sheet1!D10&", "&Sheet1!D11&": "&Sheet1!D17&"; "))
So on and so forth

Say, for instance, that D10, D11, and D17 were empty, meaning that the
proceeding columns' corresponding cells are too.  I want to then stop
at C and ignore the rest.  Otherwise, I'll have a bunch of hard
returns and 0's in my Word Document that someone'll have to weed out.

Thanks!
0
kflinspach (10)
9/10/2009 7:56:16 PM
excel 39879 articles. 2 followers. Follow

1 Replies
919 Views

Similar Articles

[PageSpeed] 11

Nit_Wit_400 wrote:
> 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 into my Word document the output is
> formatted a certain way.  For example:
> 
> A2 =((Sheet1!B10&", "&Sheet1!B11&": "&Sheet1!B17&"; ")&"
> "&(Sheet1!C10&", "&Sheet1!C11&": "&Sheet1!C17&"; ")&"
> "&(Sheet1!D10&", "&Sheet1!D11&": "&Sheet1!D17&"; "))
> So on and so forth
> 
> Say, for instance, that D10, D11, and D17 were empty, meaning that the
> proceeding columns' corresponding cells are too.  I want to then stop
> at C and ignore the rest.  Otherwise, I'll have a bunch of hard
> returns and 0's in my Word Document that someone'll have to weed out.
> 
> Thanks!

I have not done Word/Merge in years but it seems to me this would be 
better addressed in a Word group.

Else, since it appears you are trying to amalgamate the data in Excel 
and pass a single field to Word, maybe you could get away with a 
modification of your formula to test for the possibility cell Dxx is 
blank. Something like

A2 =((Sheet1!B10&", "&Sheet1!B11&": "&Sheet1!B17&"; ")&"
"&(Sheet1!C10&", "&Sheet1!C11&": "&Sheet1!C17&"; ")&"
"&
if(
Sheet1!D10 * Sheet1!D11 * Sheet1!D17 = 0,"",
(Sheet1!D10&", "&Sheet1!D11&": "&Sheet1!D17&"; ")
)
)

But this feels very clunky to me, and not the right way to filter the 
merge data.
0
smartin108 (170)
9/10/2009 11:03:37 PM
Reply:

Similar Artilces:

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...

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...

Outbox shows empty, but still sending the message
I am having a problem with Outlook 2002. When looking at the folder listing, the outbox show 2 e-mails still to send. When you look at the folder, it states there are no e-mails to view and is empty. How do you fix this??? I also have verified that it is not a worm. It is an e- mail with a picture attachment. Any help is appreciated. ...

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...

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? ...

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...

empty combo box
I keep encountering the same problem where a combobox will be empty even with a valid input range. If i close the file and reopen then the combo will contain the correct list and function normally. The error is inconsistent and has occured in unrelated files. Possibly a memory problem triggered by making changes to 'large' workbooks? ...

Empy mailbox 'not empty'
I've got a users mailbox on Exch 2003 (Server 2003) that is showing approx 28Mb (20 items) in the System manager, which is over that particular users limit. However, when accessing the users mailbox there is nothing at all in there - no mail,contacts,drafts,deleted items,sent items etc etc. I've run Mailbox Management to clear deleted items but the mailbox remains the same size. Is there anyway I can 'explore' this mailbox or otherwise find out if there is anything there I can't easily see? did you check for "view" filters? did you execute an "ad...

Copying the column heading into all non-empty cells #2
Thanks very much Gord! However, when I Paste Special it doesn't seem to be working. I'll kee trying - but perhaps there's something else I ought to be doing too I'm pretty sure I've done it right so far - when I drag the formula ou to all the cells they the all say "FALSE", and then when I paste th original data over the top I just see the original data still. Any ideas where I might be going wrong?? Lizzy -- Lizzy ----------------------------------------------------------------------- LizzyD's Profile: http://www.excelforum.com/member.php?action=getinfo...

cannot empty the clipboard #5
Everytime I copy and paste, even saving, I get a message that pops up and says "Cannot empty the clipboard". It's becoming quite annoying with trying to work in excel. Does anyone know how to fix it? ~Thanks Hi see: http://support.microsoft.com/default.aspx?scid=kb;en-us;196620 XL97: "Cannot Empty the Clipboard" Error Message When Dragging Cells or http://support.microsoft.com/default.aspx?scid=kb;en-us;840872 You may not be able to paste an item in an Office 2003 program after you connect to a Windows Server 2003-based server that is running Terminal Server --...

Skip a bill
I have my credit card payments set up in "bills and deposits" and don't need to pay my credit card this month (did not use it). The bills and deposits module tells me that this bill is now past due. How can I let Money know that I don't need to pay this one? Thanks, Dave B After a little more searching I found that I can choose "skip" from within the bill. Thanks, Dave B. ...

Logon user name is empty
ASP.NET 4.0 I tried to get the Windows Logon user by: HttpContext.Current.User.Identity.Name.ToString(); But it return empty. What I want to do is to get the full name of the logon user from Active Directory. Alan T wrote: > ASP.NET 4.0 > > I tried to get the Windows Logon user by: > > HttpContext.Current.User.Identity.Name.ToString(); > > But it return empty. > > What I want to do is to get the full name of the logon user from Active > Directory. > Are you using authentication before allowing access to the applicatio...

reference row on another sheet skipping zeros but not skipping li.
I am trying to list info from another sheet but I do not want to refernce row that have a zero value in a certain cell. but the I also do not wasnt to skip lines. i am trying to make this spread sheet so the zero rows can change at a later date and automaticaly update the other tab. this is important becaust this info is referencing to mappoint. -- Thanks, Brennan ...

Sending empty mails......
Hi My entourage (office X) is sending empty mails, and calendar appointments, even if they on my machine is filled out. I have tried the database rebuild, and advanced rebuild. I have even repaired my disk permissions, and still have the same problem. Do any one of you have any suggestions of what to do? Thanks in advance Peter Wahlstr´┐Żm ...

error: cannot empty the Clipboard
I am copying a lot of information, bit by bit, to Print Shop Deluxe 21, but am now receiving the error: "cannot empty the Clipboard". I tried going to the Edit menu and deleting any items in the clipboard but there are none there. I am unable to copy at this time. Any suggestions???? jane ...

Extracting Data from a Database, but skipping rows
This is my first post here so hi everybody... The situation is this: I have tables in different files, the main one is used to creat projects and assign a person who will be in charge, among othe information, the other tables are used by some people to fill in th hours dedicated to each project assigned to them. For example (Main Table): Project A, Responsible John Project B, Responsible Bob Project C, Responsible Mary Project C, Responsible Bob Project D, Responsible Mark Project E, Responsible John What I need to do is to find some way to update the dependent tables s that when I create a ...

Posting Setup File Empty error
Hello - I'm trying to setup PA in one of our companies and I keep on getting this error when I go to Misc Log Entry : The Posting Setup File is Empty. Has anyone seen this before? Thanks in advance. Marisol ...

Numeric empty fields (System Configuration)
Hello, When installed CRM we got number configuration different from the one that we use in our systems. So, we tried changing that configuration but when we did it CRM just undo the change and got back to the wrong configuration. Looking at support.microsoft.com I found this article: http://support.microsoft.com/default.aspx/kb/921547/en-us. We applied the solution described there and we changed the number configuration successfully. However, after that change, we are unable to enter values in numeric fields on any crm form. When we enter any numeric value CRM just clean the field in...

Why do I keep getting: "cannot empty the clipboard" Message? in Ex
When I try to drag a cell with data in it to a new blank cell then I keep getting a pop up message of: "Cannot empty the Clipboard" I then must click on the "OK" button on the popup before I am allowed to do anything else. HOW IRRITATING!! I am using Excel 2007 PLEASE - Why am I getting this, and how do I fix the problem so I don't get it anymore!? Also, I am logged in here using my hotmail account because that is my Microsoft system ID, but I NEVER check my hotmail address anymore, it's nothing but full of spamm... so please use the below address for...

Paste Special Skip Blanks not skipping blanks, but overwriting...
Using Excel 2003, I'm having problems getting the "skip blanks" feature to work. It works on one computer using 2003, but not on another. I updated, but still have the problem. ...

Skip over a (calculated) cell in a Table
I have an (Excel 2003) list with some calculated columns in it. Users add data to the botttom of the table as usual. When user tabs right from a cell in which she has entered data, I'd like the cursor to skip over any calculated columns, so that the user doesn't corrupt the formulas. However, if I protect the sheet appropriately, users can't add new records to the bottom of the list. Is there a way to skip the calculated cells in an Excel 2003 list? I don't want to have to create a data entry form. Thanks! Why not just lock the cell? "Jeremy" ...

How to skip &nbsp;?
I'm reading XHTML documents into an XmlDocument object via an XmlTextReader. The problem is that many of the documents use very large numbers of &nbsp; tags for formatting, and it appears that the XmlDocument parses each of these into its own node - which bogs down the load time. If I remove the &nbsp; tags then the document loads almost instantaneously - but obviously I want to preserve the formatting of the original document. Is there a way to get the XmlDocument object to treat &nbsp; as just text? Thanks. joemann wrote: > I'm reading XHTML documents into an Xml...

Copy range if column N is empty
Hi all I have looked through the forums but can't find what i need. In each row that column N (Sheet name is Sales) is empty I want to copy columns A:T to another worksheet (Sheet Name is Carryovers) starting at A2 Hope this is clear enough many thanks in advance Alan ajm1949 This might work Sub terrain() Dim sh1 As Worksheet, sh2 As Worksheet Dim lr1 As Long, lr2 As Long, rng As Range Set sh1 = Sheets("Sales") Set sh2 = Sheets("Carryovers") lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh1.Range("N1:N"...

Skipping Saturdays and Sunday
I have a time-series chart but would like to skip Saturday and Sunday. I have the scale setup so that the major labels repeat every seven days. This allows me to show a Major gridline to separate the weeks and minor dashed gridlines to separate the days. The x-labels only show Monday (the first day of the week) and leave the other days blank. This is great except for the extra room used for the dataless Sat/Sun. In order to skip Saturday/Sunday (since I have no data for those days), I've changed the Category (X) Axis to to Category instead of Auto or Time-Scale. This does, in fact, skip...

I can onlt delete one message at a time- will not empty my delete
will not empty my delete folder See if these offer you help. How to Remove a Stuck Undeletable Message in Windows Mail http://www.vistax64.com/tutorials/62560-windows-mail-problems.html WMUtil http://www.oehelp.com/WMUtil/Default.aspx -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "Sally" <Sally@discussions.microsoft.com> wrote in message news:E35401E3-94CC-497B-B7B1-9B496A9B408F@microsoft.com... > will not empty my delete folder ...