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.
