I pull data from sql tables into Access, then filter, calculate and manipulate until I have the data tables I need for a report in excel. One table is a project budget with all of the cost codes and relevant data from the financial system for the project. I have another table for, let's say material procurement. This table requires all of the cost codes from the project budget table that are for material procurement. I have an array formula pulling the cost codes from the project budget table using criteria, so none of the codes are left out. When new codes are entered into the financial system they automatically disperse to all my other tables using the cost code, upon a refresh. This is a tremendous help to make sure new cost codes are not missed in the report.
My issue is that the tables require manual data entry in some cases, in a particular column. When a new cost code appears from entry into the financial system, it shifts all the cost codes below it, down one, thus leaving me with manual data in the wrong cell location and calculating with the wrong cost code. Is there a way to keep manually entered data with the original cost code? I realize I can have a separate table and retrieve the information I need using index/match or vlookup, but this would require manual entry of the cost codes in the second table, defeating the purpose of the original intent.
Any help for a solution would be appreciated.
My issue is that the tables require manual data entry in some cases, in a particular column. When a new cost code appears from entry into the financial system, it shifts all the cost codes below it, down one, thus leaving me with manual data in the wrong cell location and calculating with the wrong cost code. Is there a way to keep manually entered data with the original cost code? I realize I can have a separate table and retrieve the information I need using index/match or vlookup, but this would require manual entry of the cost codes in the second table, defeating the purpose of the original intent.
Any help for a solution would be appreciated.