How To Reference Same Cell From Multiple Worksheets In Excel?

At work I create multiple worksheets to store my weekly forecasts and was trying to plot a chart showing the evolution of my forecast week after week. For that I was looking for a way to automatically reference a cell from multiple worksheets on my excel file.

I am not an Excel VBA expert so was afraid the instructions I found here would not work but they did!
===========================================================
Extracted without consent since I was not sure how to ask, all credits should go to the original author of this posting referenced in the following url: https://www.extendoffice.com/documents/excel/1337-excel-reference-same-cell-different-sheet.html

How to reference Same Cell From Multiple Worksheets With some VBA Code

If there are dozens of worksheets, the copy and paste function will be troublesome and time-consuming, in this case, the following VBA code can help you fill the same cell references from multiple worksheets into one worksheet at once.

1. In the Master/last worksheet, click into a cell which is the same cell (same address, for instance C16) that you want to extract from other worksheets.

2. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following code in the Module Window.

VBA code: reference same cell from multiple worksheets

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

Sub AutoFillSheetNames() 'Update 20131202 Dim ActRng As Range Dim ActWsName As String Dim ActAddress As String Dim Ws As Worksheet On Error Resume Next xTitleId = "KutoolsforExcel" Set ActRng = Application.ActiveCell ActWsName = Application.ActiveSheet.Name ActAddress = ActRng.Address(False, False) Application.ScreenUpdating = False xIndex = 0 For Each Ws In Application.Worksheets If Ws.Name <> ActWsName Then ActRng.Offset(xIndex, 0).Value = "='" & Ws.Name & "'!" & ActAddress xIndex = xIndex + 1 End If Next Application.ScreenUpdating = True End Sub

4. Then press F5 key to run this code, and all values of that cell from other worksheets will been pulled down into the Master/latest worksheet.

Note: This VBA code is applied for filling the cells where you click. For example, if you click cell A1 in a specified worksheet, all the values of cell A1 from other worksheets will be filled into this worksheet.

How to rename a Bluetooth device on Windows 10 - a problem with TWS earbuds - air-pods knockoff

I own a pair of Bluetooth air-pods knockoffs from China - i7S-TWS from TWS I assume. The actual brand changes depends where and who do you buy it from but I think they are manufactured by the same company.

In any case, you can use each of the earbud separately or connected but I always struggle when connecting those to my Windows Machine since I never know which one is the left and which one is the right one since they both show up on the device list as tws-i7 !

I was looking for a way to rename those but I do not have that option on the Show Bluetooth Devices window below:

I looked for a solution and finally found the answer!
1) Open your file explorer and on the address/url go to the following address:
Control Panel\All Control Panel Items\Devices and Printers

2) The following will open:

3) Do a right click on the device you want to change the name, select Properties - Bluetooth

4) Edit the device name and hit apply! he device name will remain the same! SORRY, this is just to show you the problem!
5) The are some extra careful steps you will need to take to make this work!

• Make sure that your device is actually connected to your pc before starting step 3
• then repeat step 3 and 4, but this time, wait, DO NOT hit apply yet! Turn off your Bluetooth device first (physically on your device side, not on windows), THEN HIT APPLY on the properties window above and then OK. This time the device name will  change
• reconnect the bluetooth device and it will now show on the Bluetooth & Other devices with the updated name!

Hope this works for you! Pass this blog post around if you found this useful!

Stoping WIndows 10 Microsoft Compatibility Telemetry/Remote Telemetry

I noticed I had a process taking 7% of my cpu time, it was called Microsoft Compatibility Telemetry. A search indicated that this was probably sending info to MS and it would not be a big harm to stop it. I found a way of easily doing that on the link below:
https://www.drivereasy.com/knowledge/windows-10-microsoft-compatibility-telemetry-high-disk-usage-solved/
I used Method 1 below, (taken from that site as is, please use at your own risk! All credit goes to the original authors of the above page!)
=============================================:

1) On your keyboard, press the Windows logo key  and R at the same time to invoke a Runcommand.

2) Type gpedit.msc and click OK.
Note: If you cannot find gpedit.msc on your Windows 10, here is the method to got it.

3) Go to Computer Configuration >  Administrative Templates > Windows Components > Data Collection and Preview Builds.

4) Double-click Allow Telemetry.

5) Select Disabled, then click OK.

How to fix the mouse drifting on Windows 10 on a Toshiba Portege Z30-A

I have been struggling with this problem on and off for years (3 years of ownership) and have fixed this in the past by updating drivers, changing ports, etc. It seems that the problem is fixed and then someday, the same thing happens all over again!

In summary, it seems that the problem is related to the accupoint device (that blue dot in the middle of the keyboard). The way the problem seems to be fixed now is by disabling it all together. How? Follow the instructions below:



How?
Double click the touchpad icon on your system tray. (the one with the red cross)

Then, unmark the Use AccuPoint. Please note that if you tap the left top corner of your touch pad you can turn the Touch Pad on and off. and when you turn it on after unmarking the Use Accupoint, it can also turn the AccuPoint back on! So, go ahead, and see the Use Accupoint turn on and then select it off again. After that turning the touchpad on and off will not affect the AccuPoint. (or at least until it happens again and they you will need to repeat this procedure. Good Luck!

Managing iPhone pictures on Windows (10) - How to consolidate content from multiple folders into one?

So you have finally synced your pictures from your phone into your computer (100s or 1000s pictures) and now you have all these folders with multiple pictures in it so you have to go into each and every folder to see its content?

If you are like me and would like to put all of them in one folder (or a smaller number of folders organized by Year or Month)?  In this way, you can can go through those pictures all at once and not need to open one by one.

I am pretty sure someone may have created a command batch for that but this is how I did it:
1) First of all, you need to be careful to not lose data. Think about the situation where multiple folders contain files with the same name, if you simply move those to the same folder while they are in fact different pictures/files you will lose data. So I did check my folder tree first checked my folder first for duplication. I suggest you to do this outside of file explorer. I have used "Duplicate Cleaner" freeware from DigitalVolcano (found on CNET)  The software is free and very fast. Please read the instructions well since you do not want to make any mistakes here.
2) Once you are sure there are no duplication this is how I have consolidated all the pictures into one directory by:

  - Opening the parent folder which has the pictures/sub-folders. Use search to find all the files you want to copy. For that just click on search box (below ribbon on right top side)  (use an operator like *.jpg or click on the ribbon and choose a kind type, for instance kind:=picture). This will do a search on that head folder and all sub-folders and find the pictures or movies, or file (depending on your search). Now, click on Home-ribbon and Select all. This will select all the files. Now you can just click on Move or Copy on the ribbon and transfer the files to the new directory you are creating.

I hope this is clear enough, if not please ask a question on  your comments.

I have compiled this from multiple sources but mainly from an answer I found here and here. If/When I have more time I will try to add some pictures to this instruction.

Solving problem: Windows snipping tool freezes when controlling screen and you can not kill it since task manager does not stay on top

I use Windows snipping tool quite a lot and never had problems before but after I upgraded to Windows 10 I am getting this problem quite often,

The snipping tool freezes when taking control of screen and you can not kill it since task manager does not stay on top of snippingtool. You still have access to taskbar but can not control any window since snipping tool is blocking that.

It took me a while but I found a work around that at least gives you a way to kill snipping tool in the future. You will need to change the setup of task manager first, to make sure it stays on top when running. You can do that by:
1) Hit Ctrl+Shift+Esc to invoke TaskManager
2) On TaskManager menu, select Options-Always on top

In this way if the same problem happens in the future you can still open taskmanager and try to kill snipping tool. I have note tested this yet but will do so and update this blog.

If you have a way to change the stay on top setup while you have the problem please let me know since this would be an even better way. Someone posted this option on the same link I show below but I could not make sense of the instructions:

This happened to me today on Windows 10 when the Snipping Tool froze while making the capture selection. I was able to get the Task Manager to show up by the following:

1. Press the Windows key, then type Task Manager + Enter to open the Task Manager
2. Press the Windows key again to get the Taskbar in focus
3. Right click the system tray icon for the Task Manager and select "Always on top"

BTW, I have compiled this answer based on discussions and suggestions posted here: How to terminate program that takes over the screen.

I found other options but not sure they really work like this recomendation:

Snipping Tool Freezes
Run this command in command-line, and it will work Again:
C:\Windows\System32\cmd.exe /c echo off | clip

iOS10 Problem - Can not edit a contact found using spotlight search

iOS10 Problem

Not sure if there is an specific option set up for that but I have noticed that I can not edit a contact found using spotlight search as I used to do prior to upgrade to iOS 10.

Before, when clicking on the contact, after search, I would be on the actual contact app and could then edit as needed. Now I can only see the contact and start a call or message or video, or mail or share but not Edit the actual record!

To me this is a pain, since it forces me to go back to contact app, search for the contact again before making any changes to it.

iPhone 6s Plus, iOS 10

Posted on Sep 16, 2016 2:20 PM

I wish I had an answer to this question but I don't. I have posted this to Apple forum here;
https://discussions.apple.com/thread/7669223
and will let you know when / if I get a solution.