Formula to pick up a cell in the latest unhid column

[gautamacharya]

Hi All,
I am trying to come up with a formula that can pick up the value in a cell for the latest unhid column. Basically I have months in columns and corresponding values in row 32. I have hid the future months and only historic to the current month is unhid. I want the formula to pick up the value in Row32 of the current month (unhid) in the sheet. In some cases, I may want hide current month and unhide only last year's data. Hence, the current date function wont work.

Greatly appreciate your help.
Thanks,
Gautam

[NumbersGeek]

See if this helps:

http://www.extendoffice.com/document...ered-rows.html

[gautamacharya]

Thanks NumbersGeek,
The VB code does not seem to work. It says "invalid name". Besides, my spreadsheet has many worksheets. Having to avoid a VB code. Can it be done with a formula?

[NumbersGeek]

Are you able to attach your file?

[gautamacharya]

NumbersGeek,
Thanks. I take it back i am able to insert the module and now it recognizes the formual SumVisible but does not pick up the latest unhidden cell. Please see attached file. The value i want in cell in EF14 is the cumulative sum of Row 14 or the value in cell 15 (both are the same). Right now as the file is, it has to pick up the value in EE15. Lets say, if you hide columns EE&ED, it has to pick up value cell EC15.
As the formula stands, If you hide columns, the SumVisible value does not change.

Thanks
GautamBook1.xlsm