Replies

I do precisely this same function. A non-VBA approach would be:

In cell A1 enter =CELL(“filename”) (should display the workbook path/name)

Define a name, let’s say “Show_Links” with its reference as =LINKS(A1,1)

Select a vertical range of cells that is large enough to hold entries for the number of files you expect to be linked

Enter =TRANSPOSE(Show_Links)in that range — you should get back the filenames of all linked files or #N/A if no link exists.

I like to pretty this up a bit by putting the ‘Transpose return’ in a hidden column. I then format a column wide enough to hold long path/filenames and fill it with IF statements like: =IF (ISNA(A2),”No Link Exists”,A2). Sometimes, the return gets confused and fills the entire return range with the same name if only one file is linked. I add a check to the IF statement if the cell is not #N/A, to see if it was equal to the one above it and display the “No Link Exists” if it is.

Don’t forget that Charts and Objects (buttons and such) can also have external references which triggers the ‘links’ message.

Your link points to the whole HTML Help package. I couldn’t find any reference to the ‘…Image Editor’ as a separate entity.

I’ve had downloaded the package previously, but haven’t installed it as yet. I couldn’t find any reference in my download references to the ‘Editor’.

Is there a way to find just that piece or do you have to install the entire package to find it?

Sounds like one big chore… The good and bad news is that:
1. Since this is financial data, there’s a good probability that you have lots of external links. A standard approach is to have a YTD roll-up that links to individual monthly workbooks etc.
2. IF the interlinked workbooks reside in the same subdirectory, changing the path won’t affect them since Excel doesn’t save the ‘fully qualified’ path name in this instance. At least it recognizes that the linked workbook is in the same subdirectory as the linking workbook and will attempt to find the linked workbook therein. (You can copy a group of linked workbooks that are all interconnected to each other within the same subdirectory to other subdirectory and, when opened, they will still refer to each other)
3. IF, however, the interlinked workbooks reside in different subdirectories, the fully qualified path is saved and when you open the workbook in the new environment, they won’t be able to find the workbooks they’re linked to.

If you have a user from the financial area that’s familiar with how these workbooks might be interconnected, it might pay to get some insight on how they’re organized. Worse case is that the individual users will have to go through the Edit-Links-Change Source dialog as they begin using the workbooks in the new environment (unless you can find a pattern that will allow you to identify just the workbooks with links to other workbooks that are in subdirectories different than their own).

Again, from the VBE Help…

line label

Used to identify a single line of code. A line label can be any combination of characters that starts with a letter and ends with a colon (. Line labels are not case sensitive and must begin in the first column.

Taken From the Visual Basic Excel Reference — Office97 SR2

There are two references to ‘goto’ in the index: “Goto” and “GoTo”. The second one has a subreference called:
Control Flow Keyword Summary.

The GoTo keyword is shown in the first Action grouping called Branch. Going to the GoTo statement page you’ll find that the statement must reference a valid label or line number that must be within the procedure that contains the GoTo statement.

You can use a simple array formula for this task.

=SUM(1/Countif(user_range,user_range)) Enter the formula via Cntrl-Shift-Enter which will put ‘curly braces’ around the formula.

user_range is the range of cells in which your user’s intials are.

I’ve run into this several times — the ‘Refers to’ argument extends well past the box and I need to add more at the end. The only way I’ve gotten around this is to use the mouse to put the cursor somewhere in the visible part of the reference and then do a ‘highlight/drag’ to the right. The reference will scroll to the end. I then position the insert bar at the end (to remove the highlighting from the other parts of the reference) and enter a comma and either type the additional range or use the mouse to highlight it on the worksheet. It’s a bit of a nuisance but it works. (Excel97 SR2)

Me too — but I personally watched it pre-fill her name (at least an exact copy of what I had changed another comment to)in newly created cell comments. It didn’t seem to matter if she was running the mouse/keyboard or I was

Select your formula cells – Format/Cells/Protection and check the Locked option – Then to activate it, you have to protect the worksheet – Tools / Protection / Protect Sheet. Unfortunately, this brings with it many restrictions like not being able to apply formatting on the sheet etc.

It works ‘cross-sheet’. I have a large workbook that has a “Input Parameters” worksheet where I have the users enter certain choices in defined ranges. In several other worksheets, I’ve used Data Validation to limit choices and present a pull-down for cells that are based on these ‘Input Parameters’. The data validation list on all of the sheets refers back to the named range on the “Input Parameters” worksheet. The syntax for the entry in the Data Validation ‘List Box’ is “=Defined_Name” (without the quotes) where Defined_Name is the range name given to the list area. This is a workbook level name in my case. If it is a worksheet level name it would look like =Sheet_Name!Defined_Name. You may need quotes for names with spaces. The easiest thing to do is to choose the list box and then go to the worksheet where your defined range is and use Insert Name Paste to insure you get the proper format.

Define the list as a named range and then refer to the name in the Data Validation List box (I’m doing this in Excel8 – I would assume it would be in Excel9).

Do you have an example or template of how your data dictionary is constructed in Excel?

TIA…

You can delete names manually via Insert / Names / Define.
Step through the defined names list box and watch their references for #REFs or external references. Delete those that aren’t valid.
Since you could have duplicate names(exist at both the workbook and worksheet level), you may have to repeat this process on each worksheet just in case. If you only have a couple of dozen names to worry about, it’s faster this way then writing a VBA sub to accomplish it.

If the cell formatting was ‘Text’ when the numbers were entered, and you change it to ‘Number’, the data attributes remain text. You can either re-enter all the numeric data or do a Copy / Paste Special – Multiply on that range. Just enter the number “1” in an unused cell, Copy that cell, then do Paste Special – Multiply on your data range. This multiplies all the data by 1 and changes the data attribute to numeric if it’s a number.