Incrementing Column references in a formula

[Randy Hunt]

I am trying to find a way to create a macro that
increments the column reference in a formula. For example
to calculate a simple percentage such as =H48/$F$8*100. As
I add new data for the next week under column "G" (Each
column is a diff. week of data) I want all of my linked
formulas to now use $G$8 instead of "F" in order to save
someone having to modify the formulas on several sheets.

Thanks.

Randy

 

[J.E. McGimpsey]

Instead of a macro, consider using INDEX().

For example, if A8:F8 have no blanks, and G8:IV8 are empty:

=INDEX(8:8,COUNTA(8:8))

will return the value in F8.

When you add a value to G8, the INDEX() function will then return
the value in G8. You can adjust this if you have, say, a blank in
B8, to read

=INDEX(8:8, COUNTA(8:8)+1)

or with no blanks but a sum in column M:

=INDEX(8:8, COUNTA(8:8)-1)

For your example, you could use:

=H48/INDEX(8:8,COUNTA(8:8))*100