Indirect function: dragging across

[Laurensvl]

Hi,

I am using an indirect function, but I want to drag the function down and across.
Initial formula:
=IF(Verlof!K18="VG";0;IF(Verlof!K18="Z";0;IF(Verlof!K18="HVG";0,5;IF(Verlof!K18="W";0;IF($D135="";"";IF(Verlof!K18="THD";0,5;IF(Verlof!K18="T";0;1)))))))

To drag it down, I changed it into the following formula:

=IF(INDIRECT("'Verlof'!K"&ROW(Verlof!K18))="VG";0;IF(INDIRECT("'Verlof'!K"&ROW(Verlof!K18))="Z";0;IF(INDIRECT("'Verlof'!K"&ROW(Verlof!K18))="HVG";0,5;IF(INDIRECT("'Verlof'!K"&ROW(Verlof!K18))="W";0;IF($D136="";"";IF(INDIRECT("'Verlof'!K"&ROW(Verlof!K18))="THD";0,5;IF(INDIRECT("'Verlof'!K"&ROW(Verlof!K18))="T";0;1)))))))

But when I want to drag it across, the "K" in:'Verlof'!K, does not change to "L". the reason I am using indirect is to stick the formulas to the same cell when inserting or deleting rows.
Can anybody help me?

Highly appreciated.

[Pete_UK]

If you listed the values VG, Z, HVG, W, THD, and T in column A of a separate sheet (e.g. Sheet2), with the corresponding values in column B, then you could change your formula to:

=IF($D135="";"";VLOOKUP(Verlof!K18;Sheet2!$A:$B;2;0))

which is a lot shorter and should not depend on the insertion/deletion of rows/columns after it is copied across and down.

Hope this helps.

Pete