Hi There,
Every week I have to update 6 worksheets with the previous weeks results (Have attached copy of 1, others are the same format) then I adjust the formula in columns W to Z (row 4) and drag down.
I thought I would try HLOOKUP formulas to do this automatically each week on all 6 worksheets when I change the week numbers in cells V2 to Z2, however, it works fine on the first row (row 3) which relates to site 1, but when you drag down the 'Row_Index_Num' stays as '2'.
How can I change the formula so it pick up the correct row for each site?
Any help would be greatly appreciated
Regards
Paul
Hi Paul,
A simple solution is to use ROW formula to get the row number.
Your formula in cell W3 is currently:
=HLOOKUP($W$2,$B$2:$T$251,2,FALSE)-HLOOKUP($V$2,$B$2:$T$251,2,FALSE)
My proposal is that you replace the 2 with ROW()-1 as row reference. With this you get 2 as row reference in cell W3, you get 3 in cell W4 and so on.
=HLOOKUP($W$2,$B$2:$T$251,ROW()-1,FALSE)-HLOOKUP($V$2,$B$2:$T$251,ROW()-1,FALSE)
I would also remove the first $ symbol for the cell lookup reference, so I can copy the formula horizontally also.
=HLOOKUP(W$2,$B$2:$T$251,ROW()-1,FALSE)-HLOOKUP(V$2,$B$2:$T$251,ROW()-1,FALSE)
Br,
Anders
Hi Anders,
Thanks so much for your help, really appreciated
Regards
Paul
