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.
Hello All,
The data structure is less than ideal and does need an overhaul, this is a known issue. My coworker inherited the workbook and the responsibility a few years ago, but is not comfortable using Excel. He has plodded along doing things the same way his predecessor did, watching the workbook expand along the way.
On my end, I am a little more comfortable using Excel but can only donate time as it becomes available, which isn't often enough.
I will send a scrubbed and reduced version of the workbook to each of you via e-mail. Please let me know if you don't receive it.
Thank you all again,
-Ron A.
- Ron,
This is a very typical situation. Can't count the number of times I've helped with inherited workbooks. Also, I didn't see a file.
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
