Posted by: Yosef B. | August 28, 2014

Managing SharePoint List Permissions


I typically don’t do a lot in Microsoft SharePoint, however on my current project I’ve been working on building out a little site that has required learning some new things about it. In particular, I had a main site, with multiple lists on the site. I also have multiple groups of people setup and I wanted to ensure that each group can view the main site, but then can only view their specific list.

So it turns out that if you create a group at the site level than it automatically overrides permissions at the list level, even if you tell a list to not inherit permissions from the parent site. What you have to do is create a group at the site level but do not select any of the permission levels, just create the group without any permissions. This will create the group with “Limited Access” permissions that does not allow them to view or do anything.

Then, you go to the specific list you want to grant the group permissions to and add the group there, granting them permissions directly at the list level. In my case, I gave them “View Only” permissions because it allows users to view lists and look at documents attached to the list.

Finally, in order for users to view the main site (but not any lists below the main site), create and add them to a Visitor’s group that you grant “Read” permissions too. This restricts them to view the site, but not view any lists (besides the specific ones you grant permissions too).

Hope this helps!

The two Microsoft help articles I referenced are:

Remove users and groups from site access – Windows SharePoint Services

Customizing user access to a SharePoint list or library – SharePoint Server

Posted by: Yosef B. | August 6, 2014

Excel: How to use the INDIRECT function – Example


I received a request for a follow-up post to my last post on how to use the INDIRECT function in Excel. The request was to create a sample table showing a potential setup and use of it.

The best use I’ve found for the INDIRECT function is to build dashboards. You can have all your data stored on one sheet and then use INDIRECT formulas in combination with others to pull over only the data you want in a quick and easy manner.

In the picture below, there are two sample tables. The one on the top left is my “Original Data Table” which could be stored on any tab. The “INDIRECT Table” on the right uses INDIRECT formulas to pull over the data from the first, original table.

INDIRECT1

Remember that the INDIRECT formula takes in a text string and converts it to a cell reference. In this case, I put my column references as table headers and my row references as row IDs for my INDIRECT table. In this second picture, you can see how I combine the column reference & row reference in my INDIRECT formula to create a formula that is easily copied over to all the other cells in my table.

INDIRECT2

With properly setup column & row references, I need only write a single INDIRECT formula to pull over all my data in the correct order. I could just have easily made it skip every other row or skip a couple of columns, or even reorder the columns or rows by reordering my column & row references. My INDIRECT formula wouldn’t have to change at all!

Hope this helps! Let me know if you have any further questions.

Thanks!
~Yosef

Posted by: Yosef B. | August 5, 2014

Excel: How to use the INDIRECT function


The Excel INDIRECT function is useful for displaying data in another cell by passing it text strings to tell it where the value is.

The syntax for the Indirect function is as follows:

INDIRECT(ref_text,[a1])

Where:

ref_text is a text reference to another cell & [a1] is a logical value that specifies what type of reference style ref_text is using.

In excel you have two types of cell reference styles:

  1. A1 style is where each row has a number and each column has a letter (the default view in excel).
  2. R1C1 style is where each row has a number and each column has a number.

A1 is indicated by [a1] = “TRUE”, R1C1 is indicated by [a1] = “FALSE” in the INDIRECT formula.

For example in order to display a specific column of data in a given row (in this example, row 7), the following function is used:

=INDIRECT($B$1&$A7,TRUE)

The first argument, $B$1&$A7 concatenates the values in cell B1 & A7 together to create a text statement of where the data that needs to be displayed is stored. In this case B1 = “Data!B”, A7 = 3 so the final result is “Data!B3” so the value displayed in the cell is the value on the Data tab in the B3 cell.

Column A now displays the row of the data for that particular project on the “Data” tab.

Hope you find this useful!

~Yosef


Excel comes with a handy tool called the “Formula Auditing tool” located on the Ribbon under the “Formulas” section under “Formula Auditing”.

(Click to Enlarge)

(Click to Enlarge)

  1. The Trace Precedents tool shows you visually which cells are used by the selected cell in it’s formula.
  2. The Trace Dependents tool shows you visually which cells are used by the selected cell in it’s formula.
  3. The Evaluate Formula tool walks you through the formula in the selected cell so you can see how it’s calculating the formula. If there is an error, this is a handy way of figuring out where the calculation goes wrong so you can potentially fix it.
(Click to Enlarge)

(Click to Enlarge)

Simple right? If not – feel free to leave a comment below & I’ll try to help!

~Yosef

Posted by: Yosef B. | July 14, 2014

How to Hide Pivot Chart Filters


(Directions below are for Excel 2010)

I recently built a simple dashboard using pivot tables with corresponding pivot charts. I was asked by my client if I could remove the filters from the pivot charts so that they would have a cleaner look for presenting. I had never worried about this before and didn’t know how off the top of my head so I did a couple of quick Google searches & couldn’t find any references on how to hide the filters! I was very surprised that I couldn’t find anyone else asking the question so I thought I would share how to easily do this. Luckily it’s pretty simple – I just didn’t realize these options existed!

Here’s a Pivot chart with the usual filters visible:

Pivot with Filters

To remove these filters, click the “Field Buttons” (or drop down for more control) button on the Analyze tab of the PivotChart Tools section of the menu ribbon (only visible if the chart is selected):

Pivot Chart Filter/Field Buttons

You can then choose which (if any) filters to display so that your chart looks nice & clean:

Pivot Chart w/o Filters

Hope this helps!

~Yosef B.

Posted by: Yosef B. | July 7, 2014

Excel: Creating a Drop Down Menu


Ever wondered how to add a drop down menu to your Excel file? Here’s an easy way to add one wherever you want!

To create a drop down menu, you have two options. First, you can create a list of all the items you want to display in your Drop Down List. E.g. “Yes, No, Maybe” or “Early, Late, None”, etc. in a range of cells (one option per cell). Alternatively, simply copy a list of items, ensuring that they are comma delimited.
Once you have your items listed:

  1. Select the Data Tab from the Excel Menu Ribbon. In the “Data Tools” section, click on “Data Validation”.
  2. Under the Settings Tab, select “List” from the “Allow” drop down menu.
  3. (Click to enlarge)

    (Click to enlarge)

  4. Using the arrow on the right hand side of the “Source” box, select the list of items that you want to display in your Drop Down List.
  5. Click “OK”
(Click to enlarge)

(Click to enlarge)

Got questions? Leave a comment below!

~Yosef

Posted by: Yosef B. | June 30, 2014

Excel: Conditional Formatting


Excel provides the ability to automatically apply specified formatting to a cell based upon the value in the cell or by comparing the value of the cell vs. the value in another cell.

Here are instructions to apply basic conditional formatting:

  1. Select the first cell which you wish to have automatically highlighted based on a value in another cell.
  2. Select the Home Tab on the Ribbon. In the “Styles” section select “Conditional Formatting”. In the drop down menu select “New Rule…”.
  3. Conditional_Formatting1

  4. Select “Use a formula to determine which cells to format”
  5. In the “Format values where this formula is true:” box type in a formula that describes the values in the cell the highlighted cell is based on. For example, if you want cell A1 to change to green when cell B1 contains the value “1” type “=if(B1=1,TRUE,FALSE)”. This formula will return “TRUE” if B1 contains a 1, otherwise it will return “FALSE”. Your cell will only change color if the returned value of the function is “TRUE”.
  6. You can overlay multiple formatting rules on the same cell – therefore you can have the cell turn one color based on one rule and another color based off another rule. You do need to be careful that the formulas will not both be true at the same time otherwise your conditional formatting will not work.
    • Note: In addition to an equality, you can also use any other comparative logic symbol. Examples include: “Does not equal” e.g. =if(A1B1,TRUE,FALSE) or “Is greater than” e.g. =if(A1>B1,TRUE,FALSE).
  7. Select the “Format…” button and apply whatever formatting you want to occur when the formula returns “TRUE”.
  8. Conditional_Formatting2

  9. Click “OK”
  10. To apply your formatting to multiple cells, select the “Format Painter” tool and copy your formatting to other cells.

Pretty straight forward but if you have questions, as always leave a comment & I’ll get back to you!

~Yosef

Posted by: Yosef B. | June 11, 2014

Writing a Bring Your Own Device (BYOD) Policy


I was recently asked to write a Bring Your Own Device (BYOD) policy for a company. While I have some experience writing policies, I had never done a BYOD policy and it opened up a lot of questions which I had not thought of and I thought I would share them for your consideration.

In this day & age, a company must have a BYOD policy due to the simple fact that virtually all employees have smartphones & have access to company (&/or client) data on their phones (e.g. through Dropbox, Google Drive, or by having data directly on the device, etc.). Your company must have an official position on how to handle this access to data in order to be in compliance with any NDAs, contracts, etc. A major concern your policy should handle is – what happens when an employee leaves your company & takes a device that stores company data with them?

You need to be very clear of who owns what. The employee owns the device but who owns the apps & data? For example – what happens if the employee is terminated and the company remote wipes their device to remove company data, but this also deletes personal data? Is the company liable?

What types of security should a company require & how will you enforce your requirements? Do you require just a password? Do you require whole device encryption? Do you require the company to have the ability to remote access / wipe the device?

Who is responsible for supporting devices? If so, What devices are allowed or included in the policy? What level of support will you provide?

What does an employee have to do to leave with their device? Do they have to prove that they’ve removed data, apps, access?

What do you do if you find illegal information or activities (such as child pornography) on the device? Does it make a difference if it’s in an “area” of the device that’s not work related?

What level of involvement does the legal team need to have? What rights does the company have to audit or look at the device holistically vs. just company data, apps, etc.? Example being if there are multiple email accounts in the same app (work & non-work), what rights does the company have to access just the work email account of the app (if it’s even technically possible)?

If your employees are consultants and work for multiple clients and those client’s email requires different device management controls – how do you reconcile multiple security policies on the same device?

An interesting fact I came across is Juniper Networks released results of a survey (back in 2012) of more than 4,000 mobile-device users and IT professionals & found that many employees circumvent their employers official mobile-device policies, with 41% of all respondents who use their personal devices for work doing so without permission from the company.

In other words, at a bare minimum, you need a policy to cover yourselves legally but it may not be enforceable & you may just have to live with that (which could unfortunately backfire in court as well – if the argument is that you had a non-enforceable policy, the court could invalidate the entire policy).

Tricky thing writing policies…

Got an example of a good or bad BYOD policy? I would love to hear from you!

~Yosef B.

Posted by: Yosef B. | May 28, 2014

What do I do with my old…?


Got old electronics & don’t know what to do with them? Here’s a few ideas to get you started:

Sell
Try selling your electronics online. You can use services such as worthmonkey.com to find the value of your electronics, then post them to Craigslist, Amazon, or eBay.

Recycle
Many different retailers now offer free electronics recycling – some will even pay you (minimally) for your stuff. A lot of original manufacturers (such as Dell, Samsung, Apple, etc.) offer recycling programs as well. Try BestBuy, Office Depot, & Staples and see if they’ll take your unwanted items off your hands.

Donate
Another option, assuming your electronics are still usable is to donate them to a good cause. Both Goodwill, the Salvation Army, & Dell have strong electronics reuse programs which you can donate to.

Reuse
Turn your electronics into a piece of art, a dedicated media center, a planter, a media server, an electronic photo frame, a fish bowl,… the list is endless! Try an internet search for “How to reuse…” and fill in the blank. You’ll be amazed at the number of amazing ideas people have for reusing old junk.

Hand-Me-Down
Another option is to give your old electronics to your kids or nieces and nephews to use. You can also use it as a learning experience and let them take the electronics apart to figure out how they work.

Trade
If your electronics aren’t too old, you may be able to trade them in towards store credit or cash at BestBuy, GameStop, and other fine electronics retailers.

Note: Before throwing out or getting rid of old electronics, make sure you aren’t giving away your private information! Always remove hard drives (I suggest going to town with a sledgehammer on them after removal) before donating. If you’re trying to sell the item, at least make sure that you’ve wiped the hard drive numerous times using a program such as CCleaner or Eraser.

Got a particular use you like for old electronics? Share it below in the comments!

Posted by: Yosef B. | May 7, 2014

Heartbleed’ing – what do I do?


With the Heartbleed bug in the news recently, a lot of folks have asked me for advice on what to do.

First off, here’s a quick description of what Heartbleed is:

Wikipedia states: Heartbleed is a security bug in the open-source OpenSSL cryptography library, which is widely used to implement the Internet’s Transport Layer Security (TLS) protocol. This vulnerability, classified as a buffer over-read, results from a missing bounds check in the handling of the Transport Layer Security (TLS) heartbeat extension, the heartbeat being behind the bug’s name.

In plain(er) English – this means that websites that use a certain version of encryption (aka OpenSSL), are vulnerable to sharing encrypted information with anyone who queries the website in a certain way. A website’s address that starts with https:// (note the “s”) use SSL to encrypt the transaction of data back and forth between the website and your browser.

To find out if a website is affected, you can use a bunch of different tools to check the version of OpenSSL used by a website. The easiest one that I’ve come across is LastPass’s Heartbleed checker available here.

When you plug-in a website it will tell you if the website uses OpenSSL & if so, if it’s safe to use the website or not. Here’s a screenshot of what the checker tells you about yahoo.com:

LastPass Heartbleed Checker

As you can see, LastPass says that Yahoo used to use the problematic OpenSSL but has since fixed it on their website and that it’s now time to change any Yahoo passwords that you may have.

If a website hasn’t updated their OpenSSL to a fixed version, there’s really no point in changing your password – I would recommend minimizing or ideally not using the website until it has been fixed. If possible, contact the website & tell them that you are not using their website until they get it fixed.

Hope this information helps explain what Heartbleed is & what to do about it – stay safe!

~Yosef

Older Posts »

Categories

Follow

Get every new post delivered to your Inbox.

Join 163 other followers

%d bloggers like this: