worksheet change event to populate formula for number of columns

[nigelog]

Hi all,

in the attached workbook I am trying to populate a range with data to record depreciation over time. I would like to automate the population by an change event in column "k" (k will dictate the start column number). Either by a formula in all cells in range that reacts when "k" >"" or a macro that copies the formula to a number of columns dictated by column h and a formula between "h" and "e".

Basically column "k" will be the start column number, the number of columns populated will be dictated by the "h" column(duration of write down) and the value will be decreased accordingly.

Not easy explain but straightforward enough. I tried to do a hlookup to try to identify the start column and use an offset to populate the number of columns but I couldn't get a handle on hlookup as never used before. Any pointers appreciated.

Rgds

[davesexcel]

Your Hlookup() is a bit out.

=IFERROR(HLOOKUP(K6,$L$1:$GJ$3,3,FALSE),"x")

[nigelog]

Hi Dave, that was a first attempt to try to identify the correct column
this returns the value of the required first column in row1

=HLOOKUP(K6,$L$1:$GJ$1,1,FALSE)

but I would like to copy a formula

=$E6-($J6*(V$1-$K6))

from that column up to the number of columns dictated by

(LEFT($H6,2)

which would be a offset to the row in question, in this instance "6"

columns j and k should not be necessary as the start month should be dictated by the date in column f and a hlookup of the dates in row 4,
with

=HLOOKUP(TEXT(F6,"mmm-yyyy"),$L$4:$GJ$4,3,FALSE)

but i thought it easier to start with the simple extracted value and put it all into one later

[nigelog]

Does hlookup only return a value though or can it be used to identify a start point??

this works without the helper column but has a problem passing the change of year

=$E6-($E6/LEFT($H6,2)*(S$1-TEXT($F6,"mm")))

so thats really what I need in the columns from start date to number of columns