Cell referencing between tabs whilst filtering table

JennyG2180 

Suppose your 'Invoiced' sheet looks like this:

In your 'Overview' sheet, you want to reference the 'Total Units' from the 'Invoiced' sheet.

Here is how to do it:

1. For the 'Total Units' column in 'Overview':

=INDEX(Invoiced!$B$2:$B$100, MATCH($A2, Invoiced!$A$2:$A$100, 0))

Explanation:

• INDEX(Invoiced!$B$2:$B$100, ...) refers to the range where the 'Total Units' data is located in the 'Invoiced' sheet.
• MATCH($A2, Invoiced!$A$2:$A$100, 0) finds the position of the site name in the 'Invoiced' sheet that matches the site name in column A of the 'Overview' sheet.
• 0 specifies an exact match.
1. For the 'Units Installed' column in 'Overview':

=INDEX(Invoiced!$C$2:$C$100, MATCH($A2, Invoiced!$A$2:$A$100, 0))

1. For the 'Value' column in 'Overview':

=INDEX(Invoiced!$D$2:$D$100, MATCH($A2, Invoiced!$A$2:$A$100, 0))

1. For the 'Invoiced to Date' column in 'Overview':

=INDEX(Invoiced!$E$2:$E$100, MATCH($A2, Invoiced!$A$2:$A$100, 0))

Summary

Using INDEX and MATCH is a robust method that maintains references accurately even when the data is filtered. This way, you avoid the pitfalls of direct cell references which can change when the table is filtered.

This approach can handle any number of rows, provided the ranges ($B$2:$B$100, etc.) cover all your data. If your data extends beyond 100 rows, adjust the ranges accordingly.

By implementing this, you can ensure that your 'Overview' sheet always shows the correct data from the 'Invoiced' sheet, regardless of any filtering applied. The text and the steps were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.