Okay,  This is where the magic happens: The Indirect formula

So now we have a list, we know how to name ranges but we need the list to look at the contents of other cells to populate itself.  The way we do this with the Indirect formula

So you will need to have all the lists written already and have given them unique names for this formula to work

How I do it is pretty simple

First, I create a simple list in the first column.  Just a regular data validation list – nothing fancy but this serves as the basis for the rest of the lists.  The entries in this list are the named ranges for your other lists so basically it is a drop down list with the names of the other named ranges

Second you need to use the formula =indirect(a1) in the second (b) column.  The second column has a formula that basically says to look at the list in the first column and return that as the named list. Not working!?  This was causing me some headaches until I realised that you have to =indirect(a1) as a data validation as well.  So the end result of cell b1 is a list with =indirect(a1) as the source

Drag down and/or drag across and the lists will start populating themselves.  The real key here is to make sure that the entries in a list are also the same names as the named ranges.

Happy Nerdings!

This is the continuation of my previous post. In it I described how to make lists in Excel.

Here we are going to look at Named Ranges

So taking what we built last time (that column of data) and then using the Define Name option allows for some wizardry

First you need the list

Second you need to select the list and (including a blank space) right click and select “Define Name”. Give the list a name. Now you can use this as the source for the list

Third is to go back into data validation and create the list but this time in the source field you will be entering this

old: A1:a5

New: =”list name” (which is the name of the list you created – so if you gave the list a name like ThisIsMyCoolList then the source field will read as =ThisIsMyCoolList

Next post – Indirect formula

Happy Nerdings!

Hi,

Its happened again.  I got lost in Excel world.  I spent way to much time there but came out with another gem.  The “indirect” list

So here is the deal: I wanted to have lists that populate people based on a group choice

It takes about 3 pieces of knowledge to make this work

First, you need to know how to make lists

Second, you need to know how to name ranges

Third, and coolest, is the “Indirect” function

Over my next 3 posts I will talk about these

In this post we will look at creating lists. This is done in a few steps

Start by creating a source list.  This is where Excel will look when you tell it to create the list. It is just a list of the names/numbers you want to use.

Next you have to select the cell where you want the list to be (this can be copied to other locations later

Next you need to create the list function in Excel.  On the data tab (in Excel 2016) you will see the option for “Data Validation”.  Click the Data Validation and in the new window under validation criteria select: “List”.  Now point the “Source” to the list you created previously (pro tip: also include a blank cell so you can have that as a choice if needed). And voila!  You got a nice little list that you can copy around your spreadsheet universe 😉

Happy nerdings 🙂

How to tell when 2 machines use the same IP

Starting with the machine name do a PING

That will give you the IP

Then do an NSLOOKUP on the IP

It will give you the other computer name

Map the C: drive from command prompt (like this: net use x: \\(other computer name)\c$

Look through the users folder and you should find who owns the machine.

Not so bad, eh!?

As with all products these days there is more than one way to skin a cat. A terrible metaphor I know especially since I love cats and dogs and all 4 legged animals.

I guess what I’m trying to say is that there is always more than one way to do things in this era of integration.

So here is the common way:

1. Simply right-click the email in question and select “junk”
2. then block sender

But what if you don’t have the email?  If you know the address but don’t have the email anymore:

1. Onthe On
2. On the “Home” tab, in the “Delete” section, click on “Junk”
3. Click on “Junk Mail Options”
4. Click on “Blocked Senders” tab
5. Click “Add”
6. Add the senders email address
7. Click OK

There are lots of other interesting things in this window

On the Options tab there is the default methods to handle junk – this can be reviewed and you can change the way Outlook handles Junk email

You can also block countries if you wish. You do this on the International tab.

On the same tab you  can also block character sets so you can block emails written in certain languages if you need to.

Very cool

Happy postings 🙂

What do you do when all your icons are weird.  Not sure? I wasn’t either.  Then I found this: How to rebuild your icon cache for Windows Vista, Window 7 and Windows 8

1. Go here: C:\Users\Username\AppData\Local\IconCache.db
2. Delete the file Iconcache.db
3. Reboot.
4. Done!

Note that you will have to change the view settings to show hidden files and folders first.

UPDATE:  Here is what to do with WIndows 10

1. Navigate to the this location: C:\Users\<your username>\AppData\Local\Microsoft\Windows\Explorer (note that you will have to enter your username in the second field)
2. You will see numerous files names iconcache_XXX.db.  Delete them all
3. Now note this : you may have issues deleting all the files as they are in use by the system.  Here is the solution to that: Command Prompt
4. In command prompt enter: cd C:\Users\(username)\AppData\Local\Microsoft\Windows\Explorer
5. In Windows open Task Manager and kill the Explorer process
6. Alt tab back to the command prompt
7. in command prompt type: del iconcache*
8. Reboot.

Why it is so hard in WIndows 10 – I have no idea but at least there is a solution out there!

Happy hunting!

Command lines are the way to go for so much.  In my last post we looked at SCCM communications from the command line.  In this one we are still looking at SCCM command lines but this time it is triggering actions.

Here is the base command:

WMIC /namespace:\\root\ccm path sms_client CALL TriggerSchedule {Triggercode} /NOINTERACTIVE

Now to make the command work you need a list of values to enter in place of the “Triggercode” in the curly braces. Here they are:

{00000000-0000-0000-0000-000000000001} Hardware Inventory
{00000000-0000-0000-0000-000000000002} Software Inventory
{00000000-0000-0000-0000-000000000003} Discovery Inventory
{00000000-0000-0000-0000-000000000010} File Collection
{00000000-0000-0000-0000-000000000011} IDMIF Collection
{00000000-0000-0000-0000-000000000012} Client Machine Authentication
{00000000-0000-0000-0000-000000000021} Request Machine Assignments
{00000000-0000-0000-0000-000000000022} Evaluate Machine Policies
{00000000-0000-0000-0000-000000000023} Refresh Default MP Task
{00000000-0000-0000-0000-000000000024} LS (Location Service) Refresh Locations Task
{00000000-0000-0000-0000-000000000025} LS (Location Service) Timeout Refresh Task
{00000000-0000-0000-0000-000000000026} Policy Agent Request Assignment (User)
{00000000-0000-0000-0000-000000000027} Policy Agent Evaluate Assignment (User)
{00000000-0000-0000-0000-000000000031} Software Metering Generating Usage Report
{00000000-0000-0000-0000-000000000032} Source Update Message
{00000000-0000-0000-0000-000000000037} Clearing proxy settings cache
{00000000-0000-0000-0000-000000000040} Machine Policy Agent Cleanup
{00000000-0000-0000-0000-000000000041} User Policy Agent Cleanup
{00000000-0000-0000-0000-000000000042} Policy Agent Validate Machine Policy / Assignment
{00000000-0000-0000-0000-000000000043} Policy Agent Validate User Policy / Assignment
{00000000-0000-0000-0000-000000000051} Retrying/Refreshing certificates in AD on MP
{00000000-0000-0000-0000-000000000061} Peer DP Status reporting
{00000000-0000-0000-0000-000000000062} Peer DP Pending package check schedule
{00000000-0000-0000-0000-000000000063} SUM Updates install schedule
{00000000-0000-0000-0000-000000000071} NAP action
{00000000-0000-0000-0000-000000000101} Hardware Inventory Collection Cycle
{00000000-0000-0000-0000-000000000102} Software Inventory Collection Cycle
{00000000-0000-0000-0000-000000000103} Discovery Data Collection Cycle
{00000000-0000-0000-0000-000000000104} File Collection Cycle
{00000000-0000-0000-0000-000000000105} IDMIF Collection Cycle
{00000000-0000-0000-0000-000000000106} Software Metering Usage Report Cycle
{00000000-0000-0000-0000-000000000107} Windows Installer Source List Update Cycle
{00000000-0000-0000-0000-000000000108} Software Updates Assignments Evaluation Cycle
{00000000-0000-0000-0000-000000000109} Branch Distribution Point Maintenance Task
{00000000-0000-0000-0000-000000000110} DCM policy
{00000000-0000-0000-0000-000000000111} Send Unsent State Message
{00000000-0000-0000-0000-000000000112} State System policy cache cleanout
{00000000-0000-0000-0000-000000000113} Scan by Update Source
{00000000-0000-0000-0000-000000000114} Update Store Policy
{00000000-0000-0000-0000-000000000115} State system policy bulk send high
{00000000-0000-0000-0000-000000000116} State system policy bulk send low
{00000000-0000-0000-0000-000000000120} AMT Status Check Policy
{00000000-0000-0000-0000-000000000121} Application manager policy action
{00000000-0000-0000-0000-000000000122} Application manager user policy action
{00000000-0000-0000-0000-000000000123} Application manager global evaluation action
{00000000-0000-0000-0000-000000000131} Power management start summarizer
{00000000-0000-0000-0000-000000000221} Endpoint deployment reevaluate
{00000000-0000-0000-0000-000000000222} Endpoint AM policy reevaluate
{00000000-0000-0000-0000-000000000223} External event detection

Communication is great when it works. When it doesn’t it all goes to caca.  This is true for SCCM as well as life and IT in general (drawing life lessons from IT -why not 🙂 )

So here is what we have been seeing: sometimes (for some unknown reason) client machines lose their connection to the server.  In the past we re-imaged them but then one of my co-workers found a really handy command to try and reset the connection.

Here it is:

From SCCM command line: C:\Windows\CCM\ccmrepair.exe

From WMIC command line: WMIC /namespace:\\root\ccm path sms_client CALL RepairClient

From WMIC command line to a remote computer: WMIC /node:[COMPUTER] /namespace:\\root\ccm path sms_client CALL RepairClient

From PowerShell:

Local Computer
$oSCCM = [wmiclass] “\root\ccm:sms_client”
$oSCCM.RepairClient()

Remote Computer:
$oSCCM = [wmiclass] “\\[COMPUTER]\root\ccm:sms_client”
$oSCCM.RepairClient()