Replies

I too have seen this practice — primarily used in spreadsheets I’ve seen built by people in our finance or finance related areas. Is there some convention to adding the redundant (in my opinion) ‘+’s in front of a number or cell reference?

Why use ‘Paste Links’? Think the other way around and make straight links on your destination sheet to the source sheets with a simple formula like:
=IF(SourceCell=””,””,SourceCell) where you just click on the cell you want in the source worksheet to fill in the SourceCell entry. I avoid the Paste Links operation when working in Excel because it used to create ‘arrays’ when working with multi-cell ranges which were sometimes difficult to deal with when modifing the destination sheet.

Check the topic “Enter and edit data” in Excel Help. This topic also includes an expansion covering dates. Bottom line: You must enter date data with separators (which are chosen by your Regional settings in Windows). This is not like setting up a custom format that will stick things like hypens in the middle of a number so that you can type in phone numbers without the hyphens — you have to enter dates with the separators.

Apparently, there is some confusion between setting the date format (which controls how the date will actually be displayed in the cell) with how the actual date ‘data’ must be entered.

011801 entered in to a date formated cell is interpreted as a numeric date serial number which does indeed translate to April 22, 1932. The ‘011801’ must be entered as 01/18/01 for Excel to recognize it as a date. Try it this way instead.

One caution on using INDIRECT…

If the reference is to an external workbook, the workbook MUST be open or INDIRECT will return #REF.

I don’t run the MAC version, but copied below is the HELP entry from Excel 8.0 (Office97). While there are limits imposed by available memory, I’ve never seen any ‘byte limit’ to workbook size. I’ve seen posts over the years from people claiming to have workbooks into the 32Meg range.

Program workspace specifications

Feature Specification
Maximum number of custom toolbars in a workbook Limited by available memory
Maximum number of custom toolbar buttons Limited by available memory
Maximum number of open workbooks Limited by available memory and system resources
Maximum worksheet size 65,536 rows by 256 columns
Column width 0 (zero) to 255 characters
Row height 0 to 409 points
Maximum length of cell contents (text) 32,000 characters
Maximum length of formula contents 1,024 characters
Maximum number of sheets in a workbook Limited by available memory (default number of sheets is 3; the maximum number of sheets in a default workbook is 255)

Check out the Visible Property for a worksheet. Setting this to xlVeryHidden in a VBA statement prevents the user from unhiding it. It can only be unhidden through another VBA statement. All you have to do is figure out a good strategy of where to place those statements.

Geoff, it’s part of a package of Excel utilities that are for sale. I also have the Bill Manville file which I’d forgotten about. The link you’ve already included in your post is accurate and should be sufficient. I double checked the download and it looks to be still valid.

=SUM(PRODUCT(range,range2)) returns the product of ALL the numbers — not what I think he wants. I think he wants the Sum of the products of each pair which is quite different.

SUMPRODUCT expects arrays for its parameters, yet for some reason, will not recognize a multi-sheet argument. You can use that construction with the SUM function as long as you don’t make the SUM an array formula. For example, =SUM(AL:WY!C5) will work but =SUM(AL:WY!C5*AS:WY!B5) will not. (I typed this in as symbolic examples — they’re should probably be some ‘s in there somewhere..)

I even tried to define named ranges using both the multi-sheet selection approach and identifing the reference as each sheet reference individually separated by commas — Neither of which would work.

My thought would be to build a couple of rows or columns on your summary sheet and link each of the state sheets there. Then do your sumproduct/count calculation on those ranges.

I’ve seen that happen a few times — and even to HRH.

HELP, MUMMY! The signature switcher gremlin is alive.

Edited by gwhitfield on 01/01/15 23:15.

*** I’ve added tags to the web reference- but it’s put my sig in place of Gene’s ***

The only item that I’ve seen while surfing Excel links that fits this description is the one from MacroSystems which is shareware. See: http://www.add-ins.com/linkfind.htm%5B/url%5D

Select your cells than go to Format / Cells. Select the Number tab and choose Custom in the Category box (at bottom). Enter mmmm-yyyy in the Type input box. This should do exactly what you’ve specified and add that format to your custom list.

1. Make sure that the “Print Area” reference is not pointing back to the original workbook.

2. You have to do the Name / Define routine on each worksheet since it’s possible the name is defined at the worksheet level and will only show up when that sheet is active.

3. All the other advice you’ve been getting is valid — links can be in cell references, chart references, and name references, so you have to check them all. Using the Find command with a search argument that consists of a portion of the external reference name is the best way to do a quick check on the cells. Search for something like “.xls]” without the quotes. You need to do this on each worksheet since Find does not work reliably on multi-sheet selections depending on which Excel release you’re using.

This sounds like a natural for a Pivot Table. The Pivot Table wizard can be a bit intimidating at first, but between the example in Help and playing with it a bit, you should be able to build what you’ve described without too much difficulty.