How to Collect the Cell Value in Another Worksheet

How to Collect the Cell Value in Another Worksheet
Last Updated on 24/07/2023
Reading time: 3 minutes

In Excel, it's easy to collect the value of another cell to reuse it. Let's see different situations in the same worksheet or in another worksheet.

What is the cell reference?

In Excel, a cell is often called by its reference. In other words, it is the intersection of a row and a column, like A1, B5, D8,... For instance, here, the active cell is the cell B3

Reference of a cell

If you are unfamiliar with the Excel keywords, you can do this QUIZ

 Reusing the value of a cell in the same worksheet

In Excel, it's easy to reuse the content of another cell.

  1. Select any empty cell in your worksheet
  2. Press the =sign
  3. Select the cell to link (here A1)
  4. The complete formula is displayed below (easy to understand 😉)

=A1

Link a cell to another one

Remarks. When you refer to another cell

  • The content is duplicated, whatever the type of data, Text or Number
  • The format of the cell selected is not repro

Why it's important?

Creating such a reference is not complicated, and it's the best way to work with Excel. Because now, each time you update the value in the source cell, the linked cell is automatically updated

Change cell value with a linked cell

Reusing the value of a cell in another worksheet

If you want to link a cell from another worksheet, the steps are the same

  1. Select any cell in another sheet
  2. Press the =sign (this activates the edit mode)
  3. Return to the worksheet with the cell that you want to link
  4. Select the cell with the content to link

The formula is now:

=SheetName!Cell Reference

Collect cell value from another worksheet

The delimiter between the Sheet name and the cell reference is ALWAYS an exclamation mark (!)

What happens if you change the Sheet's name?

If you change the Sheet's name, Excel will automatically change the reference in the destination cell.

Impact of renaming a worksheet in a cell reference

Copy a formula with references

When you copy a formula that contains references to other cells, the action to copy will change the references according to the direction of the copy.

For example, let's take the formula in cell C2 for calculating the total.

=A2*B2

When you copy down this formula, all the references will be changed as follows:

  • The reference of the row number changes
  • The reference of the column letter remains the same

Copy a formula containing a reference to the right

Now, if we copy a formula to the right:

  • The reference of the column letter changes
  • The reference of the row number remains the same

This example shows a cumulative sum of sales from month to month. C4 contains the formula:

=B3+C2

Now, by copying this formula to the right, all column references change but not those of the rows.

As you can see, depending on the direction in which you copy your formula, the cell references will be modified according to the direction of the copy. It's very clever😊

The absolute or relative reference

As you can see, each time we copy a formula with references, the references change. It's because we have written relative references.

But we can avoid that by fixing the references. We call them absolute references.

In this article, we will see how to use them.

Leave a Reply

Your email address will not be published. Required fields are marked *

How to Collect the Cell Value in Another Worksheet

Reading time: 3 minutes
Last Updated on 24/07/2023

In Excel, it's easy to collect the value of another cell to reuse it. Let's see different situations in the same worksheet or in another worksheet.

What is the cell reference?

In Excel, a cell is often called by its reference. In other words, it is the intersection of a row and a column, like A1, B5, D8,... For instance, here, the active cell is the cell B3

Reference of a cell

If you are unfamiliar with the Excel keywords, you can do this QUIZ

 Reusing the value of a cell in the same worksheet

In Excel, it's easy to reuse the content of another cell.

  1. Select any empty cell in your worksheet
  2. Press the =sign
  3. Select the cell to link (here A1)
  4. The complete formula is displayed below (easy to understand 😉)

=A1

Link a cell to another one

Remarks. When you refer to another cell

  • The content is duplicated, whatever the type of data, Text or Number
  • The format of the cell selected is not repro

Why it's important?

Creating such a reference is not complicated, and it's the best way to work with Excel. Because now, each time you update the value in the source cell, the linked cell is automatically updated

Change cell value with a linked cell

Reusing the value of a cell in another worksheet

If you want to link a cell from another worksheet, the steps are the same

  1. Select any cell in another sheet
  2. Press the =sign (this activates the edit mode)
  3. Return to the worksheet with the cell that you want to link
  4. Select the cell with the content to link

The formula is now:

=SheetName!Cell Reference

Collect cell value from another worksheet

The delimiter between the Sheet name and the cell reference is ALWAYS an exclamation mark (!)

What happens if you change the Sheet's name?

If you change the Sheet's name, Excel will automatically change the reference in the destination cell.

Impact of renaming a worksheet in a cell reference

Copy a formula with references

When you copy a formula that contains references to other cells, the action to copy will change the references according to the direction of the copy.

For example, let's take the formula in cell C2 for calculating the total.

=A2*B2

When you copy down this formula, all the references will be changed as follows:

  • The reference of the row number changes
  • The reference of the column letter remains the same

Copy a formula containing a reference to the right

Now, if we copy a formula to the right:

  • The reference of the column letter changes
  • The reference of the row number remains the same

This example shows a cumulative sum of sales from month to month. C4 contains the formula:

=B3+C2

Now, by copying this formula to the right, all column references change but not those of the rows.

As you can see, depending on the direction in which you copy your formula, the cell references will be modified according to the direction of the copy. It's very clever😊

The absolute or relative reference

As you can see, each time we copy a formula with references, the references change. It's because we have written relative references.

But we can avoid that by fixing the references. We call them absolute references.

In this article, we will see how to use them.

Leave a Reply

Your email address will not be published. Required fields are marked *