Hi everyone,
I have an excel issue with sorting data (with dynamic formulas) that's in a table. I've included a Google Drive link to download the file as the attachment feature here isn't showing up for me ....
https://drive.google.com/open?id=1mn...BLGmlCArhOYtHz
Background info:
- Spreadsheet contains 3 sheets: Sheet A, B, and C
- Sheet A grabs data from sheet B that matches the names in columns B and C
- This is done using index and match (I used array formula CTR+SHIFT Enter)
- The data is inputted into column D
Problem:
The problem occurs when I turn this into a table and sort by spend for example. The index match formula in column D doesn't move with sort and stays in the fixed rows. Please reference Sheet C for an illustration of the problem.
You'll see that the spend for Google / AAG_S_Reverse_Mortgage_SKAG_Tier_3 is $336 instead of $525.
>> How do I make it so that the match portion of the formula moves as the table is sorted? I only locked the columns in place and not the rows, so I don't understand why it isn't moving as the table is sorted...
Looking for help from the Excel ninjas!
Bookmarks