Actually, what I was thinking of required everything referenced to be in a column.
That’s the game plan for now.
I’ve never used index and match, indirect, or offset and don’t know what they are. I tried to use vlookup a few years ago. In that case, I had it looking for an account number in a new file and an old file. If the account number found a match, it was supposed to take the name and address information from the old file and use it in the new file. (I got help here, BTW.) For some reason it would run forever, even though by all accounts the formula was formatted correctly. In one case I quit Excel after it had been running eight or 12 hours on a few-thousand-record file. It happened on my Mac, and also the PC at work. I should experiment with it some more, but I never seem to have the time; and after the previous failure I’m not convinced I can use it.
The source files don’t change extremely often, but they do change. Unless there’s a way to make Excel keep up with the changes, I think I’ll just copy and paste-values in the target spreadsheet and type dates in manually after typing them into the source spreadsheets.
Sorry to here that you’re back where you started.
In my Post above (#8) I warned you about unexpected consequences when working with Indirect statements.
Had I known you were sorting source and result areas I would have added the same caveat about sorting.
I agree with ** Darth Panda** about sorting and formulas. They don’t mix well at all.
I write some excel apps for a guy who loves to sort things so I usually use VBA to set up sorting areas that have no formulas via copy/paste-values methods. We have fast computers and lots of storage so fattening up a spreadsheet with these specialized sorting areas isn’t too bad.
You mentioned doing some experimentation with Excel and I think that’s a great idea. I haven’t had any formal training with Excel but I’ve become a more powerful user because I tested out its features.
I suggest that you play around with development features of Excel. It has a VBA recording program that will write code as you perform tasks.
If you want to know how to write the code for something like a copy and paste procedure, you can turn on the macro recorder, go to a cell, select it, select copy, go to another cell, select it, select paste, and then turn off the recorder.
You can then open a visual basic page and see what the code looks like.
I learned VBA by having Excel itself show me examples. I didn’t even know it had these features but all versions of Excel come with VBA macro capabilities ready to run.