drag formula to the right

[Dedeke]

Hi,

I am having problems with dragging this formula to the right so it changes the correct way.
I am using exel 2016 dutch version.

In cel F26 i have this formula:=ALS($V$15="Werk";Urenregister!R130;ALS($V$15="Verlof";Urenregister!T130;ALS($V$15="EW";Urenregister!U130;"")))
So depending on the Value from $V$ i get the correct value from de sheet"Urenregister"
Now i would like to drag this formula to the right (horizontaly) so it would change like this.

For cell G26= =ALS($V$15="Werk";Urenregister!R131;ALS($V$15="Verlof";Urenregister!T131;ALS($V$15="EW";Urenregister!U131;"")))
For cell H6= =ALS($V$15="Werk";Urenregister!R132;ALS($V$15="Verlof";Urenregister!T132;ALS($V$15="EW";Urenregister!U132;"")))

Is this possible, if yes How pleas

 

[JamesCanale]

Here is a much simplified version using the offset - I think you should be able to apply it to your problem to allow a nice dragging.

MrExcelPlayground20.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q	R
26	Jamie	Fred	Harry	Mary
27
28
29
30
31													Jamie
32													Fred
33													Harry
34													Mary
Sheet26

Cell Formulas
Range	Formula
F26:I26	F26	=OFFSET($R$31,COLUMN(F26)-COLUMN($F$26),0)

 

[Dedeke]

@ JamesCanale

i did some efforts using your hint , and translating to my dutch version (wich i did not mensionend) and came up ith this formule:

=ALS($V$15="Werk"; VERSCHUIVING(Urenregister!$R$9; KOLOM(F20)-KOLOM($F$20); 0); ALS($V$15="Verlof"; VERSCHUIVING(Urenregister!$T$9; KOLOM(F20)-KOLOM($F$20); 0); ALS($V$15="EW"; VERSCHUIVING(Urenregister!$U$9; KOLOM(F20)-KOLOM($F$9); 0); "")))

This seems to be working for my needs. It is also possible that this is a good effort and i could be even more simple or shorter a this one