On my worksheet "User List" I have a header row that references a list on the sheet "User Fields". What I want this to do is capture the list exactly as it is in User Fields. I set up a series of simple formulas to do this at first -- each cell in the header row is a 1:1 call to the list on the User Fields (e.g. A11 on User List = 'User Fields'!$B16).


This works well at first, but User Fields is designed to be updated by our clients. If they don't need a field, they delete a row with the field they don't need. This results in a #REF now in the header row. Initially I set up the following code to handle this:

Please Login or Register  to view this content.
But deleting the column also deleted important instructional information and status tracking that exist in rows 1-10. I found the Indirect function and thought this might be a solution to make the Header more responsive. But if the header shifts and the underlying data does not, then the data is inaccurate (for example, the list of Locations now looks like the list of Business Units).

I set up a series of cascading conditions that I think will work for if fields are deleted from the previous page (the conditions aren't important to this discussion and are based on how the status section at the top of the page is configured):

Please Login or Register  to view this content.
But none of this helps if users add rows on the previous page. Adding a row still causes the references to shift instead of the cells themselves.

Does anyone know of a more efficient, streamlined way of making a header row updated dynamically based on the list on the previous page, regardless of whether cells are being added or deleted? (Note that the data in the column below the header needs to stay with the header if it shifts).