Replies

Oy vey! There’s lots of cells with various other pre-existing formatting choices that need the colors, so I guess that other formatting will just have to be overwritten and then reinstated after the colors are applied. (That or create a huge number of styles!) Guess that’ll just be part of the pain of making the transition.
Thanks for the quick answer!

Thanks very much, Hans. This works perfectly, and the test I tried that crashed it yesterday now works as well. Perhaps I was just in need of a reboot at the time. Would no doubt have wasted a lot of time without your help.
Best,

Thanks so much – this is perfect.

It’s the prevalence of worksheets where Page Setup>Page>Fit to is set to 1 x “” that is causing the problem – I wanted to see if there was an easy way to access the total number of pages that such a sheet will print on.
Basically just looking to see if there’s an accessible property or simple workaround that gives one the number of pages that a sheet will print on. I guess, actually, it could probably be calculated from the h and vpagebreaks.
Thanks again

Thanks Steve. Hope I’m not being dense here – I tried to do the closest thing I could figure to what you suggested, which is to set up a do loop that prints each successive page 1,2,3, etc from a worksheet until that results in an error. But unfortunately it seems that asking Excel to print from page 24 to 24 on a worksheet that only has 12 pages to be printed doesn’t generate an error. Is there some other way of accessing the name of that last printed sheet?
Thanks.

Thanks Don! That’s the sort of thing that never occurs to me. I’m not sure if I will use it here, because the workbooks in question are so huge and taxing on Excel already, and so many sheets may be involved, but it is actually very helpful with another project I’m working on.
Best,

Thanks, Steve – that makes sense.

FWIW, where I work we’ve encountered files with 30,000+ names; so long as they weren’t corrupt names (#REF or #N/A errors within the definitions, or linked to other workbooks), they didn’t seem to cause problems. And the bulk added wasn’t too bad (again with corrupt ones it can be a different story). Not that there was any good reason for the files to have that many – they had been assembled from many other books, and the users have add-ins that add a huge amount of names to each file.

So if you’re going to delete a worksheet that contains a bunch of names, you might want to use Jan Karel Pieterse’s Name Manager to get rid of the #REF error names after you do it. But if you stick to the method of Sheet Level names via INDIRECT that Steve suggested, that shouldn’t be a problem either.

Best,

Interesting. It is a little circular – cells refer to themselves as parts of ranges in counts, etc.; perhaps I should have mentioned that — I work at a place where everyone works with iteration on so I forget about the other possibility sometimes. The attachment does work for me as soon as iteration is turned on, though.
Best,

To jump back to the original problem (no text), I think you can do it with non-array formulas making use of SMALL and COUNT and some expanding ranges. Not as elegant as the array or as clear and logical as Steve’s formulas, but I figured I’d add it anyway.
Best,

Thanks very much for all the replies.
Microsoft Premier Support investigated and have confirmed that it is a bug. Which is obvious, of course, but they’ve never gotten back to us so fast on anything before. (Sounds like there are no plans to fix it though.)
What amazes me is that, while most people avoid data tables, they are very very common in financial modeling, yet this seems never to have been noted before.
Best,

Well, this won’t clear anything up but thought I’d mention it.

I work at a bank. Almost all our bankers use a set of circular references for interest calculations which have many cells that are involved in multiple circles.

When implemented properly, in files without too much corruption, they work consistently. But then, every once in a while, we’ll get a file where they aren’t stable. Most often it involves one cell directly linked to another cell (e.g., cell A1 has =B1 in it), where the two cells trade off values at each calculation, so A1 will = 1153 and B1 will = -94, then after the next calc, A1 will equal 94 and B1 will equal 1153.

With these files, it always turns out that the banker has deviated (usually inadvertantly) in some way from the standard set-up.

I guess certain types of circles with shared cells would have to be unstable. In the simplest form, a cell that was the center cell in a figure 8 would be problematic, because Excel would have to calc one of those two circles last. But unfortunately, I’ve never had the time to examine them closely and see which sorts of shared cells are ‘safe’. I’d be very interested to hear anyone else’s thoughts on this subject.

Best

Thanks Hans. Unfortunately they are user’s files, and stubborn users to boot, so they probably won’t break up their formulas. I’ll just use that workaround.
Best,

That’s hilarious. Clearly the next step is some combination of the two.

A Shakespearean insult generator. (The lists of adjectives and nouns are easy to find on the internet.)
Press F9 to get a new insult. (Uses RANDBETWEEN, so the analysis toolpak needs to be enabled.)

Best,