Columns of Data move position - how to get formula to adjust?

[andrewc]

Hi,

I receive a daily worksheet in which the column order may change from time to time. As I have formulae linked to specific columns (e.g. “Sales” is currently in column F) I would like to make them dynamic so that if, for example, the Sales data moves from column F to column Z, my formulae will adjust to consider a new range Z2:Z1000, rather than F2:F1000.

I thought of using the MATCH (to find the position of the “Sales” column) and ADDRESS functions together but without luck.

Can someone please suggest a solution?

Thanks!

[Fotis1991]

Which formula are you using?

[andrewc]

A few, like SUM and AVERAGE.

Thanks

[Fotis1991]

This does not helps us.

Can you pls upload a small sample workbook?

[andrewc]

Sorry, here's the sample.

Thanks

[Pepe Le Mokko]

Does the SUM have to be in the same column as the data ?

[andrewc]

No, they can be anywhere

Thanks

[Fotis1991]

1 way could be this.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[andrewc]

Thanks very much!

[Fotis1991]

You are welcome and thanks for the feedback & rep.