How To Locate Named Range Elsewhere And They Still Work?

[abrogard]

If I put a named range like a1:a10 and call it ‘cost’
Then put a named range in the same rows like for instance d1:d10 and call it ‘quantity’

I can then have a third column - say g1:g10 in each cell of which I can put the formula ‘=cost*quantity’

And each cell will correctly display the result of the calc, row by row. i.e. a1 * d1, a2 d2, a3d3 and so on.

But if those three columns are not side by side it doesn’t work. Move any one of them outside the range of rows used by the other two and it won’t work.

Is there a way I can make it work with the ranges in arbitrary places on the sheet?

Edit:

I am thinking I need something like Index(quantity,row,1) to access the range ‘quantity’.

So that I would have something like ’ = cost * (index(quantity,row,1)) ’

I guess I could put ‘1’ in for the the first row - it’ll index the array which is the range and call the first row ‘1’ I suppose rather than use anything got from the actual row designation?

So that would an okay formula for the first row of ‘cost’ and I could do the ‘slide down’ copy thing and have the formula copy down that column, great.

Except that it doesn’t increment the ‘1’ for the row, does it?

How best to get it increment that value as I copy it down? I need some big convoluted thing or there’s some simple way?

Further Edit:

I just tried @anon73440385’s Ctrl-Shift-Enter solution and it is perfect. LIterally perfect as best I can see.

Perhaps there’s some drawbacks with it inasmuch as it is not a copied formula existing in each row by itself but is a whole matrix multiplication the results of which just happen to occupy the rows I’m interested in.

I don’t know what difference that might make in any future operations on the sheet but I’d say for my purposes it is a total solution, all that I asked for.

I’ll go about marking his answer as such and thanks very much.

I retain an interest though in that last little question I raised - how to increment the rows when copying down an 'index() formula.

And I will investigate the offset() thing, too, thanks all for the help.

[mariosv]

I think it is because you are assigning relative addresses to the named ranges, if you want e.g. keep always the column but not the row use $a1:$a10, they are used similar to what happens copying a formula between cells, so using properly absolute addresses matters.

[anon73440385]

Hello,

from my perspective the only way to make =cost*quantity working anywhere in your sheet is to use array version of that formula:

{=cost*quantity} - Enter as usual =cost*quantity} but finalize using CTRL+SHIFT+ENTER instead of just ENTER.

[mikekaganski]

The formula that you use works by intersection. How else could it know which cells to use from each range? The ranges are not guaranteed to have equal size: e.g., your cost could be 10 elements, and your quantity could be 11 elements (LibreOffice can’t guarantee it won’t ever happen as a result of range modifications, or row deletions, or something like that); what to do then? The evaluator looks for the cells in the ranges that are either in the same row, or in the same column, as the formula cell; and if it finds one such cell in the range, it knows what to use; otherwise, there’s no intersection. You should use OFFSET for that; or you may use @anon73440385’s advise.