Selecting totals from UNHIDDEN columns Only

[DGB]

Hi,

I have a workbook that has 2 sheets; "Quote Summary" and "ECS". The "ECS" sheet contains columns "C" through "L" each of which has a total on row "27". At any time, one or several (but never All) of these columns may be unused and therefore hidden.

I would like to know if it is possible to sum the total of only those columns that are NOT hidden and display that value in cel "C15" of the "Quote_Summary" sheet.

Thanks in advance for your help

 

[PCL]

Perhaps next UDF could help.

Option Explicit
Function SUM_NOT_HIDDEN() As Double
Dim I As Integer
    SUM_NOT_HIDDEN = 0
    For I = 3 To 12
        If (Sheets("ECS").Cells(27, I).EntireColumn.Hidden = False) Then SUM_NOT_HIDDEN = SUM_NOT_HIDDEN + Sheets("ECS").Cells(27, I)
    Next I
End Function

and to refresh the formula without to do it manually.
Install next code in Quote Summary sheet code

Option Explicit
Private Sub Worksheet_Activate()
Dim MyFORMULA As String
    MyFORMULA = Range("C15").FormulaR1C1
    Range("C15").FormulaR1C1 = MyFORMULA
End Sub

 

[DGB]

Thank you PCL,

As a relative newbe, where is it I should put the UDF code? Would I put that on the code sheet of the ECS page?

 

[PCL]

The UDF code must be placed in a module.
An easy way to do (not very smart) can be to record a macro: Tool>macro>Record New Macro
and close it immediately.
Then Tool>Macro>Edit
and replace the macro code by the UDF one

 

[DGB]

Thanks,

I have placed the UDF code in a module by replacing a macro code as you suggested and I have added the refresh code on the code sheet of the Summary page.

When I look at the field c15 on the summary page, i get no value. What is it that triggers the UDF code?

I think what i am missing is what triggers these 2 pieces of code. Is it supposed to just always be working so that Summary C15 will display a total of all columns and change as coumns are hidden?

 

[PCL]

OOOOOH
I forgot to mention
In sheet Quote Summary
C15 = SUM_NOT_HIDDEN()

 

[DGB]

Ok...Now we are getting somewhere.

I set the Summary C15 cell =SUM_NOT_HIDDEN()
but now I get an error message that reads "A value used in the formula is of the wrong data type"

Any ideas?

 

[PCL]

The UDF is summing data from sheet ECS column 3 to 12 in row 27.
It means it must exist only numbers in these cells.
In sheet Quote Summary cell C15 must be format as general or number.
Is it what you have???
Note: We could have a range mentioned in the UDF argument if you want to sum somewhere else than C27:L27

 

[DGB]

PCL,

I found it, I had a typo. Please excuse my last reply.

The value 332 now shows in the Summary C15 cell. This is the correct sum for all of the columns when none are hidden.

I do have another 2 issues.

1) When I hide a column on the ECS sheet, the value on Summary 315 does not change accordingly.
2) When I try to use the macro which appears just above the function you gave me, I get an error stating that "only comments can appear beneath End Sub because the "Option Explicit" appares above the separation line. How do I get that beneath the line?

 

[DGB]

PCL, You are so patient and I thank you for that.

I think I answered one of my questions. I added a module and pasted your code there instead of where it was and now the Hide macro I was using no longer gets the error.

I still do however have the issue of the value not changing on Summary C15 when I hide a column.

Also, would there be a way to alter the C15 part so that if the value is = to 332, it displays a "0" and only displays the sum of all unhidden columns from the summary page if the total is not 332?

Maybe I am asking too much of Excel?