Forum Discussion
Writing updated info across several worksheets
Excel 2010: Am creating a worksheet for a coworker who enters equipment calibration dates and other details for all the calibrated equipment in our labs. He currently has one workbook with multiple ...
I think I understand what you're saying. Ingeborg is right though, structured this way is backwards.
If the data you're looking up is in a Table, then you can reference it and never worry about it. My assumption is the data being entered on these other sheets isn't in a Table, and keeps growing, thus you don't always have the most up-to-date range to reference. The easy fix is to make them into Tables. Can you do that? If not I would use a named range. Of course I recommend a good data structure, like Ingeborg suggested.
That makes three... like Zack mentioned if you can't use a table then go for a named range. See the attached file for how to get a dynamic named range as a list or as a table.
You can use OFFSET() and COUNTA() to accomplish this in most cases:
OFFSET : https://support.office.com/en-us/article/OFFSET-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66
COUNTA: https://support.office.com/en-us/article/COUNTA-function-7dc98875-d5c1-46f1-9a82-53f3219e2509
