Forum Discussion
Extracting date
I am pretty comfortable with XLOOKUP and HLOOKUP. But, I do not know if (or how) the individual reports could be automated. My solution might simply be to manually create the individual reports since it only has to be done once per year.
Unfortunately, I cannot give a more detailed example because I am still waiting for input from others, always a slow process!
Again, then, if that "Master" sheet is representative, and all the columns are consistently organized, such that row xx always contains data element "Job Title", (etc) you should be able at the basic level to write an XLOOKUP formula that would fill each cell in your individual sheets and use column and row references off to the side as guides within those functions. Or maybe OFFSET would be the function. Key each formula with an absolute reference to the Account Number on the "Master" and let the columns and rows be absolute or relative as needed.
It's not been clear to me whether you were wanting those individual sheets printed or electronic. If the latter, you could just have 200 individual sheets and populate the index (account number) once and be done.
Or write a macro (I did this one time in a comparable situation a couple decades ago) that runs through the account numbers from top to bottom and prints each sheet before going to the next. I generally avoid macros and VBA, preferring the elegance of Excel's many formulas.