A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Summing non hidden values in a range



 
 
Thread Tools Display Modes
  #31  
Old May 3rd, 2006, 01:19 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Summing non hidden values in a range

Try adding:

Application.volatile

to the top of the procedu

Function TOTAL_VISIBLE(rng As Range) As Long
application.volatile

.....


This tells excel to recalculate the function whenever excel recalculates.

If I recall correctly, some versions of excel won't recalc when columns are
hidden/unhidden--maybe all versions??? (Changing the columnwidth--not
hiding/showing--causes a recalc in xl2003, though.)

So for UDFs like these, you'll want to force a recalc before you trust the
results.

starguy wrote:

what should I do now...problem persists.

--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=537953


--

Dave Peterson
  #32  
Old May 3rd, 2006, 02:21 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Summing non hidden values in a range

Still need to force a recalc, at least in XP you do.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Dave Peterson" wrote in message
...
Try adding:

Application.volatile

to the top of the procedu

Function TOTAL_VISIBLE(rng As Range) As Long
application.volatile

....


This tells excel to recalculate the function whenever excel recalculates.

If I recall correctly, some versions of excel won't recalc when columns

are
hidden/unhidden--maybe all versions??? (Changing the columnwidth--not
hiding/showing--causes a recalc in xl2003, though.)

So for UDFs like these, you'll want to force a recalc before you trust the
results.



Biff wrote:

Did you enter a valid range? (eg: =total_visible(A1:A4) )


Yes. I got it to return a sum but it doesn't change when columns are

hidden
and I calculate.

I had changed the function name but didn't realize it was called later

in
the procedure. I changed it back.

Biff

"Ardus Petus" wrote in message
...
Works fine by me.

Did you enter a valid range? (eg: =total_visible(A1:A4) )

--
AP

"Biff" a écrit dans le message de news:
...
Ardus, I tried your UDF but I get a result of 0 all the time (with or
without hidden columns)

Biff

"Ardus Petus" wrote in message
...
I don't have XL 2003 either..

Sounds like you need an UDF.

Here is some code you can paste in a Module

'------
Function TOTAL_VISIBLE(rng As Range) As Long
Dim c As Range
For Each c In rng
With c
If Not .EntireColumn.Hidden Then
TOTAL_VISIBLE = TOTAL_VISIBLE + .Value
End If
End With
Next c
End Function
'---------

"starguy" a

écrit
dans le message de news:
...

what should I do. I need it... any body esle...


--
starguy


-----------------------------------------------------------------------

-
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=537953








--

Dave Peterson



  #33  
Old May 3rd, 2006, 02:40 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Summing non hidden values in a range

xl2003, too. But I don't recall if that ever changed from previous versions.

Bob Phillips wrote:

Still need to force a recalc, at least in XP you do.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Dave Peterson" wrote in message
...
Try adding:

Application.volatile

to the top of the procedu

Function TOTAL_VISIBLE(rng As Range) As Long
application.volatile

....


This tells excel to recalculate the function whenever excel recalculates.

If I recall correctly, some versions of excel won't recalc when columns

are
hidden/unhidden--maybe all versions??? (Changing the columnwidth--not
hiding/showing--causes a recalc in xl2003, though.)

So for UDFs like these, you'll want to force a recalc before you trust the
results.



Biff wrote:

Did you enter a valid range? (eg: =total_visible(A1:A4) )

Yes. I got it to return a sum but it doesn't change when columns are

hidden
and I calculate.

I had changed the function name but didn't realize it was called later

in
the procedure. I changed it back.

Biff

"Ardus Petus" wrote in message
...
Works fine by me.

Did you enter a valid range? (eg: =total_visible(A1:A4) )

--
AP

"Biff" a écrit dans le message de news:
...
Ardus, I tried your UDF but I get a result of 0 all the time (with or
without hidden columns)

Biff

"Ardus Petus" wrote in message
...
I don't have XL 2003 either..

Sounds like you need an UDF.

Here is some code you can paste in a Module

'------
Function TOTAL_VISIBLE(rng As Range) As Long
Dim c As Range
For Each c In rng
With c
If Not .EntireColumn.Hidden Then
TOTAL_VISIBLE = TOTAL_VISIBLE + .Value
End If
End With
Next c
End Function
'---------

"starguy" a

écrit
dans le message de news:
...

what should I do. I need it... any body esle...


--
starguy

-----------------------------------------------------------------------

-
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=537953








--

Dave Peterson


--

Dave Peterson
  #34  
Old May 3rd, 2006, 02:47 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Summing non hidden values in a range

Nope, no good in 2000 or 97.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Dave Peterson" wrote in message
...
xl2003, too. But I don't recall if that ever changed from previous

versions.

Bob Phillips wrote:

Still need to force a recalc, at least in XP you do.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Dave Peterson" wrote in message
...
Try adding:

Application.volatile

to the top of the procedu

Function TOTAL_VISIBLE(rng As Range) As Long
application.volatile

....


This tells excel to recalculate the function whenever excel

recalculates.

If I recall correctly, some versions of excel won't recalc when

columns
are
hidden/unhidden--maybe all versions??? (Changing the columnwidth--not
hiding/showing--causes a recalc in xl2003, though.)

So for UDFs like these, you'll want to force a recalc before you trust

the
results.



Biff wrote:

Did you enter a valid range? (eg: =total_visible(A1:A4) )

Yes. I got it to return a sum but it doesn't change when columns are

hidden
and I calculate.

I had changed the function name but didn't realize it was called

later
in
the procedure. I changed it back.

Biff

"Ardus Petus" wrote in message
...
Works fine by me.

Did you enter a valid range? (eg: =total_visible(A1:A4) )

--
AP

"Biff" a écrit dans le message de news:
...
Ardus, I tried your UDF but I get a result of 0 all the time

(with or
without hidden columns)

Biff

"Ardus Petus" wrote in message
...
I don't have XL 2003 either..

Sounds like you need an UDF.

Here is some code you can paste in a Module

'------
Function TOTAL_VISIBLE(rng As Range) As Long
Dim c As Range
For Each c In rng
With c
If Not .EntireColumn.Hidden Then
TOTAL_VISIBLE = TOTAL_VISIBLE + .Value
End If
End With
Next c
End Function
'---------

"starguy"

a
écrit
dans le message de news:
...

what should I do. I need it... any body esle...


--
starguy


-----------------------------------------------------------------------

-
starguy's Profile:

http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=537953








--

Dave Peterson


--

Dave Peterson



  #35  
Old May 3rd, 2006, 05:59 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Summing non hidden values in a range

Thanks for testing.

(I'll try to remember it.)

Bob Phillips wrote:

Nope, no good in 2000 or 97.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Dave Peterson" wrote in message
...
xl2003, too. But I don't recall if that ever changed from previous

versions.

Bob Phillips wrote:

Still need to force a recalc, at least in XP you do.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Dave Peterson" wrote in message
...
Try adding:

Application.volatile

to the top of the procedu

Function TOTAL_VISIBLE(rng As Range) As Long
application.volatile

....


This tells excel to recalculate the function whenever excel

recalculates.

If I recall correctly, some versions of excel won't recalc when

columns
are
hidden/unhidden--maybe all versions??? (Changing the columnwidth--not
hiding/showing--causes a recalc in xl2003, though.)

So for UDFs like these, you'll want to force a recalc before you trust

the
results.



Biff wrote:

Did you enter a valid range? (eg: =total_visible(A1:A4) )

Yes. I got it to return a sum but it doesn't change when columns are
hidden
and I calculate.

I had changed the function name but didn't realize it was called

later
in
the procedure. I changed it back.

Biff

"Ardus Petus" wrote in message
...
Works fine by me.

Did you enter a valid range? (eg: =total_visible(A1:A4) )

--
AP

"Biff" a écrit dans le message de news:
...
Ardus, I tried your UDF but I get a result of 0 all the time

(with or
without hidden columns)

Biff

"Ardus Petus" wrote in message
...
I don't have XL 2003 either..

Sounds like you need an UDF.

Here is some code you can paste in a Module

'------
Function TOTAL_VISIBLE(rng As Range) As Long
Dim c As Range
For Each c In rng
With c
If Not .EntireColumn.Hidden Then
TOTAL_VISIBLE = TOTAL_VISIBLE + .Value
End If
End With
Next c
End Function
'---------

"starguy"

a
écrit
dans le message de news:
...

what should I do. I need it... any body esle...


--
starguy


-----------------------------------------------------------------------
-
starguy's Profile:

http://www.excelforum.com/member.php...o&userid=32434
View this thread:
http://www.excelforum.com/showthread...hreadid=537953








--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #36  
Old May 3rd, 2006, 07:05 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Summing non hidden values in a range

CaptainQuattro may be on to something!

This could be done with GET.CELL but if a working UDF is available I'd go
with it.

Biff

"Bob Phillips" wrote in message
...
Can you explain that, it doesn't work for me?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"CaptainQuattro"
CaptainQuattro.277voy_1146635100.8876@excelforu m-nospam.com wrote in
message
news:CaptainQuattro.277voy_1146635100.8876@excelfo rum-nospam.com...

Here's a workaround that will work in any version of Excel:


In row 1 Column D enter =CELL("width",A2)

Copy to cells E1 through K1

in Cell L11 enter

=SUMIF(D1:K1,"0",D11:K11)


--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile:

http://www.excelforum.com/member.php...o&userid=32763
View this thread:
http://www.excelforum.com/showthread...hreadid=537953





  #37  
Old May 3rd, 2006, 11:37 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Summing non hidden values in a range

Bob,

I got this to work for me by changing the formula in D1 to:

=CELL("width",D2)

For Excel2k (at least) you need to force a recalc to get the totals in
column L to refresh.

I imagine that when you hide column X the value in X1 turns to zero, though
its hard to see to confirm. ;-)



"Bob Phillips" wrote:

Can you explain that, it doesn't work for me?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"CaptainQuattro"
CaptainQuattro.277voy_1146635100.8876@excelforu m-nospam.com wrote in
message news:CaptainQuattro.277voy_1146635100.8876@excelfo rum-nospam.com...

Here's a workaround that will work in any version of Excel:


In row 1 Column D enter =CELL("width",A2)

Copy to cells E1 through K1

in Cell L11 enter

=SUMIF(D1:K1,"0",D11:K11)


--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile:

http://www.excelforum.com/member.php...o&userid=32763
View this thread: http://www.excelforum.com/showthread...hreadid=537953




  #38  
Old May 4th, 2006, 08:44 AM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Summing non hidden values in a range

Exactly, you need to force a recalc, which is the same as the UDF. So it is
no better, and requires a lot of other formulae scattered about. Your
assumption on the value is exactly what I assumed, and can easily be checked
by doing
?range("D1").Value
in the immediate window in the VBIDE, after forcing the recalc of course,
and indeed it does show 0.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Dominic" wrote in message
...
Bob,

I got this to work for me by changing the formula in D1 to:

=CELL("width",D2)

For Excel2k (at least) you need to force a recalc to get the totals in
column L to refresh.

I imagine that when you hide column X the value in X1 turns to zero,

though
its hard to see to confirm. ;-)



"Bob Phillips" wrote:

Can you explain that, it doesn't work for me?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"CaptainQuattro"
CaptainQuattro.277voy_1146635100.8876@excelforu m-nospam.com wrote in
message

news:CaptainQuattro.277voy_1146635100.8876@excelfo rum-nospam.com...

Here's a workaround that will work in any version of Excel:


In row 1 Column D enter =CELL("width",A2)

Copy to cells E1 through K1

in Cell L11 enter

=SUMIF(D1:K1,"0",D11:K11)


--
CaptainQuattro


------------------------------------------------------------------------
CaptainQuattro's Profile:

http://www.excelforum.com/member.php...o&userid=32763
View this thread:

http://www.excelforum.com/showthread...hreadid=537953






  #39  
Old May 4th, 2006, 09:38 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Summing non hidden values in a range

http://cjoint.com/?fewLLijimL

With this instruction, worksheet is calculate:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
calculate
End Sub

Function sumVisibles(champ As Range)
Application.Volatile
t = 0
For Each c In champ
If c.EntireColumn.Hidden = False Then t = t + c.Value
Next c
sumVisibles = t
End Function

Cordialy JB

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula: If 2 values (in a range of six) are >3 then TRUE, FALSE IFfunction General Discussion 2 October 10th, 2005 10:34 AM
Summing values within a range rmellison General Discussion 7 September 2nd, 2005 12:43 PM
Cell linked to a range of cell values in different sheet szeng General Discussion 1 August 9th, 2005 02:41 AM
Want to put range values in a column into two columns Arch Worksheet Functions 7 June 15th, 2004 11:49 PM
Dynamic Offsetting, Names, Arrays, Range, Chart Values. NEED HELP Bob Lidgard Charts and Charting 14 February 25th, 2004 04:26 PM


All times are GMT +1. The time now is 12:06 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.