How to link to values in another Excel 2010 worksheet or workbook

In Excel 2010 or 2007, if you want data from one worksheet to be linked to, or shown in another worksheet then follow these steps:

1. In the sheet you want to link to (source) select the cell where the value is stored.

2. Right click and select Copy.

XL10Link1

3. In your main sheet (target), select the cell where the value is to be displayed and right click on it.

4. In the displayed context menu, under Paste Options, click on Paste Link (the last icon to the right).

XL10Link2

5. The value in the source sheet will now be reflected in the target sheet. Notice the link to sheet1 in the formula bar.

XL10Link3

6. Every time the source sheet is updated, the target sheet will also be updated with the new values.

7. The same procedure can be applied to link to data in another workbook.

 

How to convert numbers between decimal and hexadecimal

To convert numbers between decimal and hexadecimal you use the equivalent Excel built-in function. To convert from decimal to hexadecimal use the function DEC2HEX. Syntax DEC2HEX(number;places) Number: The decimal number you want to convert … [Continue reading]

Use INDEX and MATCH functions for table lookup

  The table above lists some internet Top Level Domains (TLD) and their equivalent country codes. How can we look up the country name from the table if we know its TLD? E.g. we have the code “cn” and we want to know the corresponding country … [Continue reading]

How to get the day of the week as text in Excel 2010

Excel WEEKDAY function will return the day of the week as a number. See details here: How-to-get-the-day-of-the-week-in-excel-2010 To return the day of the week as text (name of the day), we combine the WEEKDAY function with the TEXT function … [Continue reading]

How to get the day of the week in Excel 2010

To get the day of the week as a number you use Excel WEEKDAY function. Syntax WEEKDAY(serial_number,[return_type]) Serial number: a serial number that represent the date. Return type (optional): a number that determines how the weekday is … [Continue reading]

How to change the range of a defined name in Excel 2010

In a previous post I explained how to define named ranges in Excel 2010. OK, you have defined a name for a specified range. Later on you decided to change the size of that range either by expanding it or reducing it. Here is how you do it: 1. … [Continue reading]

How to define named ranges in Excel 2010

If you have a range of cells that hold a specific type of data e.g. Sales, No. of students etc. then you can give that range a meaningful name. Later if you want to refer to the range you can do that by specifying its name instead of using its cell … [Continue reading]

How to calculate age in Excel 2010

To calculate a person (or anything else) age you basically subtract his date of birth from today’s date. However which date functions to use depends on the amount of precision and the output format you want. Suppose that we want to calculate the … [Continue reading]

New dynamic yearly calendar

I have added to the downloads page a new dynamic yearly calendar template.  You access the calendar through a user form which has two combo boxes: the first one to specify the year and the second  to select the start date of the week. You … [Continue reading]

How to protect VBA code in Excel 2010

If you have an EXCEL workbook that includes some VBA code, and you want to hide that code, either to protect your intellectual property or simply to prevent others from messing up your code accidently , then follow these steps: 1. In the Developer … [Continue reading]