# How To Locate Named Range Elsewhere And They Still Work?

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, a3*d3 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 @Opaque'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.