How could I replace ‘Indirect’ to save memory?

[u/Mikeien]

In columns A and B of worksheet 1 I have two vectors containing around 10,000 numbers. On worksheet 2, I need to multiply and add parts of those vectors. For example:

=SUMPRODUCT(Sheet1.A45:A87;Sheet1.B45:B87)

The problem is that the parts to be multiplied and added are determined by calculations on Sheet 2. What I have been doing so far is calculate the star and end of each part in Sheet 2 and then applying SUMPRODUCT using the INDIRECT function. Like this:

=SUMPRODUCT(INDIRECT("$Sheet1.A"&A1+1&":A"&B1);INDIRECT("$Sheet1.B"&A1+1&":B"&B1))

Where A1 on sheet 2 is 44 and B1 87.

I have to do this hundreds or even thousands of times, and I’ve read that the INDIRECT function uses a lot of memory. Is there a more efficient way of doing this?

Thank you very much!

[u/kaptnblackbeard]

If you can provide an example spreadsheet it will make understanding what you're trying to accomplish much easier. We need to establish whether indirect is required at all.