Change Reference by dragging formula every 10th time

Hey guru,

I've been fiddling with Offset,Row,Indirect without success.

Here is the problem:

Say, I have a row from A1:D1 and another row from E1:H1.

The first column/row goes from 1 to 100 and the second column row goes from 1:10.

Now, I want to do matrix multiplication MMULT(A1:D1,transpose(E1:H1)). This works perfectly fine.

However, when dragging that formula down, I want to shift the second vector down only by 1 every 10th time for my A1:D1 vector.

In other words, this means for the first 10 filling, the column E1:H1 stays locked.

For 11th until 20th the column should be E2:H2. Basically, I want to shift that reference cell every 10th time.

Is there a way to do this without VBA?

Attached is a spreadsheet for your comfort.

Thanks alot folks!

I think you would need to use a combination of indirect and round, something like:
MMULT(A1:D1,transpose(INDIRECT("E" & ROUNDUP(ROW(A1)/10,0) & ":H" & ROUNDUP(ROW(A1)/10,0))).

Thanks guys, I appreciate your efforts!!!

I need to have some time to digest your solutions. It's kinda complicated to understand.

It's kinda sad that there is no easier way to do this simple logic in Excel.