Hi everyone. I'm having a bit of trouble with a formula from a workbook I'm trying to create. The workbook has:

  1. A worksheet containing employee names
  2. A worksheet for each employee that calculates the amount of inventory sold per product
  3. A worksheet that adds up the amount of inventory sold by all the employees per product

I would like for that last worksheet to reference the name list held in the first worksheet. For each product it will go to the name list, get an employee name, then go to that employee's worksheet and retrieve the inventory sold for that product. The formula I created to do that is as follows:

=IF(ISERROR(INDIRECT("'"&'Input Data'!$A$5&"'"&"!"&"I3")),0,INDIRECT("'"&'Input Data'!$A$5&"'"&"!"&"I3"))

I can add versions of the formula to add the inventory amounts for each employee, changing the name list cell reference in each one (highlighted in bold):

=IF(ISERROR(INDIRECT("'"&'Input Data'!$A$5&"'"&"!"&"I3")),0,INDIRECT("'"&'Input Data'!$A$5&"'"&"!"&"I3")) + IF(ISERROR(INDIRECT("'"&'Input Data'!$A$6&"'"&"!"&"I3")),0,INDIRECT("'"&'Input Data'!$A$6&"'"&"!"&"I3")) + ...

My problem however is highlighted in bold below:

=IF(ISERROR(INDIRECT("'"&'Input Data'!$A$5&"'"&"!"&"I3")),0,INDIRECT("'"&'Input Data'!$A$5&"'"&"!"&"I3"))

I would like to copy my formula to apply to all the products I'm tracking but that cell reference will not change automatically. That means for each product I would have to do it manually and there are many products. Is there a way to get that cell reference to update automatically?

Thank you for your help!