Posts by Carol Rivera32

Jack Hayes said:Since you're using specific text strings for all the columns—like "N/A" for things that don't need testing, "undefined date" for things that still need to be taken, and then just the date once they pass—you can actually just write a formula that looks specifically for those fixed labels and ignores the actual dates. Here’s how you do it:
Code:

=((10-COUNTIF(B2:K2;"N/A"))-COUNTIF(B2:K2; "undefined date"))/(10-COUNTIF(B2:K2;"N/A"))

Personally, I’d swap out "undefined date" for something a bit shorter, maybe just an "x" or a dash. It keeps the spreadsheet looking much cleaner and less cluttered.
Basically, this formula takes the total number of tests required (which is 10), subtracts the ones that were failed, which gives you the number of passed tests, and then divides that by the total number of tests that actually had to be completed. Just make sure you set the column format to Percentage.

🙏🙏
👋
🎉
🙂

🙂 👏

🙂 🙂

Thanks so much, Jack Hayes and Kyle Johnson2!! Drinks are on me next time we're out!
Works like a charm!!

Kyle Johnson2, that's not an error, "N/A" just stands for "not applicable."
But thanks anyway!
I really hope you land a great job soon, you definitely deserve it! Fingers crossed for you! 👍

Hey everyone! Thanks so much for all the help lately, you guys are honestly lifesavers..
I’m reaching out because I’ve been wrestling with an Excel issue for weeks now, and I was hoping a kind soul might be able to take a look at it for me..
So, here is the situation:

I have 10 different subjects and about 200 students.
I’ve laid out all ten subjects in columns, with the students listed in rows.
The tricky part is that not every student has to pass all ten subjects; some only need to pass 5, others 4, some 8, some 7, and so on... Basically, each student has a specific number of required subjects out of my total of 10.
Whenever a student passes an exam, I enter the date they took it (using this format: 1-Jan-13) into the cell under that subject's column. Exams a student hasn't taken yet but still needs to complete are marked as "undefined date." Subjects that a student isn't required to take at all are marked as "N/A."
So, this table with 10 columns and 200 rows is filled with these different markers (dates, "undefined date," and "N/A"), and every student has a different set of mandatory subjects.
What I really need is a formula that can calculate the percentage of exams passed (relative to the total number of exams that specific student is actually required to pass—meaning I need to ignore any cells labeled "N/A")—calculated for each row per student.
Naturally, I want this percentage to update automatically every single time I input a new date..

For instance, if someone has passed three exams (three cells containing dates) and still has three more to go (three cells saying "undefined date"), that means they have 6 total exams to pass, making their pass rate 50%. Essentially, Excel needs to completely ignore those 4 cells in the row that are marked "N/A."
What is the simplest way to pull this off (ideally using just one formula)?
Does anyone know how?😕

I’ve uploaded a sample file so you can see exactly what I’m working with: http://www.sendspace.com/file/2o4wqm

It works perfectly, but you’ve actually landed in the wrong cell. Leave that date column (Column C) alone; just click over to Column D and type your formula right there..
If you want to clear out the data in Column C later on, just highlight everything in Column D, hit copy, and then use "Paste Special" right over the top of what's already there (make sure you don't move to a different spot when you go to paste).
When that little menu pops up, just look for "Values" and select that (paste values).
And that's really all there is to it! From here on out, Column D won't be cluttered with formulas—it'll just show the final results.
Once you're done, you can go ahead and wipe out Column C with those old dates.

Kyle Johnson2 said:The link works perfectly

Funny enough, when I tried clicking that link yesterday, I kept getting those annoying "expired" messages and random error pop-ups. It was such a headache! But today? It’s working like a charm—smooth as silk. 🙂
I’m definitely going to make use of this document. I'll set up a custom keyboard shortcut for it, and hopefully, it'll be just as useful at my office job too...
THANKS SO MUCH, KYLE JOHNSON2
🙂

Kyle Johnson2 said:I haven't really messed around with this much myself, but I might be able to point you in the right direction. Check out these links along with an example I attached to this message (just unzip it and run the *.dotm file) => Let me know if this sample actually works for you

Could you please upload that again? Thanks!
thx

Kyle Johnson2, you hit the nail on the head. The file is definitely a *.dotm, but here’s the kicker: whenever I try to open it on a different computer—whether I’m moving it via a thumb drive, sending it over email, or uploading it to something like SharePoint—the macros just vanish into thin air.
I honestly can't wrap my head around why this keeps happening. I haven't run into any issues with Excel yet when I move files with saved macros from one machine to another...

It seems like Word is the only one giving me grief.
Updating the field isn't working for me either (the option doesn't even show up when I right-click the original document, and hitting F9 does absolutely nothing), and I really want the user to be able to just use a keyboard shortcut to get the result.

Assigning a shortcut in Word just isn't the same process as it is in Excel. There isn't an "Options" button in that macro dialog box like you mentioned (you know, the Alt+F8 => select macro => Options route). It feels like so many things in Word just don't work the same way they do in Excel...

🙂
Anyway, thanks so much for all your help and for taking the time to look into this. 🙂

I don't mean to be a bore, but I am honestly at my wit's end trying to figure out what's going on here...
My colleagues' computers just won't cooperate when it comes to running macros. The system clearly flags the file as a macro-enabled document or template, but there isn't a single line of actual macro code in the entire thing. It's like chasing a ghost.
The Security settings are already set to Disable All macros with notifications.

So, let's say I actually manage to track down the fix (🙂). What I really want is to be able to trigger the activation using a quick keyboard shortcut, something like CTRL + SHIFT + S.
Is there any way to write a snippet in VBA that tells Excel: "Hey, use this specific shortcut to enable macros"? I noticed that you never see anything like that explicitly written in the code... or am I just missing something obvious? Or perhaps there's some other clever way to force the whole process through?
🙂

Here is the link: http://www.sendspace.com/file/xjly5f

The very last cell in the total cost column should really be summing up all the cells above it.
Unfortunately, I can't seem to upload the entire document, but I’m hoping this snippet provides enough context..

Walter Jackson48, I've upgraded to a newer version of Microsoft Office (I have both 2010 and 2013), but for the life of me, I can't find where the security settings are defined.
Do I need a new tab? I don't see that specific sequence in my menu (or even in Visual Basic): Tools, Macro, Security, then Median or Low.
Besides, shouldn't the person receiving the document be the one handling those settings rather than me? 🤷
I'll admit, I'm a bit out of my depth here. 🤷

Could someone please lend me a hand with getting a macro to work on a different computer?
I’ve put together a Word Template that includes a specific Macro command. It’s designed to trigger within a table to sum up all the numbers in the cells directly above the active one. Essentially, the Macro just automates the process: Insert -> Quick Parts -> Field -> Formula -> =SUM(ABOVE)

I usually use the keyboard shortcut Ctrl+Shift+S (or whatever letter I decide to assign)

Here’s the thing: the Macro works flawlessly on my own machine. However, as soon as I email it to my colleagues or move it via a USB drive to another PC, the Macro simply vanishes.
The file uses the DOTM extension. I have already tried saving it as both a macro-enabled document and a macro-enabled template, but the Macro doesn't seem to "stick" through the transfer.

I don't think there's a restriction on displaying macros, and as far as I can tell, my coworkers don't have any blocks on loading them—at least, their computers aren't throwing any error messages or warnings.

I am wondering if the issue might lie within the Macro Security settings; perhaps I tweaked something incorrectly. My current setting is set to Disable All macros with notifications...

The code itself is about as simple as it gets:

Sub Macro1()
' Macro1 Macro
'
Selection.InsertFormula formula:="=SUM(ABOVE)", NumberFormat:=""
End Sub

When I C/P this into a completely new module under the document name (in Visual Basic), it either won't run, or I'm just doing something fundamentally wrong...

Honestly, I have spent the entire day combing through every possible forum and corner of the internet, and I can hardly believe there isn't an answer for this. Either the solution is so incredibly obvious that no one thinks to ask, or I am missing something very basic.
I don't have a ton of experience with Macros—just a little bit in Excel—but this Word situation is really making my head spin...

If there is some kind soul out there who could help me or point me toward a helpful link, ANYTHING would be greatly appreciated! 👍