Replies

This looks really awful but it works, avoids all the #VALUE and #NA stuff:

=ISNA(HLOOKUP(“overdue”,A1:D1,1,FALSE))=FALSE

Create an Excel template (*.XLT) with macros and a desktop shortcut pointing to it. User’s click the shortcut and a copy of the template opens in Excel. Off you go… Not exactly transparent, but I don’t understand what event “starts” your application.

If you write a VB app you can use Excel application objects without displaying them to the user.

You could also use a data filter to hide the blank rows prior to printing.

I have a big long price list that the users enter quantities on. Then they click a button that limits the visible rows to only those where Quantity>0. The button is actually a toggle that either hides or shows the “blank” rows. I don’t recommend using Autofilter — buggy results and funky little drop-down arrows in the column heads — I just coded my own filter in the macro and apply it or remove it.

Then what you see is what you get when you print.

This is basically the macro attached to the button:

If ActiveSheet.FilterMode Then

ActiveSheet.ShowAllData
ActiveSheet.Shapes(“ViewButton”).Select
Selection.Characters.Text = “Short List”
Range(“D7”).Select

Else

Range(“data”).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range(“Worksheet!Criteria”), Unique:=False
ActiveSheet.Shapes(“ViewButton”).Select
Selection.Characters.Text = “Long View”
Range(“D7”).Select

End If

=if(FeelBetter(“You”),FeelBetter(“Me”), ” “)

Maybe you could use “Mail Merge” in Word to merge the data from a named range in the Excel sheet to a “catalog” document in Word. Then formatting would be completely handled on the Word side.

This would, however, require the users to do the “merge” action to create the final version. They might not like that.

PLAN C: Get all that “not important” formatted text into the Excel workbook and skip Word altogether. You can make a spreadsheet look a lot like that .DOC with features like:

– merge cells
– word wrap
– headers/footers
– insert graphics
– print to fit within one page
– etc…

I’ve had to completely rework a number of Office applications because of bizarre, inexplicable errors that I just couldn’t find the cause of. Sometimes you find the conditions that cause the bug, but you can’t fix the bug, so you have to redesign your app to avoid those conditions. At some point I’d rather switch than fight. 🙂

I have had problems with font size CHANGES WITHIN CELLS in Excel causing incorrect print formatting.

Use nested SUM and IF functions if you have multiple criteria:
http://support.microsoft.com/default.aspx?…b;en-us;Q275165

Using an array function like this can check values against multiple criteria before summing…
{=SUM(IF(A1:A10>=1,IF(A1:A10<=10,A1:A10,0)))}

An array formula like this must be entered with Ctrl-Shift-Enter to get the the 'curly brackets' to appear. No curlies = no array calculation = no work. 🙂 Basically, the IF criteria are applied to each cell in the range and the SUM adds up all the values that pass the IFs.

RE: criteria for blank cells
This array formula does a count of cells with negative values or blanks:
{=SUM(IF(A1:A10<0,1,IF(ISBLANK(A1:A10),1,0)))}