I have a workbook made that has a 'Quantities' sheet that list any number of quantities along with 'contract quantity', 'current quantity', etc. There are also any number of other sheets which equal the number of items listed on the 'Quantities' sheet. These sheets are the individual items from the 'quantities' sheet, where I can go in and insert an individual quantity which is added up as more quantities in cell D28 and then the cell D28 is transfered to the correct location on the 'Quantities' sheet. I have been looking for a better formula to transfer this data. I originally had the following: ='1'!D28 where '1' means the sheet named '1' and !D28 is the cell i'm referencing in that sheet. But when I copied down the formula I had to individually change the '1' for each item. This takes a considerable amount of time when I get workbooks with 200+ sheets. Then after googling a little I wrote a macro which created a list of the sheet names and then used the following formula in place of '='1'!D28': =INDIRECT(A3&"!d28"). This equation worked fine for copying down the column but there is still another issue I can't figure out to fix. Cell D28 is normally always the cell used for the sum of quantities but occassionally there are enough quantities to enter that more rows must be inserted which changes the cell that the total is calculated in. When this happens the 'INDIRECT' formula does not update with this - it keeps using cell D28. How can I make the formula update?
Sorry for the long post - just wanted to get all my information out there.
Thanks, Mike.
Related:
How to auto change sheet reference in copied formulas
