Copying a block and incrementing a row by 1.

[ken800]

I can't install the tool to upload a sheet on my work PC so an image will have to suffice. Here's the jist: I have data coming from multiple sheets that I vlookup and format into a summary sheet. It is always the same number of rows like the below example. I want to be able to copy A3:C7 and paste it in A8. A4 is simply ='sheet2'!E4 and then copied down. I want to change the formula in A8 to when I copy the block I don't get ='Sheet 2'!E8 but instead increment it just one so the second block of picks up the NEXT number in the list -- i.e. 'Sheet 2'!E5. Copy the block again and pick up E6 and so on... I've spent quite a bit of time using offset, indirect, and other methods but haven't figured it out.

 

[awoohaw]

If you have lots of calculations in the workbook using the INDIRECT function will slow the workbook down.
As far as users of the workbook. the Power Query functionality is nearly invisible, unless they play with the data and connections group in the ribbon.
It will take you an hour or two to learn it.

But, you have a solution that works. which is good. best wishes.

This means that the users will have an easier chance to mess up the workbook's INDIRECT functions.