Posts by Kyle Johnson2

rapidhawk23 said:Need some help with school stats. Anyone got me?

Drop the file somewhere. get it. Change the info—use fake names for anyone else who could be identified.
Dump the raw data into one table, then set up a second one showing how the final result should actually look. Throw in some comments too—it helps.

Someone else might actually be able to help you out with that.

slyviper6 said:slyviper6 | 4 | 11 | 9 | 7 | 13= 33
Man, I could just manually color them red 🙂

So where's the link to download the file?

Code:

slyviper6 said:Got a question... I need to run some numbers like this -> click

So you’re just gonna sit there crocheting and snacking on popcorn while someone else does all the heavy lifting, manually typing in your data from the examples? Real classy. 😉 ?
You should've just dropped a sample file for download. Saves everyone from having to type all that crap in manually.

slyviper6 said:Can we just have it ditch the two worst results and maybe "redline" them if possible? (x = 0 points, btw).

What do you mean by "toss out"? You probably meant "mark," right?
If that's how it is... Conditional Formatting Formatting. Set up a rule for every row that pulls the two lowest values.
Look, you can't exactly add text and numbers together—unless you want to end up mixing apples and oranges, which is just a mess. But maybe you could hack it with a long-winded formula or some macro? You could set it up to check if a cell is text or a number first, then use those conditions to hunt down the two smallest numbers. I'm not talking about just adding up cells here; I mean doing the whole thing in one single move.
If the app lets you do it without messing up the core logic, just type "0" instead of an "x". It’ll make setting up your Conditional Formatting way easier. Then just throw a standard SUM formula at the end. SUM.

mistybison19 said:Hey guys, anyone know how to set up American Autocorrect in Word?!

- Autocorrect in Word 2003
- Autocorrect in Word 2007

Timothy Lee29 said:How do I fix this? You think the bottom margin is acting up because of that?

It's not the bottom margin, it's just how the PageNumber is measuring out.

Nicholas King already covered all that (same as the deal with Widow / Orphan Control basically regarding the number of lines in Word), so there's nothing left for me to say except here's the file back. Try printing it and see if that last line sits about 1.2 inches from the bottom edge of the paper. Just keep in mind it all depends on the text type or style (if what Nicholas King told you is right, there's zero room for another line).

Timothy Lee29 said:Fine, I'll just print it like this and deal with the fallout. I doubt they're gonna be checking every single millimeter anyway.

If they actually start nitpicking, then your 1.25 PageNumber setting is totally off.
You've got an extra line in there somewhere.

Jamie Barrett7 said:How do I set a new indent after a heading without just spamming the Enter key? Using Microsoft Office 2010.

That’s what styles are for, like Nicholas King said, or... Tabulators.

Benjamin Wilson7 said:The rule is simple: the values in the first column of the table array must be in ascending order; otherwise, you're gonna have problems.

Nicholas King said:Nah.

You're both right, honestly—it just depends on which side of the fence you're sitting on. VLOOKUP. That's it. That's the whole post. The function has its own syntax.
Code:

The last "range_lookup" argument gives you two choices: TRUE or FALSE.
If you set that thing to TRUE, the values in the first column of the table array must be in ascending order; otherwise, it's gonna break.
If you set it to FALSE, you don't even need to worry about sorting the data.

driftingpilot8 said:I need to fix my rows—basically, I need to align two columns on the right with their matching rows on the left.

You should've just uploaded the file for download so I wouldn't be wasting my damn time. 😉

Alright, here’s a quick pic to make things easier to wrap your head around.



Just pop this formula into cell G1: =A1. Done. Copy. See below.
Just pop this formula into cell H1: =B1, then drag it down. Easy.
Populate cell I1 with this formula: =IFERROR(VLOOKUP(G1;$C$1:$D$5;1;FALSE);"")
Stick this formula in cell J1: =IFERROR(VLOOKUP(G1;$C$1:$D$5;2;FALSE);"")

You gotta use the "table_array" argument. No exceptions. Absolute addresses. or Named ranges..
By the way: Take a look. Excel 2003 basics. or Some examples for Excel 2007.

Timothy Lee29 said:but I've seriously never seen memory usage spike like that just by adding pages. Did I mess something up, or is this actually normal? How do I check if I broke something?

Look, if the text looks right and the doc is finished, who cares how many MBs it's pulling?

Benjamin Wilson7 said:I didn't see this coming since I didn't even think it was possible. Now you've gone and left me hanging.😢

That's just how it goes—nothing's ever 100% secure.
Once I get a breather, I'll try to update my tutorial if I can track down a fix.

BTW: in the meantime, Google some macros that block the SHIFT key. They can expose hidden sheets right after you enter a password in a workbook. Not sure if the guy I'm looking at uses anything like that, but I remember there's stuff for ALT+F11 and ALT+F8.

Later

Benjamin Wilson7 said:is exactly what I was hunting for. Dropped it into the VBA under VBE ThisWorkbook and it's running like a dream. Saved it as an xlsm.

Funny little question though:
Since I'm the one who built the thing, if I hardcode something like: deadline = DateSerial(2010, 5, 30) (an old date), how can I actually get back into the program to change that expiration date? Unless I just keep a master template

It won't work quite the way you're picturing it.
You clearly overlooked the SHIFT key.
If a user opens the Workbook while holding down SHIFT (which is super easy to do in your setup since they don't need admin rights), or if they just disable macros entirely, your whole plan goes out the window. You'd basically need to preventing access to Workbook by blocking that SHIFT key. Google a Macro that handles that, or check out the link and try whatever's there.

You also need to find a way to keep all the Sheets hidden by default, only unhiding them once the macro kicks in.

I'll dig into this a bit more and maybe add it to the tutorial later.

Benjamin Wilson7 said:I want to set up some VBA-Ccod, calling it "time code"... Any ideas? Appreciate the help.

So, since my last post was basically just me winging it and copying unverified Macro snippets, I actually put together a real tutorial based on what you asked. Check it out.

- how to restrict access to a Workbook or Worksheet after a specific date

All you gotta do is tweak it to fit your specific situation (if you're feeling this approach) and then Google the actual Macro commands you need.

Benjamin Wilson7 said:I want to drop some VBA code, let's call it "time code", for a specific Excel file used by different people.
The file itself has an opening password, say "AB," which stays the same for everyone all year long.
The file has about 30 Sheets, and those are also locked with two different passwords (some with "CD," others with "EF," for example), but the data entry fields within those sheets are wide open.

Hard to say much without seeing a dummy version with fake data—that'd make it easier to see what you've already built. Just throwing out ideas here since my Excel 2007 is being weird and won't run Workbook_Open or Auto_Open events (no clue why)

But before I dive into the idea, you gotta consider how tech-savvy your users actually are. You really need to lock down the VBE access so nobody just snoops on your passwords; even then, anyone with enough time and brainpower can crack it.

Based on what you described, the plan is: Use certain VBA macros and VBA events

1. Set a global password for the Excel file via File => SaveAs (Tools). This is the one that pops up when you first launch the
Workbook. 2. You have 30 Sheets. Some Sheets should only be editable if you hit them with a specific password, right?
3. After a certain date, access to a specific Sheet or the whole Workbook gets cut off entirely.

There are plenty of ways to handle protecting the Workbook and Worksheets via VBA.
Here’s one way you could play it:

1. Set that global password like I mentioned, assuming there's no expiration date on the main file level.
2. Once they punch in the global password, land them right on Sheet1 so they can read instructions or whatever. To pull this off, you'll need to put something like this macro in the VBE ThisWorkbook module so it defaults to Sheet1 every time it opens.
Code:

3. Put VBA code on all sheets that triggers a password prompt whenever someone clicks a sheet tab. Basically, when a user clicks a sheet name at the bottom of the Excel window, a dialog box pops up asking for the password before they can touch any data on that Sheet. Something like this macro:
Code:

Next, inside that same Sheet's VBE right under that first macro, you’d drop in a second one to check the date. Basically, it limits access to that specific Sheet once a certain date passes. I haven't personally tested this exact combo, but check online—there's a ton of similar macros out there. Here’s what that macro looks like (this part applies to Sheet2)
Code:

So, this macro checks the date; if it's passed, it wipes the old password used to get in ("abc") and swaps in a new one ("33") that only you know about (keep your own 😉
records). You can set this up on all Sheets, just be careful managing which password goes where. And obviously, you'll need to trigger all those Sheet macros within the Workbook_Open event

Hopefully, that gives you a decent starting point and some direction based on what you asked.

If you want to take the global approach, it’s actually way less work. You just do this:
1. Use VBA for preventing access to Workbook
2. Set up a Macro that checks the date
But leave the Sheets themselves accessible, or maybe just set passwords for specific user groups. Also, don't forget to look into File Sharing

Now, a quick question for you.
What happens if a user just rolls back the system date on their computer?
Think about this: once that expiration message pops up for the first user, you could have a Macro automatically lock down the entire file so it won't even open.

One more thing.
What if the user just disables Macros in Excel and opens the file without any running code (like by holding down the Shift key)?
You gotta think through every loophole. Though, honestly, against a real hacker? It's probably useless. 😉

Daniel Thompson9 said:How do I turn 1.55*10^-5 into 15.5*10^-6?

Just pop -5 or -6 into cell C1.
Code:


Obviously, if you need to, just divide by 100 or whatever gets you the specific result you're looking for.
Code:

Elizabeth Brown90 said:the laptop isn't messing around

Forum Moderator??? 😢 😢 😕

driftingpilot8 said:in Excel... basically just raw numbers, you know, data

try Copy = > Paste Special = > Unicode Text or Text
otherwise just kill whatever extra junk is hanging around.

urbanraven0 said:"What do you mean I can't help? You literally just fixed everything! Just used CONCATENATE 🙂 and boom, done. Thanks a ton."

Alright, if that was the only issue.
Just ignore my columns, man. Stick to yours on the Result Sheet—it’s basically just duplicating data anyway, so it's all the same thing.

urbanraven0 said:Anyone got some ideas or a hand with this?

Can't really help you out. I don't have the bandwidth to dive deep and try to figure out "what the poet meant."

I reckon you should just go ahead and add two new Sheets—label them "wins" and "losses."
Pull the data for the clubs, the home team, and the away team from those, then just feed that info through... Let's talk VLOOKUP. Maybe on the Statistics Sheet. Or honestly, just use whatever function works—it all really comes down to how you decide to organize and structure your data.

Just add three helper columns to the Results sheet—home wins, away wins, and draws—then pull that data based on whatever club is listed in column A or B.
Look, I think you messed up by jamming the whole result into one cell. If you absolutely have to do it that way, fine—but at least add three more cells to break it down. You need to split that result up based on whether it's greater than or less than... IF functions. You can easily pull the winning team, or just break that result down into specific parameters to use for your next calculation. If you end up merging them back together later, just use a function. concatenate

Here's that file back. Take a quick look at what I added to the Results Sheet.

Samuel Baker3 said:Can someone tell me if there's a way in Word to make my text print upside down—like, rotated 180 degrees?

There are actually a few ways you can pull off printing text backwards or upside down.

- Throw your text into a table cell and rotate it 180
- Put the text in a TextBox and just rotate it (Flip)

If that doesn't click, check out these links

urbanraven0 said:but I honestly can't figure out how to track down the biggest or smallest wins for any given club. Anyone got a lead or some help?

Just upload the file somewhere so we aren't all just guessing here.🙂