I created a macro setup that takes raw data from a remittance (1st tab), pastes it in a table (2nd tab), and the 3rd tab uses formulas to reference the table name & headers to do a reconciliation, partially manual (which is why I don't use Pivot). It works great, except the main issue I have is that the remittance data is inconsistent in which column the main reference (Invoice #) comes from. In one remit, the column would be Col U, in another it would be Col T. Therefore, I need my column reference to be flexible based on the header name, which is consistent.
in place of the Payer Account #, which the column reference that moves, Excel won't let me leave the formula bar since I can't figure out how to insert a formula for a column reference in a table.
The 'Payer Account #' started as 'Invoice #', but when I inserted a different remit that didn't have the same column setup (there was one less), my column shifted to 'Payer Account #', and now none of the other references in the table work. The first image is the current result.
The second image is the correct result when the 'Payer Account #' reference is 'Invoice #' like it should be.
Does anyone know how to either merge the first formula I pasted (to find the column header) with the second formula to make the search dynamic, or lock the header name so I'm still referencing the 'Invoice #' column regardless of which column letter it's in?