Hi
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.
I can use the formula:
=INDEX(RawData[#Headers],,MIN(IF(RawData=Reconciliation!A7,COLUMN(RawData[#All]))))
to Index the column name I need. A7 is the invoice # that acts as the anchor to lookup. When I plug that into my main formula in Cell B6:
=IFERROR(INDEX(RawData,MATCH($A6,RawData[[Payer Account '#]:[Payer Account '#]],0),MATCH(B$5,RawData[#Headers],0)),"")
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?
Thanks in advance!