excel, vba and construction of a formula that updates properly
 

i am constructing my inventory controls using excel and vba
i have 14 tables that contain inventory data for each warehouse (2
warehouses altogether)
the first table is YTD inventory information (ie the starting inventory
level, total product sold, total product sent as samples, and ending
inventory level (ie current inventory).
the second table is monthly inventory information (ie the start inventory
level for the month, total sold for the month, total samples for the month,
and ending level for the month

tables 3-14 are the inventory levels for each month.

table 2 uses a validation box to choose the month that the inventory level
is to be displayed.
once the month is chosen, formulas are used to find the chosen month among
tables 3-14, and select the corresponding value and put it into table 2

an example of table 2

K L M N O P
Q R
2 March
3 Code Lot# St_Case Dt_Case Sd_Case Td_Case Se_Case Eg_Case
4 FC112 54733 10 0 0 0 0
10
5 FC116 57762 5 0 0 0 0
5
6 Totals 15 0 0 0
0 15

8 March
9 Code Lot# St_Case Dt_Case Sd_Case Td_Case Se_Case Eg_Case
10 FC112 54733 1 0 0 0 0
1
11 FC116 57762 5 0 0 0 0
5
12 Totals 6 0 0 0
0 6

the bottom table is the table in question and it gets its data from an
identically formed table, where the corresponding value of K8 starts in T8
my code for the top warehouse works perfectly.

my code for the bottom warehouse is what i am having trouble with.

when i add a new product line, lines (rows) are inserted and the formulas
are added for the new product line.
i am using the following vb code to set the formula of the cell.

<BEGIN VB CODE
loopRange.Offset(0, 11).FormulaR1C1 = "=offset(indirect(address(" & 7 +
numpr & ",19+match(r[" & numpr - 2 & "]c11,r[" & numpr - 2 & "]c20:r[" &
numpr - 2 & "]c108,0),4))," & 2 + numpr & ",2)"
<END VB CODE

(numpr is the number of product codes already in the inventory)
when a new product line is added, 2 rows are inserted. one for each of the
warehouses - and in alphabetical order (ie if FC114 would be the next product
line then the row would be inserted between FC112 and FC116 - row 5 would
contain FC114 as would row 12)

my problem is in the & 7 + numpr & part of the above code - it works fine
until a row is inserted and then it referrs to an incorrect cell since 7 +
numpr does not update on the lines above.

is there a way to make that part of this formula relative so that when a row
is inserted it updates correctly? if need be i can upload my project to a
website and insert a breakpoint at the line in question.

is there a better way to construct my formula so that it does update
correctly when a row is inserted?

or is my only option to re-do the formulas after the product line has been
added (there will be ( 6 x the final number of products - 6) formulas to
update)

thanks!

J