How do I automate formatting and appending a weekly list of names and numbers?

Given that you are simply merging the lists, and not doing anything with them... you'd be mad to try to use a formula to do that.

1. Enter 1 at A4. Double click the black rectangle (bottom right) of cell to fill down column.

2. Paste next dataset (minus header) into next available cell.

3. Enter 2 at fisrt empty column A cell.

4. repeat.

If you're only dong this once a week, it will take less time than it would to adjust teh ranges of formulae, etc. About 3 seconds per week's worth of data.

so, you really have to ask yourself... is it worth it?

It took me less than 20 seconds to do the lot...

The best approach in Excel ALWAYS is to generate one big boring flat table organised into columns and rows, with NO merged cells. If you do that... most tasks are as simple as falling off a log.

If you are manually pasting data in from somewhere else, it make more sense to assemble that big boring table at that point.

Sure, I can get a formula to do that for you... but honestly... it's not the way to go. If you want I CAN do it for you... but IMHO, it is not the way to go. it might be a different matter if the data were coming from a system that spits the numbers out on a different sheet each time... But since YOu are manually opening the email etc....

If I were you, I would continue to do it in the way that you currently do!!