Referencing Another Sheet in Excel: Use Pattern Every 15 Rows

Excel provides an efficient way to reference data from one sheet to another. In this article, we will discuss how to reference another sheet with a repeating pattern every 15 rows.

Background

When working with large Excel workbooks, it is common to have data spread across multiple sheets. In such cases, it is necessary to reference data from one sheet to another. Excel offers various ways to do this, and one of the most common methods is by using formulas.

Prerequisites

To follow along with this article, you will need:

Two or more Excel sheets in the same workbook.
Data in the referencing sheet that matches the pattern in the referenced sheet.

Steps to Reference Another Sheet Every 15 Rows

To reference another sheet every 15 rows, follow these steps:

Identify the cells in the referencing sheet where you want to insert the reference.
Use the INDIRECT function to reference the cell in the referenced sheet. For example:
```
=IF(INDIRECT("'Sheet2'!B40").Value > 0, INDIRECT("'Sheet2'!B40").Value, ...)
```
Replace "Sheet2" with the name of the referenced sheet and "B40" with the cell reference in the referenced sheet. The INDIRECT function returns the address of the cell specified as a reference.
Wrap the INDIRECT function inside an IF statement to check if the value in the referenced cell is greater than zero. Replace the ellipsis (...) with the rest of the formula.
Drag the formula down to copy it to the next 14 rows.
Repeat steps 2-4 every 15 rows to reference the next block of data in the referenced sheet.

Example Formula

Here's an example of an formula that references data every 15 rows:

=IF(INDIRECT("'Sheet2'!B40").Value > 0, INDIRECT("'Sheet2'!B40").Value, IF(INDIRECT("'Sheet2'!B55").Value > 0, INDIRECT("'Sheet2'!B55").Value, IF(INDIRECT("'Sheet2'!B70").Value > 0, INDIRECT("'Sheet2'!B70").Value, IF(INDIRECT("'Sheet2'!B85").Value > 0, INDIRECT("'Sheet2'!B85").Value, IF(INDIRECT("'Sheet2'!B100").Value > 0, INDIRECT("'Sheet2'!B100").Value, ""))))

This formula references the value in cell B40 in Sheet2, then every 15 rows until the end of the sheet.

In this article, we discussed how to reference another sheet in Excel with a repeating pattern every 15 rows. We used the INDIRECT function to reference cells in the referenced sheet and wrapped it inside an IF statement to check if the value was greater than zero. By repeating this pattern every 15 rows, we were able to reference data from the referenced sheet efficiently.

Referencing Another Sheet in Excel: Use Pattern Every 15 Rows