Forum Discussion
Cell referencing between tabs whilst filtering table
I have been cell referencing between 2 tabs on the same worksheet to carry data across from 'Invoiced' to an 'Overview' page to see budget comparisons on an install project.
However, I have noticed when filtering the 'Invoiced' tab where the data is drawn from the cell reference changes to another cell as the lines have moved, even when I unfilter it is is not correct! (note I have made the reference constant but it still doesn't work)
I believe the only way I can still cell reference whilst using a filtered worksheet is using VLOOKUP - is there any other way as this gets too complicated?!
This is the 'Overview' below as an example.
As you have suggested, the formulation that might interest you is probably an INDEX/MATCH or similar.
Can you attach a sample document without sensitive data or upload additional screenshots of the "Invoiced" tab as well as explain what is the desired output from the formulation?
I feel it would be easy to help with this problem in that way!
Suppose your 'Invoiced' sheet looks like this:
Site Name
Total Units
Units Installed
Value
Invoiced to Date
C Road
3
0
1,268.00
800.00
D Road
4
1
1,671.00
1,200.00
In your 'Overview' sheet, you want to reference the 'Total Units' from the 'Invoiced' sheet.
Here is how to do it:
- 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.
- For the 'Units Installed' column in 'Overview':
=INDEX(Invoiced!$C$2:$C$100, MATCH($A2, Invoiced!$A$2:$A$100, 0))
- For the 'Value' column in 'Overview':
=INDEX(Invoiced!$D$2:$D$100, MATCH($A2, Invoiced!$A$2:$A$100, 0))
- 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.
