Highbrowse

Once a worksheet has been protected in Excel, to prevent formulas from being changed, for example, then a number of useful functionalities are no longer available. One of the most frustrating, for many people, is that spell checking is greyed out on the Excel ribbon.

In order to make spell checking available, the only work-around is to write a macro which will temporarily un-protect the sheet, run the spell check and then re-protect the sheet. The macro itself is simple, but where it starts to get complicated is when different files, perhaps in a corporate environment, have different passwords. In addition, users need the flexibility to spell check just a selection, an entire worksheet or indeed all worksheets in an Excel workbook.

The example Excel macro-enabled file (link at the bottom of this post) contains the code to create a custom menu-bar in the Add-ins section of the Excel ribbon. The menu will be loaded when the file is opened and removed when the file is closed. The code creates menu items to check a selection of the active sheet, an entire sheet and all sheets for two departments, Finance and Procurement. There are two example sheets in the file for Finance and Procurement, which have been locked with two different passwords “passf” for Finance and “passp”. Normally two different passwords would not be used in same file, this is done purely for the sake of the example.

Error handling provides users with a message if they try to run spell-checking on an Excel file using the wrong menu item (e.g. running a Finance spell check on a Procurement file or a Procurement spell check on a Finance file). The process could be streamlined if some consistent indication could be given of which department files originated from, e.g. using a named range in a hidden sheet set to Finance or Procurement.

Link to code example.

The previous two posts have charted my experiences and the techniques I have put together in the context of Excel data validation. The ultimate objective was to create a pick-list which would contain both a standard set of default values and any user entered values together in a single list. This Holy Grail of data validation had to be achieved, in addition, without the use of macros.

The ultimate solution relies on a complex combination of formulas on a hidden sheet. Here is the step-by-step process described in detail:

1. Create a new worksheet which will ultimately be hidden from the user, my example is called Drops.

2. Create a dynamic named range called DropSource on the column which will contain your data validation.

=OFFSET(‘Data’!$A$2,0,0,COUNTA(‘Data’!$A:$A)-1,1)

3. In A2 of Drops, create an array formula using Control-Shift-Enter with the following form:

=INDEX(DropSource,MATCH(0,IF(ISBLANK(DropSource),1,COUNTIF(A$1:$A1,DropSource)),0))

4. Drag to copy this formula down for the number of rows equivalent to the maximum number of unique values you expect to have in your pick-list.

5. Create a dynamic range on column A (in my example called Good) to pick up only values and exclude #N/A

=OFFSET(Drops!$A$2,0,0,COUNTA(Drops!$A:$A)-COUNTIF(Drops!$A:$A,”=#N/A”),1)

6. Create your list of default values somewhere, for example in column B of Drops and give the range a name, in my example this range is called Fixed.

7. In column C, row 2, enter the following as an array formula

=IF(ROW(A1)<=COUNTA(Fixed),INDEX(Fixed,ROW(A1)),IF(ROW(A1)<=(COUNTA(Fixed)+COUNTA(Good)),INDEX(Good,ROW(A1)-COUNTA(Fixed)),”0″))

then copy it down the same number of row as you did in column A.

8. Next create a dynamic named range on this column, (in my example called GoodFinal), which will concatenate unique values entered in the column with data validation plus the list of default values and remove zeros.

=OFFSET(Drops!$C$2,0,0,COUNTA(Drops!$C:$C)-COUNTIF(Drops!$C:$C,”=0″),1)

9. In column D, row 2 enter the following array formula

=INDEX(GoodFinal,MATCH(0,IF(ISBLANK(GoodFinal),1,COUNTIF(I$1:$I1,GoodFinal)),0))

and copy it down the required number of rows.

10. Finally, you are ready to create a dynamic named range on this column, which will furnish the final list of values for your drop-down on Data!$A

=OFFSET(Drop!$D$2,0,0,COUNTA(Drops!$D:$D)-COUNTIF(Drops!$D:$D,”=#N/A”),1)

One final constraint is that you will need to enter a first value in your first cell which uses your drop-down, which could be an instruction, such as Enter values here.

Is there any technique which can be used in Excel to combine a list of default entries for a data validation drop-down list plus the values which have been added by the user where the data validation list is not constrained?

There is one means of achieving this which used a pivot table to create the range of values for the drop-down. The only drawback is that the pivot table needs to be refreshed before new values entered show up in the dropdown. This can be achieved most easily by including the Refresh all button in the Quick Launch menu bar.

In order to get bot the static and dynamic values to show up, we need to use a pivot table which consolidates two separate ranges. The first range will be a dynamic named range which looks up the entries that the user has entered in the column including any from the fixed list. Create a dynamic named range using the OFFSET formula as follows:

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B),2)

Note that the range must include two columns, but it is irrelevant what is in the adjacent column as it will not be used.

Next, create the list of fixed values, perhaps on another worksheet which can then be hidden from the user.

Make sure that you title the first column with the same name as used by the drop-down, so that the pivot table can consolidate both ranges.

To create the pivot table, use Alt D, P to launch the Pivot Table wizard and choose Multiple Consolidate ranges:

Add two ranges, the first will be the dynamic range created initially, the second must be a range covering the title and values in the fixed column, plus the column adjacent to the right, no matter what is in it.

The resulting pivot table will contain single entries from both ranges.

Create a dynamic range based on the pivot table, to pick up just the required values form the list:

=OFFSET(Sheet2!$H$5,0,0,COUNTA(Sheet2!$H:$H)-4,1)

Set the data validation on your column to this named range and it will combine entries from the fixed list plus any entries added by the user, once the pivot table has been refreshed.

Excel data validation is a good tool to employ to direct users to a pre-defined set of choices for a particular cell. You can also allow users to enter their own values. But what if you want to add a set of user-defined values on top of a set of default values, without any scope of entering other values apart from what is in the two lists?

This can be done with a few simple formulas in Excel.

First you need to create a place for users to enter their additional pick-list values. Let’s put these in Column A, from A2 until A11, (we will use the first row for our titles). In column B, we will use a formula to generate a numbered entry, which will follow-on incrementally from our fixed pick-list, in Column E.

We do not need to expect an unlimited number of User-Defined Values, and in this example we will limit the list to 10 entries. It is up to you to decide what is reasonable in your particular case. You may want to use sheet protection to limit editing outside certain areas of the sheet.

The formula used in Column B to generate the entry is:

=IF($A2=””,””,CONCATENATE(ROW()+4,”. “,$A2))

As you can see, the formula displays a generated value if the cell to its left is not empty. Entry 5 is then also added to the pick-list range in Column E.

To do this the cells below the first four fixed entries contain a formula:

=IF($B2=””,0,$B2)

which puts a zero in the cell if there no user-defined value present, with conditional formatting to make text white on white. I use a calculation to count how many items are in the pick-list, as follows:

=COUNTA(E2:E11)-COUNTIF(E2:E11,”=0″)

This formula counts values and subtracts entries which are set to 0 in Column E. This is necessary because otherwise Excel will consider that formulas in Column E are actually entries.

Finally, there is the formula for the Named Range which defines the choices in the pick-list for data validation:

=OFFSET(Sheet1!$E$2,0,0,Sheet1!$I2,1)

Offset will go as far as the last cell in Column E which has an entry.