Pinned Posts
Forum Widgets
Latest Discussions
An unhelpful Error Message
This is for the Microsoft folks who monitor this forum. I was attempting to respond to a question a few minutes ago and got the error message that appears at the bottom of this image. Note: I had not added any HTML myself. I had copied and pasted the items in the bulleted list, so it's possible that something carried over from Google...but in either case, that message -- how should I say it? -- contains invalid tech-speak. I was able to post the reply by eliminating the bullets, but those bullets had been added by, you guessed it, the forum's own software. Does the TechCommunity's left hand know what the TechCommunity's right hand is doing?
Conditional Formatting multi rule help!
Hi all, I've asked a very similar question to this before, but the helpful answers I got previously now don't seem to work for my spreadsheet. Please could someone talk me through how do this: Below is a spreadsheet we use to tell us when a patients prescription has come into the clinic. We manually put this data in. I need a set of rules as follows if possible: If there is a date in the 'Date of Injection appointment' cell (in this case F13) and there isn't anything in the 'Prescription received in clinic' cell (in this case O13) then I would need the patient name cell (in this case D13) to turn a different colour depending how close the date of injection is. Does that make any sense?! So if the date of injection (F13) is less than 4 days from today and cell O13 is empty it needs to be filled red, if cell F13 is less than 6 days from today filled yellow and if cell F13 is more than 8 days from today then filled white. I'd really appreciate someone's help please. I'm losing my mind! Thank you in advanceRack of Lambda
There’s been a lot of content shared recently to commemorate the upcoming 40th anniversary of Microsoft Excel. Personally, I’ve only been using Excel for around half that time, but October also marks my 2-year anniversary since "joining the conversation" on this forum. As a gift from me to you (anyone interested), I’ve spent some time over the past few weeks revisiting old posts, updating methods I shared previously and packaging them into a collection of generalized Lambda functions to assist with a variety of common array manipulation and transformation scenarios. The attached file contains some 35+ Lambda functions, ranging from very simple concepts to much more advanced techniques. You can also import them directly from my gist, if desired. While they were all compiled and composed of my own accord, I would be remiss if I failed to credit the community and its members as a major resource in my own development. The amount of knowledge, tips and tricks gained through community collaboration is simply invaluable. You may notice some recurring themes in the way I’ve written many of the functions. For example, I like to keep the optional arguments as simple as possible, using either Boolean values passed to IF, or numeric options from 0 to 3 passed to CHOOSE. Also, many of the array transformation functions use TOCOL-IF-SEQUENCE in one way or another, with MOD-QUOTIENT-SEQUENCE used only a few times in the more complex algorithms (e.g. HWRAP and VWRAP). The collection also includes a few examples of Lambda recursion, the most notable being PF (Prime Factorization). CROSSJOINM was written as a "how-to" demonstration for filtering multiple optional arguments using LAMBDA and NOT-ISOMITTED. There’re also some powerful scanning functions like SCAN3, which can handle multiple input arrays, as well as EVALS with VALS2 to VALS7, which can store and recall multiple variables at each iteration (useful for corkscrew calculations). What you won’t find, however, are methods that use INDEX in an iterative manner with functions like MAKEARRAY, SCAN, etc. as these are only efficient when iterating over a range reference (they will bog down considerably and become practically unusable after just a few thousand iterations when looping over an array object). As such, I don’t recommend them as "generalized" solutions, although they can be very effective on a case-by-case basis. Similarly, you will only find 2 examples in this collection that use REDUCE-STACK in a limited capacity, with fewer than 10 iterations, as I also consider this to be a method of last resort due to its problems with efficiency when the number of iterations cannot be controlled. Hopefully one or two of them proves useful. If not, no big deal. Many of the examples in the attached file are interactive, so you can see how the different options affect the output. For those brave enough, please feel free to share your own custom functions too. I’d love to see what you got. Cheers!
Add secure additional workbook and worksheet protection Use MS account
Instead make the workbook protection including locking cells and editing printing all the features associated with locking a workbook and worksheet away from a password. Instead have it linked to the Microsoft User Account and therefore the workbook or worksheet can only be edited by the creator when they are logged into their account and open the workbook. The current password way is not secure as we all know. If the formulas are encrypted (tied to our Microsoft account), then they never get decrypted on the end user’s machine, so there’s nothing for a hacker to inspect and steal. We would also need to lock or disable macros for untrusted users, VBa would also be hidden from those who are not logged into the creators MS account. This would greatly enhance the security of how office files are shared. The option to use the existing password system could still be selected as an option for those users who prefer it. But add this method of protection in there for those who really need it secure. No additional software would need to be installed or anything. No Azure rights management or anything. No Purview. These features simply dont let a user open the document unless they have rights. We want to share excel files with other users but restrict what they can do with them. Why using Microsoft Account would be more secure Password hashes wouldn’t live inside the file anymore (as they do with current sheet/workbook protection, which is why VBA can brute-force them quickly). Encryption keys could be tied to the user’s identity (e.g., your Microsoft account generates or retrieves a decryption key from Microsoft’s key service). Without that authenticated session, the file cannot be decrypted. Cloud-backed key management would allow revocation — if your account is disabled or you revoke access, the workbook becomes unreadable. Conditional access could apply (require MFA, only allow corporate devices, block risky sign-ins). All creator data is tied to MS account and therefore undetectable and locked to any who are not the creator of the locked document.
Two lists of transactions. Want to create one large 'combined' one.
I have a workbook which I use for tracking transactions across two bank accounts. Each account has a separate 'ledger' sheet which lists all the incomings and outgoings. I want to create a third sheet which shows the entries of both accounts in one combined ledger. I would like it to be sorted by date and to have one column to indicate which account the entry is coming from. This is a link to a a dummy workbook with three sheets: Combined, Account 1 and Account 2 so you can see what I mean. https://1drv.ms/x/c/eea13e24843cdffd/EdNz2TLk0hJEueH-abYJF6ABxp69AS5eU0NqCOG4vlZAXQ?e=iYZ2Do If this is possible, please could someone with better skills than me please advise! Thanks!
