VBA code to automatically change column reference in formula

Hi everyone

I am pretty new to VBA / Macros and would be so grateful if someone could help me solving my problem.

I have a big excelfile with different tabs that are linked to SAP. So every month (after conducting a refresh) there is a new column showing up in these tabs with the data of the latest month.
As I also maintain an overview tab that refers and aggregates the data of the current month through formulas, I had to change the column reference in these formulas every month.

i.e. one of the formulas may look like this:

=IF(ISNA(INDEX(BFD_Detail_PE!$P:$P,MATCH(1,(BFD_Detail_PE!$B:$B=B27)*(BFD_Detail_PE!$C:$C=$D$12),0))*(-1)),0,INDEX(BFD_Detail_PE!$P:$P,MATCH(1,(BFD_Detail_PE!$B:$B=B27)*(BFD_Detail_PE!$C:$C=$D$12),0))*(-1))

=> column reference for current month is column: BFD_Detail_PE!$P:$P , which needs to be changed to BFD_Detail_PE!$Q:$Q for the next month.

Is there a vba code that would simplify the step of changing the column reference?


Thank you very much in advance for any help! very much appreciated.

Best
Lucia

anyone ?

Hi everyone

This is a quite urgent issue of mine... Therefore I would be very grateful if someone could help me...?