Formula to reference a specific variable cell

Hi all

Not been around for some time now but have to do my annual update for this workbook and implement some new changes.

In the attached example workbook, I have created some formulas which reference a particular cell for each client e.g.

Please Login or Register  to view this content.

This references a week number in H12 for this client. However, as the number of rows for each client varies, the position of H12 or subsequent cell in Column H varies all of the time.

So far, I have not found a way of replacing this part of the formula with a variable reference in the formula; which means manually having to change hundreds of existing formulas.

It is quite possible that the solution to this problem may be to use VBA but I thought it best to post here first.

TIA ...spellbound

From what I can see you have over 20 cells per row referencing this value and on that basis I would advise your store the "week value" in a "helper" column for each row as opposed to calculating it 20 times over on each row... the performance gains of this approach make this a no-brainer IMO.

For ex... if you enter the following:

DJ5: =IF(N($B5),INDEX($H5:$H$100,MATCH("Arrears Stage",$C5:$C$100,0),1),$DJ4)

and copy this down you should find that each row returns the appropriate Week value per column H.

the above INDEX / MATCH will only ever execute once per group (where ID (B) is numeric) -- so the amount of calcs being done is minimal.

Each reference to Hx in your formulae (where x is 12 etc) should obviously be changed to point at the value in DJ on the same row as the formulae itself.