Replies

I can see two choices. If you sort on the file as it sits now the blank cells in column A (A2:A3, A5:A6, etc) will all group together in the sort – and the data in the coresponding cells in column B will no longer be associated with the correct name in column A. The technical term is unprintable in a family forum, but it will not be fun! (been there, incompetent clerical help has done that!).

You can either associate each of those cells with the correct name by copying the name information to the appropriate cells in column A, or you could put all the information in one row by moving the second and third address lines to the same row as the name and first line of the address. I recommend the latter, but lets deal with both techniques

To do the first one, you can use a little VBA routine as follows:

Sub FillBlank()
Dim Cell As Range

For Each Cell In Selection
If Cell.Formula = "" Then Cell.Value = Cell.Offset(-1, 0).Value
Next Cell

End Sub

Select the relevant range in column A (ie – all the cells that have address values in column and run the macro – it steps through the selection and drops the value found in the cell immediately above into any blank cell. In my opinion this looks horrible, but at least you will be able to sort the file and get meanigful results.

A much better alternative is to put all the required information onto one line. Put the following formulas in your s/sheet without the quotation marks:

in C1:  "=b2"
in D1:  "=b3"

then select the range C1:D3 and copy it down the length of active cells in column B. Every fourth row in column C and D will have a formula placing the address information on a single row, with blanks in the intervening rows. Copy and then Paste Special | Values to transform the formulas into string values. Finally, if you sort the file on column A all the rows with blanks in column A will be sorted together. They are now redundant, and can be deleted. You will have names and addresses sortable by name (or by city, post code, etc if the address lines were normally laid out).

The advantage of this approach is that this format is much easier to use as a mailmerge source for Word – which is likely where you will want to use it in any event!

Sue – the only way I can think of to do this is a kludge: graph your data as an X-Y chart with two points per data -point, being the frequency and the lower and upper bounds of the histogram bin or class. Setting the chart to include a line between the points will create what look like the top of a column chart. An example may make this clearer:

Bin      frequency
range
0-10       3
10-20      5
20-50      6
50-100     3

you would graph the following data points as an XY chart:

(0,3)    (10,3) series 1
(10,5)   (20,5) series 2
(20,6)   (50,6) series 3
(50,3)  (100,3) series 4

with the “lines” turned on for each data series. The result will be four horizontal lines on your chart. Ugly, but it works.

Secondly (but more fundamentally) you said that you wanted the area of the rectangles to be proportional to the frequency… The height can be, but not the area.

I think your approach of appending (okay “prepending and appending,” for the pedants here!) is the right way to go.

The following code will do the trick:

Sub MakeRound()
Dim CellText As String
Dim Cll As Object

For Each Cll In Selection
    CellText = Cll.Formula
    If Left(CellText, 1) = "=" Then CellText = Right(CellText, Len(CellText) - 1)
    CellText = "=round(" & CellText & ",2)"
    Cll.Formula = CellText
Next Cll

End Sub

This will round functions like sum or NPV calculations as well, and can be applied to a whole range. The routine will not fail if applied to a blank cell, although the resulting formula of “=round(,2)” will return a zero value – that is an aesthetic defect if you have zero values set to visible. Applying the routine to a label results in a name error, since excel interpretes the existing text as a name that should return a numeric value.

Both of these defects can be avoided by being more careful in applying the routine. As you say, it is easy to keep it in personal.xls and attach it to a tool button if you need it often.

Geoff (and anyone else watching ):

It sounds to me like one of two things, and perhaps a little of both, were happening:

1) The array formulas were duplicating the same calculation and / or referencing the same source ranges in the calculation. This meant that Excel was running through the examinations of the same cells numerous times (worst case, 100 times, if you had 100 array formulas on the sheet). Even though array formulas evaluate much faster than VBA functions, a VBA sub that performed multiple evaluations on one pass and then dumped the result(s) into specified locations was still faster, since you avoided the 100 read/compare cycles of the array functions.

2) By limiting the sub to run when cells in a given range were changed (by testing whether the active cell was in a specified range following the on-entry or on-change event, perhaps) the number of times the VBA sub was called could be reduced.

I can see how either of these approaches could improve the sheet responsiveness by reducing the time spent recalculating after every entry. Were these the approaches you used, and did you have other calculation-saving tweeks in place?

Whenever I have seen it, it has just been called a “4-4-5,” or sometimes a “4-4-5 accounting cycle.”

I think the romantic name is due to the fact that it is an accounting convention (and accountants, including myself, are not the most -how shall I say?- poetic of souls), and that anyone who needs to know about it usually needs a little more explanation than the name!

You have a couple of choices:

You could create a new column that would reflect the combined result of the two columns you want to use, and then use that third column as the criteria range for the sumif. Assuming that you have a logical (true/false) value in column A and in column B, and you want the sum of all values in column C where A and B are equal to “true” you would insert a new column C with the formula (in C1)
= And(A1 = TRUE, B1 = TRUE).
This will return TRUE iff A and B are true. The values you wanted to sum are now in Column D, and your sumif would look like:

=SUMIF(C1:C15,TRUE,D1:D15)
assuming that the data range extends down 15 rows

This will work, but I find it a little kludgy – it could be improved by making the second argument a reference: in this way you could change the value in the reference cell from true to false in order to obtain the total of all cells where the values in A and B are not both true.

I think a better approach is to use an array formula – you can be much more flexible in your criteria, and you don’t have to insert (and possibly hide) un-needed columns in your spreadsheet.
Array formulas opearate on arguments with multiple values – like lists or ranges of cells. The array formula equivalent to the first approach above would be:

{=SUM(IF(($A$1:$A$15=TRUE)*($B$1:$B$15=TRUE) =1,$D$1:$D$15,0))}
note that the bracket before the “=” sign and after the rest of the expression is not typed, but is added by Excel to signify an array formula after it is entered with Ctrl-Alt-Shift

This formula takes advantage of the fact that logical ‘true’ takes on the arithmetic value of “1” and logical false takes on the value of “0” when used in a calculation.

The array formula takes the sum of a series of “IF” statements, where the IF statement returns either the value in cell Dn or zero. For each value in the range A1:A15 the logical condition (An = TRUE) will evaluate as 1 if An is TRUE, and as 0 otherwise, and likewise for the values in column B. The logical values tested by the IF function are thus TRUE when both An and Bn are TRUE, and FALSE otherwise. The IF returns the value in Dn when the logical condition is TRUE, and zero otherwise, so the total returned by the function is the sum of all cells in column D where the values in column A and B of that row are both true.

Because we are dealing with boolean values, it is possible to trim this function further, to:

{=SUM(IF($A$1:$A$15*$B$1:$B$15=1,$D$1:$D$15,0))}

since they will only take on the values of 1 or 0, and we know we want to test for them both being TRUE or 1. This ‘streamlined’ version does not generalize well, however.

As for the function above using a synthesized result column and a SUMIF function, this approach can be generalized by testing a reference rather than a constant value, as follows:

{=SUM(IF(($A$1:$A$15=A24)*($B$1:$B$15=B24) =1,$D$1:$D$15,0))}

where the values set in A24 and B24 determine whether a particular row is included or excluded in the total. Although this may not be required when the data is nicely arranged with boolean values to test, it can be very useful if the values are more nomal variables, where, for instance

{=SUM(IF(($A$33:$A$47>A51)*($B$33:$B$47=B51) =1,$D$33:$D$47,0))}

could be used to identify items greater than a certain size, and related to a particular subset of the original data. I find that array formulas can be used in this way to provide similar utility as pivot tables, but they are easier to set up, particularly for “minor” applications – I use an array formula to track my billable hours by client, for instance, where a pivot table would be overkill.

I have included a s/sheet (XL 2K) with example of all these calculations – array formulas are easier to see than to explain.

I have had this problem as well (Word 2K) – but it is usually traceable to whether I hold down the mouse button or the ALT key first – you might try experimenting there

For all those who were on the edge of their seats…

It was the literal ‘wdFormatDocument’ that was giving me trouble in the second-last (or so) line of code, but it was the WordApp.Visible = True suggestion that really helped sort things out.

On the file import Word insists on popping up a dialog box (titled “Open Worksheet” – there was query about this recently, since it doesn’t seem to do anything but act as a gatekeeper) to confirm the action – even though ApplicationDisplayAlerts is set to “0” (the numeric equivalent of “wdAlertsNone”).

The work-around is to activate Word just before it imports the file so that it has the focus. That makes the dialog visible to the user, they can click on it, and then word disappears and they are back in their excel “database” environment.

Thanks, Rory.

When I had the ‘wdFormatDocument’ constant without quotes I got an error as well – I think that I have to supply the numeric equivalent of the constant, not the literal, because I am using late binding of the word application. That will require a visit to Word’s ‘immediate’ pane – but might be my solution

I would prefer to generate a ‘word’ document, because the people who are going to have to prepare mail merges are not experienced excel users, and I would like to hide as much from them as I can. I didn’t know I could set WordApp.Visible, so if nothing else I have learned one thing today.

I’ll let you know how it goes this morning.

If you are trying to document the contents of a s/sheet, I would suggest changing to the ‘formula view’ (MS may have another name for this view):

Tools | Options | View tab | - checkbox under "Window Options"

and then print the s/sheet with row and column headings (and gridlines) turned on

File | Page Setup | Sheet tab | - two check boxes under "Print"

I question the usefulness of establishing copyright on a s/sheet, since you cannot copyright the underlying ideas, only the particular expressions of them. Reverse-engineering any s/sheet is trivial (more or less) for anyone who has seen the copyrighted sheet and is familar with the subject matter. In any event, that’s your call.

If you absolutely HAVE to have a “list” of cell contents, I would do something like the following pseudo code:

for each worksheet
   create a new sheet called concatenate(originalname," LIST")
   CellCount = counta(entire sheet)
   for Counter = 1 to CellCount
      choose each non-blank cell in original sheet in sequence
      on "xxx LIST" sheet Col A, Row Counter =  _
      relevant address of non-blank cell on "xxx"
      on "xxx LIST" sheet Col B, Row Counter =  _
      value or formula for non-blank cell on "xxx"
   Next Counter
Next worksheet

For cells in the orignal worksheet that have formulas in them you could concatenate the formula with a string character to force them to display and print as a formula, rather than a value. This will probably also make values clearer, since the display will show either the value or an equals sign and the value, preceeded by whatever string character you have concatenated to the value…

You will have to have a way of handling worksheets with > 65K non-blank cells (if there are any) since you will run out of rows: I would suggest moving over to columns D and E.

Here you go…

As I said, this was demo for a colleague (I don’t like to admit that I work in the power business when you look at what is in this s/sheet!) – but there is nothing proprietary in there.

The idea is that you can run (and manage) different scenarios on the ‘scenarios’ tab – I don’t like the MS “Scenario Manager” as I find it too inflexible.

As shown in this example, you can have multiple scenarios – labour costs, fuel costs and escalation, selling prices, etc – all of which can be varied independently. To create a new scenario you just enter the parameters of interest to the right of an existing scenario on that tab, then click the update scenarios button – the macro has been provided with the locations of each list of scenarios, each row of scenario names and the controls (drop down boxes) that reference each scenario list. Then when you go to the Summary tab the appropriate drop-down box will include the new scenario name as an alternative, and the appropriate values will be used on the calculation tab.

If you need a scenario to apply to other logical groupings (say financing costs) you have to modify the ‘mainline’ routine CallUName and have it call UpdateNames one more time, and provide those parameters to UpdateNames (and name the appropriate ranges and dropdown box on the s/sheets).

It sounds a lot harder than it is! Drop me a note if you would like help with it

Cheers,

Dean

Mike – I think the problem may be that the ThisWorkbook.Names.Add syntax expects to see a string, and you have supplied it with a mixed string and integer value.

I used the following code to do much the same thing:

LAddress = "=Scenarios!R" & CStr(ListRow) & "C1:R"
LAddress = LAddress & CStr(ListRow + NameCount - 1) & "C1"

ActiveWorkbook.Names.Add Name:=List.Name.Name, RefersToR1C1:=LAddress

where “scenarios” is the name of the tab where I am inserting the new name. It was easier to assemble the string value of the range to be supplied to the activeworkbook.names.add function in “LAddress” than to write all that stuff out into a single line (or continue it on several lines.

The weird (to my eye) name of “list.name.name” is grabbing the existing name of a defined range (supplied to the routine as a variable named “List” dimmed as an Object) and then applying it (or re-applying it) to the range defined by the ListRow. The idea is to re-set a range used as a source for a drop-down list when the user adds additional choices.

Drop me a note if you would like a copy of the file whe I have this set up – I did it as a demo, so there is no proprietary information in it…

I believe you will have to issue an unstruction to remove the sheet protection before sorting, even if the password is blank. I woudl try:

ActiveSheet.Unprotect password:=""

or just

ActiveSheet.Unprotect

I am not sure of the syntax; whenever I protect a sheet I use a pasword, so I have not had to use the ‘no password’ version

If you are trying to enter this as an array formula, you have a bracket misplaced: You have

{=sumif($A$1:$A$4,RIGHT($A$1:$A$4)="A",B1:B4)} 

While I just entered:

{=SUM(IF(RIGHT(A2:A5,1)="A",B2:B5,0))}

which works. Note the left bracket between “SUM” and “IF” – your syntax is invoking the “SUMIF” function.
I don’t think SUMIF has the facility to evaluate substrings, although I don’t know since I always use the array formula “sum(if(…” approach. If it is SUMIF you are looking for, I don’t believe that has to be entered as an array formula

Hi Mark

You asked if I was exporting the data from Excel before using it in Word – I am, just by copying the entire “database” in Excel, then pasting it into word. I didn’t know that you could use an excel file as the data source for a mail merge, though, which is what I started to do with the data anyway. I will probably do it that way from now on, since Excel is my ‘native tongue’ in Windows Apps