Problem to calculate differences in cell values when columns change.

Dear all,

I have a problem where I have several columns from C to N with numerous number data going down the rows. I have then a column O call 'Diff' which calculates the difference in value between two cells.

Eg. Cell F4 value is 4, cell G4 value is 10. Cell O4 is then G4 minus F4 = 6 (i.e. the difference between the values in F and G).

I have then a VBA code which hides and shows columns from C to N depending on a value in A3. Accordingly, as a result of this code, there are only ever two columns shown to the left of Column O (e.g. F and G, or M and N, or D and E depending on which columns and hidden and the remaining columns from C to N are hidden). Columns A and B always show. Also, the two columns adjacent to the left of column O are always consecutive, eg. C/D, never C/M.

What I want to achieve is for the Column O to calculate the difference in the 'two columns adjacent to the left of it', no matter which columns show after the VBA code is applied following changes to the value in A3.

Eg:
C4=4, D4=5, O4=1
when columns C and D show
OR
L9=10, M9=14, O9=4
when columns L and M show.

How can this be achieved (i.e. can the difference between the value in the cells in the two adjacent columns to the left of column O be calculated no matter what columns these are)? I imagine this would probably not be a simple formula which could be used if we knew which were the columns which would be next to column O to the left.

Best regards, Chris

"Eg:
C4=4, D4=5, O4=1
when columns C and D show"

this means all the columns to the left of column O except C and D are hidden.

see attachment sample data "CHRIS.XLS"

you can try this macro ( perhaps a formula solution may be available)

Please Login or Register  to view this content.

I suppose this will work what ever be the unhidden columns so long as they are left to column O and in row no. 4
BUT REMEMBER COLUMN O AND THE TWO COLUMN REMAIN UNHIDDEN AND ALL OTHER COLUMN TO THE LEFT OF o ARE HIDDEN. EVERY TIME YOU HAVE TO DO THIS EXERCISE TO CHECK UNLESS THE CODE YOU HAVE ALREADY HAVE DOES IT.

hi chrisjc19, option to make it. Leave two visible columns only and press the button.

Please Login or Register  to view this content.