Replies

This path helped me clarify the problem.

But the initial question still stands.

You can always run book.xltx even if it’s in the wrong folder.

But how do you run sheet.xltx if you can’t put it in that XLSTart folder?

Without that, only the initial sheet in book.xltx open in Excel, but each added worksheet reverts to the systems default worksheet default template.

I did that on another forum and I got snark because the text version of the table wasn’t very readable. That basic point is that there’s a lot of ways that two hard drives could differ, and I can’t figure out how that translates into files showing up in the wrong places, or disappearing (the real problem since both original file structures are now gone).

Not really sure myself.

I have students that bring me stuff from their jobs. We use them as examples in class. This is from some utility construction companies HR management spreadsheet.

Most of them I can fix off the top of my head, and honestly I thought this one would take me about 10 minutes.

In a lot of cases, these are legacy workbooks that have been worked on by many people through the years. The students are trying to make their own jobs easier by using what they learn in class to clean up past nonsense (figure out how to do it right rather than twice, so to speak). But they can’t always start from scratch, or modify the workbook too much, because other people will freak out.

And, a lot of times, after they figure out how this works, then I’ll tell them to replicate it with an array formula, or VBA. But most of them can’t start with that.

So a tough one like this is pretty awesome. We will spend some time on what you called the “index within index” insight. Of course, I am always finding new uses for =n(); I just can’t seem to remember enough that this function is in there. And I still haven’t figured out the significance of the ),) that RetiredGeek noticed. Maybe this afternoon I’ll get back to that.

FWIW: there’s a different commercial real estate company nearby owned by a family who are all spreadsheet nerds (all better than me). They design huge formulas (thousands of characters) and put them into individual cells that they keep locked down. They’ve had better luck when they hire new people in having them troubleshoot those big formulas rather than VBA or array formulas that are a tenth the size.

Yes. This works with the original full worksheet. Thanks for the help!

EOM.

Bingo. Going to test this on the full data set shortly.

You folks are going to want to “kill” me.

My initial example needs to be more complex (Rory and Zeddy’s stuff is definitely on track for what I need, but running that brought up other shortcomings in my thinking).

I have learned a ton from this thread already. But it’s also pointing out that my initial question is not as well posed as I thought it was.

I have attached a better example. Should we continue here, or start a new thread?

Rick Corbett: interesting that Quick Access is still “locked down”.

Rick’s suggestion is worth following up on (for anyone else following this thread). The reason is that it allows you to multi-select individual items to remove from Quick Access, rather than right-clicking and making the choice of the context menu.

MartinM: that’s actually what I have been doing. It’s a pain.

Good call. I use that same trick in statistics packages sometimes.

RetiredGeek: yes, the UDF seems to work fine with the actual worksheet.

Maudibe: yes, your array formula works too with the actual worksheet.

I’m a dork: I wrote the example from memory using columns, and the problem worksheet had rows … it took me a couple minutes to figure out that the array formula wouldn’t work because of user error … an easy fix once I spotted it. But this points to the problem with using an array formula with novices: there’s a big drop off in their ability to troubleshoot when going from (standard) formulas to array formulas.

Maudibe: yes, it is OK to use the 0 rather than the blank.

**************************************

Again, I personally am OK with an array formula.

But if I am doing this for a lower level class, I need to start them out with (standard) formulas in cells. Then I say “but there’s a better way” and introduce an array formula or a UDF.

There’s a more general problem here: how do we search/find over what we see rather than what we wrote? I actually run into this quite a lot with students and stakeholders that I’m helping out … but the situation in this example has some extra bells and whistles that make it tougher and stumped me.

So this one is still open if someone wants to try.

I am OK with the UDF (I will not be able to check it on the real thing until the morning).

But I am sharing the solution to this problem with folks who rely on formulas in their cells.

If anyone can do this with formulas and functions in a cell, let me know.

None of the above worked for me.

But here is a workaround.

I could always use some freeware calculator. FreeCalc from MoffSoft works just fine for my occasional uses. Your mileage may vary.

The next problem is how to remap my keyboard’s calculator key. This required a registry edit. The directions at this site were helpful. Just substitute the full path to your alternative calculator (in double quotes) for calc.exe in step f of Ratandeep’s “Most Helpful Reply”.

I’d be looking at updating the video driver / scaling.

I will talk to our IT people about taking care of that.

This is calculator on my 8.1 box, so I’d be looking at updating the video driver / scaling.

cheers, Paul
46167-Capture

Interesting. You have an expanded view in scientific view. There are 4 views, and I do get this expanded view, but only if I choose the statistics view.

But that’s kind of useless since it doesn’t have keys for basic arithmetic (like I said above, I’d use anything BUT a calculator for statistics).