**Question description:**

*This user has given permission to use the problem statement for this blog.*

How to I use relative/abs/mixed cell reference if I want to drag down but I want to array to go one the right instead of down

Solved by V. U. in 21 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
26/04/2018 - 10:28

Hi, Thank you for using Got It Pro.
I’m happy to be able to help you today.

User
26/04/2018 - 10:29

Hello, i'm using index and match but I don't want to change to array everytime but simply just want to drag down

Excelchat Expert
26/04/2018 - 10:29

Sure

Excelchat Expert
26/04/2018 - 10:29

Do you know about relative and absolute references?

User
26/04/2018 - 10:29

Only with the most used one

User
26/04/2018 - 10:30

not when mixing and stuff

User
26/04/2018 - 10:30

only when I lock one cell and drag it down

Excelchat Expert
26/04/2018 - 10:30

okay let me explain quickly how it works

User
26/04/2018 - 10:30

Thanks

Excelchat Expert
26/04/2018 - 10:30

and then we address your specific issue

Excelchat Expert
26/04/2018 - 10:30

ok?

User
26/04/2018 - 10:30

ok

Excelchat Expert
26/04/2018 - 10:30

okay, any cell has a row number and a column letter

Excelchat Expert
26/04/2018 - 10:30

So A1

Excelchat Expert
26/04/2018 - 10:31

is the cell in the intersection of row 1 with column A

User
26/04/2018 - 10:31

Yes

Excelchat Expert
26/04/2018 - 10:31

ok when I want to lock in a reference I use the $

Excelchat Expert
26/04/2018 - 10:31

If i want to lock the column I insert it before the column letter

Excelchat Expert
26/04/2018 - 10:31

so $A1

Excelchat Expert
26/04/2018 - 10:31

means if I drag to the right A will not change

Excelchat Expert
26/04/2018 - 10:31

however if I drag down

Excelchat Expert
26/04/2018 - 10:32

the 1 will change

Excelchat Expert
26/04/2018 - 10:32

same thing with the 1

Excelchat Expert
26/04/2018 - 10:32

so if I insert the $ before the 1 (A$1)

Excelchat Expert
26/04/2018 - 10:32

this means if I drag down the 1 will not change

Excelchat Expert
26/04/2018 - 10:32

but if I drag right the A will change

User
26/04/2018 - 10:32

Ok

Excelchat Expert
26/04/2018 - 10:32

the final one is adding $ on both ($A$1)

User
26/04/2018 - 10:33

Yes that's the one I'm most familiar with

Excelchat Expert
26/04/2018 - 10:33

this means that no matter where I drag the cell remains the same

Excelchat Expert
26/04/2018 - 10:33

ok so let's try an exmaple

User
26/04/2018 - 10:33

Yes

Excelchat Expert
26/04/2018 - 10:34

okay so if I want to fill this table

Excelchat Expert
26/04/2018 - 10:34

by multiplying the number of units with the price

Excelchat Expert
26/04/2018 - 10:35

yes

Excelchat Expert
26/04/2018 - 10:35

but we need to change it up a bit

Excelchat Expert
26/04/2018 - 10:35

for A2

Excelchat Expert
26/04/2018 - 10:35

I want to make A a constant

Excelchat Expert
26/04/2018 - 10:35

so that when I drag right it does not move

User
26/04/2018 - 10:35

Yes that's when I easily get confused haha

Excelchat Expert
26/04/2018 - 10:35

now B1 I want the row only to be constant

User
26/04/2018 - 10:35

even though it seems easy to understand

Excelchat Expert
26/04/2018 - 10:36

no worries it may be confusing sometimes

Excelchat Expert
26/04/2018 - 10:36

check the formula now

Excelchat Expert
26/04/2018 - 10:36

I adjusted it

User
26/04/2018 - 10:36

2 secs

Excelchat Expert
26/04/2018 - 10:36

now drag right

Excelchat Expert
26/04/2018 - 10:36

sure take your time

User
26/04/2018 - 10:38

Ok

Excelchat Expert
26/04/2018 - 10:38

what happened in C2?

Excelchat Expert
26/04/2018 - 10:38

column A is constant which means it was not dragged to the right

Excelchat Expert
26/04/2018 - 10:39

the column B1 changed to C1 however

Excelchat Expert
26/04/2018 - 10:39

because B does not have a $ before it

Excelchat Expert
26/04/2018 - 10:39

makes sense?

User
26/04/2018 - 10:39

Yes :)

Excelchat Expert
26/04/2018 - 10:40

okay, is there a specific case you would like help on?

User
26/04/2018 - 10:40

Yes please

Excelchat Expert
26/04/2018 - 10:40

sure can you please copy the data here

User
26/04/2018 - 10:42

ok i don't know why it does work but my question is how I can use relative/abs cell reference with be B11:N14 array

User
26/04/2018 - 10:43

When I drag down I want it to take from the next column until the last column

User
26/04/2018 - 10:43

So I don't want it to count the whole array all the time, but -1 each time I drag down

Excelchat Expert
26/04/2018 - 10:43

-1 from the columns?

User
26/04/2018 - 10:44

Yes, like the first one i B11:N14, then C11:N14, D11:N14 until N:14:14

Excelchat Expert
26/04/2018 - 10:44

ok this is a bit tricky

User
26/04/2018 - 10:44

when I drag down

Excelchat Expert
26/04/2018 - 10:44

you can change the number 9 in the formula

Excelchat Expert
26/04/2018 - 10:44

to change dynamically

Excelchat Expert
26/04/2018 - 10:44

so 9

Excelchat Expert
26/04/2018 - 10:44

10

Excelchat Expert
26/04/2018 - 10:44

11

Excelchat Expert
26/04/2018 - 10:44

12

User
26/04/2018 - 10:45

So if I change to 9 and drag down it will change automatically?

Excelchat Expert
26/04/2018 - 10:45

no I used the rows formula to achieve this

Excelchat Expert
26/04/2018 - 10:46

check if the solution I provided retrieves the correct answer

Excelchat Expert
26/04/2018 - 10:46

and if so I'll explain what I did

User
26/04/2018 - 10:46

not quite, because I made a mistake

User
26/04/2018 - 10:46

I want it to find the number in each column for CMA

User
26/04/2018 - 10:47

so I have 13 rows and then I want 13 numbers from CMA

User
26/04/2018 - 10:47

and I just want to drag it down instead of typing in 13 formulas

Excelchat Expert
26/04/2018 - 10:47

like that?

User
26/04/2018 - 10:47

exastcly

User
26/04/2018 - 10:47

Exactly!

User
26/04/2018 - 10:48

Perfect

User
26/04/2018 - 10:48

Wiw

User
26/04/2018 - 10:48

w

User
26/04/2018 - 10:48

wow :)

Excelchat Expert
26/04/2018 - 10:48

Is there anything else I can help you with regarding this matter?

User
26/04/2018 - 10:49

No that's it. Thanks!

Excelchat Expert
26/04/2018 - 10:49

When you are done with the session please click on end session.
Have a great day ahead :)

User
26/04/2018 - 10:49

You too1

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*