Conditional formatting not working properly as one of the comparing cells contains a vlookup formula
Hi, I'm having problems with a simple conditional formatting as one of the comparing values is a vlookup formula and not a number. I did the comparison with another set of data containing just numbers and not values and it works perfect. Is there any way to let the conditional format know that we need to compare the values of column A with the values of column B which gets filled with a VLookup formula and then highlight the cells containing different values? Please see the pictures. In this case, the conditional format is working as Column Brand has been input manually typing the numbers and the red cells are those ones showing differences between Column Inventory and Column Brand. In this case, the conditional format is not working. Column Available inventory is filled as a result of a Vlookup formula. Column Inventory and Column Avail. Inv. are all red for some reason. I think this is because when the conditional formatting compares Columns Inventory with Avail, everything is different as Column Avail's cells contain a formula and not a value. My comparing formula for the conditional format is very easy =$B2<>$H2 (Fill with red all cells from rows B and H with values are different from each other). Does any one have an idea about this? Does any one have had the same problem? Does anyone knows how to solve this? Thank you. Odin.Set Default to Copy and Paste Visible Cells Only
Hello, Does anyone know if we can set the Copy and Paste Function to default into copying and pasting only the visible cells? I'm using Excel 2016 in Windows 10. I've been doing the Alt + ; and then copying it way but when copying, I never want the hidden cells anyway. So I'm wondering if this could be set as default to avoid the extra step. Thanks!
Parsing JSON to seperate columns
I am trying to convert JSON data that is provided by the Audit log for Office365 for creation of Teams into separate columns so I can use the data more effectively: The data looks like this: { "CreationTime": "2018-04-26T16:31:29", "Id": "xxxxxxxxxxxxxxx", "Operation": "GroupAdded", "OrganizationId": "xxxxxxxxxxxxx", "RecordType": 4, "UserKey": "i:xxxxxxxx@live.com", "UserType": 0, "Version": 1, "Workload": "SharePoint", "ClientIP": "xx.xxx.xxx.xxx", "ObjectId": "https:xxxxxx.sharepoint.com", "UserId": "sxxxx.xxx@xxx.com", "CorrelationId": "xxxxxxxxxxxx", "EventSource": "SharePoint", "ItemType": "Site", "Site": "xxxxxxxxxxxxxx", "UserAgent": "Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/66.0.3359.117 Safari\/537.36", "ModifiedProperties": [{ "Name": "Name", "NewValue": "SharingLinks.1cxxxxxxxxxx-xxxxxxxxxxxxxxxxxxxxxxx" }] } I want the macro to separate the different information into their own columns. I have tried this solution - https://www.mrexcel.com/forum/excel-questions/980302-parse-json-values-excel-cells.html#post4704611 But I can't get it working. Any help much appreciated!
Paste using enter key no longer available
Hi I've used Excel for years and always used the Enter key for pasting data Recently the enter key stopped performing the paste funtion on a couple of machines, just drops one line instead, so you have to use ctrl + V to paste, which i don't want ... (call me stuck in a rut or just too old to change!) Please note paste still works with all teh other methods, like Ctrl+V, right click and the edit menu etc, it's just the enter key that's stopped working for paste... On several other machines I can paste data using the enter key (all running office365 latest versions), so the feature has not been removed, it's just affecting 1 or 2 machines, so not a one off either! This morning on one machine which WAS still working for pasting with enter key, after quiting and restart excel 9not the PC, no updates or anything) it's stopped working, so now none of my own machines work! Enough is enough, I need a fix please! 2 of my machines the enter key stopped pasting a GOOD while ago, all have been running office365 (so excel 2016) for about 18-24months, all from the same family office account, all updated regularly, so currently on version 1711 build 8730.2122. one is Windows 7, rest are all Windows10 x64 I have checked the options and advanced features, but nothing there. I having searched google, found little relevant except a post, which suggested a "full repair online re-install" but this has not fixed the issue. Microsoft linked helpdesk (using the help chat option inside excel) got me nowhere, except a sore head :-( I don't have any clipboard managers enabled on all the machines in question, might have on one of them, and do use a KVM screen sharing tool called Multiplicity on them all, but this has not been updated recently, and did not affect the machine that continued to work using enter (until it stopped earlier today!), so can't see that being a factor? The feature was inbuilt into Excel, it was not an external macro or some VBA, although would consider using a fix using these if that was the only solution (if anyone has some code to share), but I'd much rather fix the issue via correct configuration, or registry key or whatever! Anyone able to help revert the enter key to provide paste capability? Thanks in advance Richard
Conditional Formatting - how to minimize formatting rule duplication with editing
Hi all, I built a spreadsheet for management to track project deliverables, and am running into two issues. I attached a slimmed down version of the spreadsheet for review. 1. The file contains several conditional formatting rules. When I turn it over for use, the end user has to insert rows in the table at times as new projects come in. I think this is causing the conditional formatting rules to explode. As a result, I have lots of rules that pertain to only one or a few cells, instead of the entire range as initially created. Is there a way to prevent this from happening? 2. The Projected columns contain rather complicated nested formulas for projecting due dates. I've asked the end user to NOT manually enter values into these columns, as it erases my formula, but with little luck. Is there a way to protect the integrity of these cells while allowing the end user to still add/remove rows from the table? Any guidance on these issues is greatly appreciated!! Thanks, Casey
