Fix Column Reference? Changes with External Data Refresh

[Scotster]

I have a sheet that pulls data in from a SAP tool. It's updated each week, column count is always the same but the column heading changes by rolling on a week. When this happens the formula above the table moves reference even though I'm not using the header name.

	4 (formula to calculate AP from below)	4	4	4
Part Number	15.2024	16.2024	17.2024	18.2024
Part1	10	20	30	10
Part2	0	0	5	10
Part3	5	15	0	5

The formula that I'm using is as below:

=APno(VALUE(LEFT($F$8,SEARCH(".",$F$8)-1)),VALUE(RIGHT($F$8,LEN($F$8)-SEARCH(".",$F$8))))

APNo is a function that I've created that takes the referenced week number (15) and the year (2024) and calculates which AP it is based on my companies calendar layout. Just for rerefence.

The issue I've got is that if I refresh the data, the file that I'm referenced has rolled on the week/year references. I wouldn't think this would impact the formula, but for whatever reason it does. The column count that I'm pulling is the same, there are no changes other than to the heading name.

I expected

	4	4	4	5
Part Number	16.2024	17.2024	18.2024	19.2024
Part1	20	30	10	5
Part2	0	5	10	10
Part3	15	0	5	5

But instead I get the first AP reference as "N/A" as it's attempting to formulate as below:

=APno(VALUE(LEFT($E$8,SEARCH(".",$E$8)-1)),VALUE(RIGHT($E$8,LEN($E$8)-SEARCH(".",$E$8))))

Why is the column reference being changed to "E"? Is there any way to have it remain "F" without going into column() references in the formula (the only workaround I've thought of so far)?

 

[Scotster]

Just as an addon, this is what I've found to work as a solution but it just doesn't sit right that I need to do this. Is this the only way?

=APno(VALUE(LEFT(INDIRECT(ADDRESS(8,COLUMN())),SEARCH(".",INDIRECT(ADDRESS(8,COLUMN())))-1)),VALUE(RIGHT(INDIRECT(ADDRESS(8,COLUMN())),LEN(INDIRECT(ADDRESS(8,COLUMN())))-SEARCH(".",INDIRECT(ADDRESS(8,COLUMN()))))))